Other subreddits of interest:

Wrong modified dates

Strange behaviour. I've just closed file but access shows wrond modified date. And the worst thing is if you open file from this menu, you loose all changes since that date

I'm using the template 'vehicle maintenance' that comes with Access.
I created a form with a subform. The main form has a drop-down combo box.
I'm using the Vehicle Extended query that's bound to my main form and Expenses Extended to my subform.
The form works properly for the majority. However, the first time I open the form, select from the drop-down combo box, after it populates into the subform - once I go to enter new info, I get the the error.
It doesn't appear after that when I go to select new items from the drop down menu. Only the first time, until I restart MS Access or I do a compact & repair.
All the fields are set to allow edits/additions.
No idea how to fix this error.
Helllppppppp please!

When importing data from multiple sources, I was successful with the built-in wizard creating subtables - it created a record in the "Main" table for each employee. (BTW: The popup with the wizard's suggestions doesn't expand and THAT is very annoying when there are 9 tables!)

The primary key is the employee's id number.

When I tried to normalize the data manually after renaming fields to be consistent & more specific, the Main table comes up with the right number of records with the autonumber ID, but the data for each isn't there - it only comes up with a drop-down for each field showing all of those records:

https://preview.redd.it/ira69612d9yc1.png?width=266&format=png&auto=webp&s=b06195f98074b52d1a8593edd2cdf965872f29f7

I used the syntax from the one the wizard made and changed the field names. Here's the Lookup row source in Main > Employee to pull records by their employee ID:

SELECT [Employee ID] AS xyz_ID_xyz, [Employee ID] & ', ' & [Empl First Name] & ', ' & [Empl Last Name] AS xyz_DispExpr_xyz, [Employee ID], [Empl First Name], [Empl Last Name] FROM Employee ORDER BY [Empl Last Name];

What am I doing wrong??? Thanks in advance - I appreciate the input.

My Google foo is letting me down. I'm trying to create a form that displays active elements in a 10x10 grid indicating whether an object is present or not. Ideally, I'd like to click this form and toggle the item. I've created the query that brings out the relevant items but I can't display them X by Y in a status grid. Only vertically. It is dynamic and there are holes in the data (ie, some objects/positions don't exist). I do need the form to be somewhat similar in shape to the item being audited.

we have moved the backend of the msaccess db to sql server.

And the front end accesses the data as linked tables.

All works very well except we would like to rework the security.

At present on the SQL side we have a single role app_user which is given

select,insert,update permissions on all tables.

Within the db is a usertable that defines the actual user permissions

and the front end references this table before any user actions are performed, and actions appropriately.

However : At present any user could with a little bit of know how could Break into the front end ACCDE

and goto the sql linked usertable and set their own permissions.

[and yes we do have the ACCDE locked down - pretty much.]

What we would like to acheive is two roles on the sql side. readonly_user and app_user

with the readonly_user only having select permissions on all tables

all users get assigned only the readonly_user role

within the frontend the usertable is read and if their credentials within the usertable allows edit then

their role is switched to app_user. [via we guess some sql procedure call - which we would also grant execute on to readonly_user]

So per this -

1. is this a good strategy? Otherwise please suggest your recommendations

2. is it possible for users to switch SQL_roles ? impersonate

3. anyone got a good reference to a sql procedure that does

nb. we have several databases in the migration to sql works and all are generally having a usertable in the ms acces backend which denotes the user permissions, so we'd like to build a standard security solution. hence reaching out to the enlightened.

Many thanks for your help and suggestions. Thx

I am creating a simple form where I have a combo box to select records from a master table. The combo box works, but the field I am selecting on is truncated in the list. There are three fields, an ID, LastName, and FirstName. The ID is a short text field. I set up an input mask for the ID so they all have the same prefix "CCSAR", then three numbers. i.e. CCSAR005. The input mask is "CCSAR"000, and it works when I input the data This ID and other info is then supposed to be saved in a second table. However, the combo only displays the 005. It fills the ID field on the form only with the three numbers. Field is short text in the main table. When I try to save the record, I get an error: "You cannot add or change a record because a related record is required in table 'tblMembers'."

I have a relationship set up between the ID field in both tables. If a I run a query to list records in the tblMembers the ID displays as it should (CCSAR001, etc). I cannot figure out why it is not displaying correctly in the combo box list.

FYI, I used to do a moderate amount with MS Access development 10-20 years ago, but I'm just starting to get back into it. So, I'd appreciate any suggestions or help. I suspect this may be the input mask - maybe it does not save the literal characters?"

Thanks!

the reference in VBA

the error

the item as it is in the navigation pane

As you can see the little arrow is indicating the query is a shortcut in a custom group in the navigation pane. Everything is a local database, not split, no ODBC connection. Do I have to reference shortcut objects different when invoking them via VBA?

Hey guys I am having some issue with the dlookup function which I need some help fixing. Here is my code to begin with: =dlookup(“Transaction #”, “Current Transactions”, “Asset=“ & [Asset])

This code is trying to look up the Transaction # value from a query known as “Current Transactions” by matching the “Asset” on that Query to the “Asset” value is on the same form as the dlookup function. All it is outputting right now is “#Error” but if I were to switch it to pulling values from the “Transactions” table, the source table of the “Current Transactions”Query, it works perfectly fine for some reason. What do I need to do to get this dlookup function to work with my Query?

Wondering if anyone has experience with something like this:

Scan multiple handwritten documents (proof of work) which includes information such as hours worked and dates then have access review it with some sort of OCR add on (or coded in) and automatically transfer the information required (dates, hours worked, emoloyee etc) into ms access data tables.

Right now I’m inputting in invoices manually from employee documents and it’s very time consuming.

so at work we have to fill a table in ms word every day (that have working position, and working hours) kinda like this :

the workers change position and timing each day (and a bigger table in reality)
is theres a way to do this in more automated way than this?
I was thinking if its possible to do it in access where i have a table of all the workers i want a way to give the user an empty table where he get some drop downs lists to pick the workers than print.
if it cant be done with access can you suggest another methode.

I am building an Asset tracking database, which needs to include many photos of each asset. As I understand it, using the "attachment" field type is not good database practice, so I'm working on a different method.

I found a video (link in comments) that details a method for dragging and dropping photos into a hyperlink field, which then appends a new file path and copies the photo in to a folder designated by the new file path. That file name with the new path is stored in a Short Text field for later retrieval.

I've attached screen shots from a sample database and the associated VBA code that is supposed to make this work. The problem that I am encountering is that my hyperlink field is using the Relative Path (..\..\..\Pictures\DSC_0020.jpg) instead of an Absolute Path. How should I address this?

When this is built out, there will also be an AssetID field that relates the photo to the asset. As someone with limited database experience, I think I would like to build out this code to create a folder (MkDir) with the AssetID, and save the photo in to that folder. Is there a different way that you would go about incorporating and organizing photos?

Hello friends, I was asked to recreate a database in Access because the perfectly good one that we use isn't free. I'm a church secretary and the online database we use is called Church 360, it's by Concordia Technologies and works really well! Besides families and individuals, it also tracks attendance, events, offerings, and does the annual reports. So I was asked to create an Access database to replace it, and I got the individuals in, and also the families, and I've connected them with relationships.

But I have some questions that I need help with. To start, how do I create a calendar with events that I can connect to the tables and track who attended which events? or at least how many people total attended the events? I know how to do it in Excel, but not how to connect it to an Access table.

Also, is there a way to track offerings, or should it just be a separate spreadsheet? which would be huge, as it would track dozens of families' offerings over a year; and although most of them are to General Fund, sometimes they go to other funds. Oh dear, is that what a pivot table is for? I never got the hang of pivot tables and I have a degree in Microsoft Office.

Also, how can I offer access to the database (heh) to other people who need to use it, such as the Pastor and the elders? My son suggested Teams but I can't figure that out. He used it when he started trade school April 2020, but we hardly ever had to close the office so I never learned it. I never heard of that when I got my degree!

I got that degree nineteen years ago, and because we have the online database I haven't used Access a whole lot except for Vacation Bible School and we haven't even done that for a few years, so I don't get much practice in Access. Just what I do at home, and I do enjoy figuring out databases, but this one is beyond me! Any help would be so appreciated!

Hi!

In my organization, we have BE/FE accdb files on a network share. Each user gets their own FE. We require ("ask firmly") that users only access the database FE through a Citrix XenApp; opening the database directly from local PCs is SUPER slow over the VPN connection.

We have a whitelist of Citrix servers that can access the database; the computer name doesn't match an entry in the whitelist, the database displays a message and closes. This works, but the database is still very slow to open and display the message if opening locally.

Is there a way to trigger this code before Access attempts to download anything to the local desktop?

Thanks!

Will the SQL view query shown in access work if I was to past it in SSMS and run it? If course using the same table sources.

Table 1 has a line per entry- job #, crew, number of hours spent

Table 2 has anywhere from 1-7 records because every activity is billed differently, but all tagged to the job #.

When I query Table 1 and Table 2 together, it multiples the hours in Table 1 by however many records are in Table 2 tagged to that job number.

Any idea how to resolve? I tried going into SQL and making INNER JOIN into LEFT JOIN, but got an error.

Note - table names are shown in [brackets]

I have a db setup in order to track groups of dates [Milestones] associated with records [Programs] in what I'll call a "parent" table. For these groups of dates, I have also set up an intermediary table that can track changes to these groups of dates [Milestone Sets]. The way it all shakes out is -

​

db setup

Now, my question is, when I query ALL of the milestone dates associated with all of the programs (for one big export to excel) I want to ONLY pull the dates from the most recent [MilestoneSets] update for each program. And I don't really have any idea how to do that. I was thinking about adding some code to my form for adding a new MilestoneSet and adding an additional field to track the status of each MS set and only have 1 be "active" that I could filter with the query, but this feels like a workaround and it seems like there should be a way to achieve a simpler solution with what I have now. Any thoughts or advice is much appreciated!

​

I have a people table. And I have several related junction tables: PeopleIncome, PeopleCity, PeopleTier, etc (there are 7 in total but there may be more later). I have a form with 7 subforms, each having two columns, a checkbox column and a label so users can tell what they're selecting.

The record source for each subform comes from one large table (TempTable) that contains all the options for ALL subforms, and I've just created select statements to populate each subform with the appropriate subset of table info. The user makes the selections, and I've created a query that takes those selections and dumps all of them into another table (PersonResult), and using that table I can inner join against the Person to get the search results needed. This works for "or" type search because anyone who meets any of the criteria is conveniently in the table.

This however does not work for "and" searches. So I figure, A) I must create a query for each subform to obtain the user selections made on each subform, store each resultset in their own table (I could use arrays/collections in VBA I guess, but I'd like as close to a no code solution as possible), and then do an inner join on each of the tables. This would ensure that I only get results where the 7 recordsets have common personIDs, and the "and" search is good to go.

However this approach results in two different sets of queries: the "or" query which dumps the results from the 7 junction tables into a common table using an append query, and 7 different select queries that would dump the results of each subform into their own tables to be inner joined together. Even typing this, the whole thing seems devised by a crazy person, but it works. Intuitively I feel like this approach is wrong, or is this basically what I'm stuck with unless I code everything out in VBA and have to figure out SQL statements and whatnot, which I hate doing, but can do if it's EASIER than what I've explained above. Thanks.

Hi, I'm having a brain freeze with this, any suggestions greatly appreciated: I am wanting to chart a grouped / aggregate query, based on rows selected from a table related to a ClientID. I'm not very good at explaining but I'll try, and I have included a handdrawn sketch of what I am looking to achieve in the links commented below. ClientID is related through the DSQIID table where it is a FK.

1 table "Dsqiid" contains screening (DSQIID) records: DsqiidID (PK), ClientID, DsqiidDate, DsqiidScore etc.

Another table "PartAScore" contains 43 optional questions to answer for each DSQIID record, each having a value of 1 to 4 to represent 4 fixed answers: DsqiidID (PK) , S1 (number field), S2 (number field) etc.

The subform for PartAScore uses option groups to assign score of 1 to 4 for each scoring field (S1, S2...)

All works fine - simple table in image link 1 below. The relationship between DSQIID and PartAScore tables is 1 to 1, but Clients (ClientID) to DSQIID is 1 to many.

A query or 2 gets me to image link 2 below where I dlookup the text for each question, along with the dlookup for the text corresponding to each score answer of 1 to 4. They are grouped and an Scount field counts the number of corresponding answers to each answer group, for each question. QID is the question number as I was also looking to have the questions in the same order as on the paper form clinicians use.

Where I am struggling is creating a column chart with each question on the x axis, the number of responses for each answer (1-4), or percentage of each answer (1-4) for each question on the Y. I figured the legend could be assigned the answer text, giving 4 different coloured columns for each question. I can get the questions on the x axis, the count / percent on the y, but when I select SText (the corresponding text for each answer) the chart then goes blank.

My instincts tell me I have done/not done something fundamental, but it's not occurring to me.

I have been trying with UNION queries, which seems to get me mostly to where I want to be but I am not too familiar with them.

Link to image 3 (apologies for Blue Peter style sketch!) of what I was after. X axis is each question, Y is the percentage of each answer 1-4 for each question.

Once I get there, I would like the query, or subquery to pull screening records between 2 dates set on a form, which I think I can do once I have gotten my head around this.

Apologies if this hasn't been explained well, any help / advice much appreciated, thank you.

Hello,

Got a help desk ticket on Friday the one of our reports wasn't showing the right dates. The ApptDate keeps replicating 04/17/2024, the DatetoAdmin keeps replicating 04/18/2024, and the DateReturnedCorr keeps replicating 04/24/2024. 334 frikin' pages of this stuff, even showing these bogus dates from sales that were closed (which shouldn't appear at all.)

SELECT tblSalesHeader.ApptID, tblClient.CompanyName, tblSalesAppt.ApptType, tblSalesAppt.ApptDate, tblSalesAppt.Agent, tblSalesAppt.[2ndAgent], tblSalesAppt.DatetoAdmin, tblSalesAppt.DateReturnedCorr, tblSalesAppt.DateReturnedCorr2ndTime

FROM WhoAmI, tblClient, tblSalesHeader, tblSalesAppt

WHERE tblClient.Active=Yes AND tblSalesHeader.ApptID=tblClient.ApptID AND tblSalesHeader.SaleComplete=No AND tblSalesHeader.CloseOutDate Is Null AND tblSalesAppt.InvSentDate Is Null AND tblSalesAppt.ApptDate Is Not Null AND tblSalesAppt.DateReturnedCorr Is Not Null AND (tblSalesAppt.Agent=WhoAmI.Initials OR tblSalesAppt.[2ndAgent]=WhoAmI.Initials)

ORDER BY tblSalesAppt.ApptDate;

I ran the query through debugging and no errors return. The query ran fine before Friday. Has anyone else experienced this issue and know a fix?

UPDATE:

Here's the solved SQL. The problem was with the joins and there was also a missing value for ClientID. Thank you, u/ConfusionHelpful4667.

SELECT tblSalesHeader.*, tblClient.*, tblSalesAppt.*

FROM tblClient, tblSalesHeader, tblSalesAppt

WHERE ((tblSalesAppt.ApptDate) Is Not Null And (tblSalesAppt.DateReturnedCorr) Is Not Null And (tblSalesAppt.InvSentDate) Is Null) And (tblSalesAppt.Agent=WhoAmI.Initials OR tblSalesAppt.[2ndAgent]=WhoAmI.Initials) And tblClient.Active=Yes AND tblSalesHeader.ApptID=tblClient.ClientID AND tblSalesHeader.SaleComplete=No AND tblSalesHeader.CloseOutDate Is Null AND tblSalesAppt.ApptID=tblSalesHeader.ApptID

ORDER BY tblSalesAppt.ApptDate;

I'm creating a database with a growing number of queries, used for all sorts of things. It's becoming clear that I need some sort of naming convention to keep these organized and to state the purpose of each query. Somehow creating insanely long and descriptive query titles, such as which form its for and what its purpose is seems intuitively wrong, but possibly unavoidable. How do you all address this problem, and what are some dummy examples of query naming conventions you might use? Thanks.

Rant: I'm well aware i have to provide my own date converter for sql querys in Access VBA environment. But now even boolean, the most simply types of variables screw me up when composing a sql query in a nonenglish environment

When using a boolean as variable (as boolean!) in a function,

function myfun (prmDate as Date, prmbool as boolean)

yaddayadda

set rs=db.openrecordset("select * from table where abooleanfield = "& prmbool )

prmbool has the value "Wahr" which is german for true regardless of the way i call the function.

myfun(date, 1) => prmbool = "wahr"

myfun(date, true) => prmbool = "wahr"

which wouldn't bug me at all if access only knew that true = wahr but also that wahr =true.

but it doesn't. recordset empty.

so i have a workaround:

Public Function fdb(bool As Boolean) As String

If bool = True Then

fdb = "True"

Else

fdb = "False"

End If

End Function

Which is used as

set rs=db.openrecordset("select * from table where abooleanfield = "& fdb(prmbool) )

and provides perfect results.

but cmon, i mean, really? Performance? Usability?

/rant

Anybody able and willing to point out where i screwed up here? I must do something fundamentally wrong. I cannot accept the idea a database is unable to compare a boolean value without some mental circling.

​

​

I'm usually "knowledgeable" enough to find the steps I need with a google search but I can't seem to find the right terminology in this case:

Not that the specifics are necessary, but due to my inability to explain it otherwise, put your mind in the context of human resources. Thousands of Employees - thousands of transactions to track their raises, physical location changes, position changes, etc.. I need to isolate the rows where the action reason is a transfer and max out the date. This will give me the most recent transfer for each Employee (who has a transfer row).

This is where I need help: step two is diving back into that data and returning, for each Employee returned above (those with transfer transactions, and if more than one the most recent one), the most recent row occurring prior to that transfer transaction. It is this step that I don't know enough for google to help me.

Any help would be greatly appreciated!

Helloo, So I have a few questions as I’m new to this program but want to create a database. My questions are:

1. How to insert a dropdown (combo box?) in a table? (I already created the form)
2. How to change background color of a field, depending on the dropdown menu? (For example if you choose option 1- it gets red, if you choose 2 - it gets green and so on)
3. Is there a way to generate a calendar? Something like I would put dates in or a source where it all generates by itself for a year without putting every day manually?
4. I created a relation between two tables, why doesn’t the data transfer? One table is like a source with names and other data and I would like it to transfer it to other tables so there are lists that have some same data: for example names.

Thank you in advance for the answers 🙏🏼 PS. I would be really thankful if there were solutions that don’t require VBA. If there is no other way, please tell me, but I was told not to use it 🤷🏼‍♀️

I'm hoping to get some pointers or advice as I'm at a loss here. I have a legacy program that we need to access data from, and I'm having a heck of a time.

I have an accde file, I tried opening it with M365 Access and it said it can only be opened by 32 bit Access. I made it through that hoop and used 32 bit Access, and now it says it cannot open in any Access version greater than 12.9.9999.9

I've looked around and can get old runtimes, but where can I get old versions of full Access? I've also tried importing the tables into a new Access database, but I'm getting persistent errors about missing odbc drivers that I can't get to the bottom of.

I just want the heckin' data and it seems insistent on not letting me have it!

I work with data that gets transcoded incorrectly from a program we use. We can import text with special characters just fine, but any export we do from the program will not translate right. I understand why it's doing that, but unfortunately there's no solution to do anything other than deal with what we get, so I would like to see if Access can help with the following:

Take text that got formatted as é or Ã-­ and correct it to display é or í . There are many more characters like these two examples.

Thanks for any guidance.

I am having troubles converting my excel data into csv format. Please help!!! Any ideas guys! Thank you in advance.

I want a data input form to not save if the user exits out via the x button and instead use a save button to do that. I got the save button working but how do I prevent the form from saving the data when the user presses the x button? I’m assuming I would make an ON Close Macro but I’m very new to macros so idk what to put in there so any suggestions would be appreciated

I'm trying to make a database of computer components, but I'm having a little trouble conceptualising the tables I would need. I figured that, given how each component has a few identical fields, with numerous others that are unique to that type of component, splitting them apart would be useful, but I fear I'm either overcomplicating it, or setting myself up for headaches later if I need to change parts of the database.

Right now, I've got a product table which includes basic info like product ID, price, part number and part category. Categories include Processor, Memory, Graphics Card, Chassis, Motherboard etc.

Then for each category of products I have a second table for more detailed aspects of each part. Product_CPU for example has fields for processor information like core count, clock speed, boost clock speed etc, while Product_GPU has fields for graphics card related features like shader count, dimensions, number of slots used, number of fans. Basically each category will need all the elements of Product, but each have their own subset of unique fields only really applicable to them.

I'm more just wondering if I'm on the right kind of track for making such a set of tables to handle this, and making relationships between the product and that of each category. Are there any other basic elements I'm missing that would be more suitable for this style of data?

Once I've got the tables created, I want to make a form that will automatically change fields depending on which category is selected, creating a new line in the relevant table top contain information added there. Not worrying myself too much with that right now though, just trying to tighten up the table side of things.

I'm working on a project at the moment and wondering how possible is it to add a section in my form/database using access for an upload button that when a document is connected to it it copies that document into a designated folder under a name given in the form but also connects it to that row on the DB so if I ever need to view that doc again it can be done through the excel dump or through the DB ?