Solved Bring data into form field from a different query

[Burnrose]

I'm hoping someone can assist with a unique issue I am having trying to come up with a solution to my Form data. I have a Form (CamInputToMaintT Form) which of course adds data to a Table (MaintT). The data from this form evenetually merges data from multiple table queries for multiple end query results.
There is a field in the (CamInputToMaintT Form) Form for "LastEntryTimeStamp" <date/time entry> which needs to be populated from data in a specific query [CameraHealthCheck ALLCompiled RepairDue Query2] which is based on the "Bus#" field that is entered.
Basically, when a Bus# is entered into the form, after update it will lookup that bus# in the [CameraHealthCheck ALLCompiled RepairDue Query2] query and bring in the <date/time> data from the "LastEntryTimeStamp" field that matches the Bus#. I am attaching images for example of table and query for reference. Hope I am explaining this well enough. Let me know if you have any questions I can go into more detail of why I am choosing this method if needed... thank you in advance!

FYI, I have tried entering in LastentryTimStamp field Default Value as:
DLookup("LastEntryTimeStamp" , "[CameraHealthCheck ALLCompiled RepairDue Query2]" , "Bus# = " & Forms![CamInputToMaintT Form]!Bus#)
But this gives me an <Invalid Date> error.

 

[theDBguy]

Hi. Welcome to AWF!

I'm not sure using the Default Value property is what you want. You could try using an unbound textbox for now with the DLookup() in the Control Source just to troubleshoot your syntax. Once you got that box working, you can decide whether to keep that box or use code to apply the DLookup() result to your field.

 

[plog]

Let's see if I understand your question by trying to simplify it:

User inputs a bus number, a field populates with the latest date that corresponds with that bus number in a query.

First a question--do you really need to save this value? In a database if you can simply look up a value when you need it, you don't store it in multiple places. You store it in that one place and just look it up when you need it. You don't store customer data all over the place, you store it in the Customer table and if the Sales table needs to be tied to a customer you store that customer's ID in the Sales table and when you need all that data together you build a query using Customer and Sales. Same principle applies here. Do you really need to store that value in the underlying table? If you think the answer is 'Yes', tell me why you think that.

Displaying the data on the form can be accomplished with a DMax:

MS Access: DMax Function

This MSAccess tutorial explains how to use the Access DMax function with syntax and examples. The Microsoft Access DMax function returns the maximum value from an Access table (or domain).

www.techonthenet.com

User enters the bus number, the input for latesttimestamp is an unbound input and you use the DMax to lookup the value you want. Lastly, 'Bus #' is a poor field name because it uses that hash tag. You should only use alphanumeric characters and underscores in names. Using special characters make coding and querying just a little harder, as you will see when you try that DMax.

 

[Burnrose]

Ok thank you, I will try to play with your advice. I've been really struggling with this for some time and time is running out on me

Hi. Welcome to AWF!

I'm not sure using the Default Value property is what you want. You could try using an unbound textbox for now with the DLookup() in the Control Source just to troubleshoot your syntax. Once you got that box working, you can decide whether to keep that box or use code to apply the DLookup() result to your field.

Ok thank you, I will try to play with your advice. I've been really struggling with this for some time and time is running out on me

 

[LarryE]

I'm hoping someone can assist with a unique issue I am having trying to come up with a solution to my Form data. I have a Form (CamInputToMaintT Form) which of course adds data to a Table (MaintT). The data from this form evenetually merges data from multiple table queries for multiple end query results.
There is a field in the (CamInputToMaintT Form) Form for "LastEntryTimeStamp" <date/time entry> which needs to be populated from data in a specific query [CameraHealthCheck ALLCompiled RepairDue Query2] which is based on the "Bus#" field that is entered.
Basically, when a Bus# is entered into the form, after update it will lookup that bus# in the [CameraHealthCheck ALLCompiled RepairDue Query2] query and bring in the <date/time> data from the "LastEntryTimeStamp" field that matches the Bus#. I am attaching images for example of table and query for reference. Hope I am explaining this well enough. Let me know if you have any questions I can go into more detail of why I am choosing this method if needed... thank you in advance!

FYI, I have tried entering in LastentryTimStamp field Default Value as:
DLookup("LastEntryTimeStamp" , "[CameraHealthCheck ALLCompiled RepairDue Query2]" , "Bus# = " & Forms![CamInputToMaintT Form]!Bus#)
But this gives me an <Invalid Date> error.

First, ACCESS will not recognize Bus# as a valid field name. The # symbol is reserved in ACCESS for other purposes. Rename the Bus# field in your table. You can use BusNumber or Bus_Number or BusNum or Bus_Num as examples.

Second the DLookup function you are using is erroneous as well. Let's say you changed Bus# to BusNum. Now you can set the default value for LastEntryTimeStamp field by using:

=DLookup("LastEntryTimeStamp","CameraHealthCheck ALLCompiled RepairDue Query2","[BusNum] = Forms![CamInputToMaintT Form]![BusNum]")

In addition, never use spaces in table names or queries. Change CameraHealthCheck ALLCompiled RepairDue Query2 to CameraHealthCheckALLCompiledRepairDueQuery2. The DLookUp should work with spaces used, but it is not a good idea. Same with field names in tables. No spaces.

 

[Burnrose]

@plog - I say yes because I need the LastEntryTimeStamp result included with the maintenance data for antoher query which analyzes Liquidated Damages LD's which looks at excessive time for repairs between reported issue date "LastEntryTimeStamp" and Repair "Date" which is from this form. This LD query is where I am going with the results of the code I'm looking for.

 

[Burnrose]

Hi. Welcome to AWF!

I'm not sure using the Default Value property is what you want. You could try using an unbound textbox for now with the DLookup() in the Control Source just to troubleshoot your syntax. Once you got that box working, you can decide whether to keep that box or use code to apply the DLookup() result to your field.

I received the same invalid date result:

 

[Burnrose]

First, ACCESS will not recognize Bus# as a valid field name. The # symbol is reserved in ACCESS for other purposes. Rename the Bus# field in your table. You can use BusNumber or Bus_Number or BusNum or Bus_Num as examples.

Second the DLookup function you are using is erroneous as well. Let's say you changed Bus# to BusNum. Now you can set the default value for LastEntryTimeStamp field by using:

=DLookup("LastEntryTimeStamp","CameraHealthCheck ALLCompiled RepairDue Query2","[BusNum] = Forms![CamInputToMaintT Form]![BusNum]")

In addition, never use spaces in table names or queries. Change CameraHealthCheck ALLCompiled RepairDue Query2 to CameraHealthCheckALLCompiledRepairDueQuery2. The DLookUp should work with spaces used, but it is not a good idea. Same with field names in tables. No spaces.

Yes Ive been using this database for 7 years this way and I've learned alot from scratch over the years. There are alot of beginning badd habits in here. But changing to BusNum will not change my results for the Dlookup error. I will sit down once the code is resolved to clean up alot of these bad practices as This database is actually an extract from the original so its not as massive of a beast to fix these things now. Thank you for the advice on cleanup.

 

[Burnrose]

Let's see if I understand your question by trying to simplify it:

User inputs a bus number, a field populates with the latest date that corresponds with that bus number in a query.

First a question--do you really need to save this value? In a database if you can simply look up a value when you need it, you don't store it in multiple places. You store it in that one place and just look it up when you need it. You don't store customer data all over the place, you store it in the Customer table and if the Sales table needs to be tied to a customer you store that customer's ID in the Sales table and when you need all that data together you build a query using Customer and Sales. Same principle applies here. Do you really need to store that value in the underlying table? If you think the answer is 'Yes', tell me why you think that.

Displaying the data on the form can be accomplished with a DMax:

User enters the bus number, the input for latesttimestamp is an unbound input and you use the DMax to lookup the value you want. Lastly, 'Bus #' is a poor field name because it uses that hash tag. You should only use alphanumeric characters and underscores in names. Using special characters make coding and querying just a little harder, as you will see when you try that DMax.

I'm going to look into Dmax now and let you know. <Sorry I was having issues replying earlier>

 

[MajP]

Yes Ive been using this database for 7 years this way and I've learned alot from scratch over the years. There are alot of beginning badd habits in here. But changing to BusNum will not change my results for the Dlookup error. I will sit down once the code is resolved to clean up alot of these bad practices as This database is actually an extract from the original so its not as massive of a beast to fix these things now. Thank you for the advice on cleanup.

It may fix the error. You have bus# shown in two places in the dlookup and not surrounded by [] brackets. That should fail and likely give the invalid date message. Need the [ ].

 

[Burnrose]

It may fix the error. You have bus# shown in two places in the dlookup and not surrounded by [] brackets. That should fail and likely give the invalid date message. Need the [ ].

Ahh ok, I'm actually in process taking the advice to correct this now. approx. 15-20 Queries to update. I'll let you know hopefully shortly. I'll edit the SQL's its faster - UPDATE - Gonna take longer than I thought. I'll check back in tomorrow. Thank you all for your advice so far!

 

[MajP]

Access allows you to hurt yourself. You can create bad object names (spaces, special characters, reserved words) and does not complain. But when you go to use them you have to [Enclose them] or get a failure
forms![Bad Name]
forms!GoodName

"[Bus#] = " forms!SomeForm![bus#]

So that is why it is better and easier to always do it right to start and have good names (no special characters, no spaces, no reserved words).

 

[Burnrose]

Access allows you to hurt yourself. You can create bad object names (spaces, special characters, reserved words) and does not complain. But when you go to use them you have to [Enclose them] or get a failure
forms![Bad Name]
forms!GoodName

"[Bus#] = " forms!SomeForm![bus#]

So that is why it is better and easier to always do it right to start and have good names (no special characters, no spaces, no reserved words).

Ok, I have renamed all of my Bus# references to [Bus]. This has gotten rid of my Invalid Date error, but now my final result gives me a #Error in the field box. I have tried several variations of the DLookUp and DMax with the same result. I have also tried After Update in the Bus field box to Requery LastEntryTimeStamp field. Are there any suggestions to what I am missing here?

Some of the variations tried:
=DlookUp("[LastEntryTimeStamp]","[CameraHealthCheck ALLCompiled RepairDue Query2]",[Bus]= & [Forms]![CamInputToMaintT Form]![Bus])
=DlookUp("[LastEntryTimeStamp]","[CameraHealthCheck ALLCompiled RepairDue Query2]","[Bus]=" & [Forms]![CamInputToMaintT Form]![Bus])
=DlookUp("[LastEntryTimeStamp]","[CameraHealthCheck ALLCompiled RepairDue Query2]","[Bus]='" & [Forms]![CamInputToMaintT Form]![Bus])
=DLookUp("[LastEntryTimeStamp]","[CameraHealthCheck ALLCompiled RepairDue Query2]","[Bus]='" & Nz([Forms]![CamInputToMaintT Form]![Bus],0) & "'")
=DMax("LastEntryTimeStamp" , "[CameraHealthCheck ALLCompiled RepairDue Query2]" , "[Bus] = " & [Forms]![CamInputToMaintT Form]![Bus])
=DMax("[LastEntryTimeStamp]" , "[CameraHealthCheck ALLCompiled RepairDue Query2]" , "[Bus] = " & Nz([Forms]![CamInputToMaintT Form]![Bus],0) & "")

 

[Burnrose]

Yes Ive been using this database for 7 years this way and I've learned alot from scratch over the years. There are alot of beginning badd habits in here. But changing to BusNum will not change my results for the Dlookup error. I will sit down once the code is resolved to clean up alot of these bad practices as This database is actually an extract from the original so its not as massive of a beast to fix these things now. Thank you for the advice on cleanup.

Renaming my Bus# field solved the Invalid date but now have #Error for my result.

 

[Burnrose]

I'm going to look into Dmax now and let you know. <Sorry I was having issues replying earlier>

I tried DMax but get the same #Error result as DLookUp after correcting my Bus# naming to Bus

 

[Gasman]

Perhaps check what is in Forms]![CamInputToMaintT Form]![Bus ?

I would create a string variable for the criteria, then you can debug.print it to see if it is correct. Then you can use that in the function.

 

[Burnrose]

Perhaps check what is in Forms]![CamInputToMaintT Form]![Bus ?

I would create a string variable for the criteria, then you can debug.print it to see if it is correct. Then you can use that in the function.

Bus is a Number Data Type. Sorry, not sure what to do to create a string variable for the criteria to debug.print and see if it is correct.

 

[Burnrose]

Bus is a Number Data Type. Sorry, not sure what to do to create a string variable for the criteria to debug.print and see if it is correct.

If you have the patience, can you guide me on these steps?

 

[Gasman]

Dim strCriteria as String, dtDate as Date
strCriteria = "[Bus]= " & Forms]![CamInputToMaintT Form]![Bus]
Debug.print strcriteria
dtDate = =DlookUp("[LastEntryTimeStamp]","[CameraHealthCheck ALLCompiled RepairDue Query2]",strCriteria)

However, copying your inordinately long names , I spotted that you dor not start the criteria with a quote, nor end it, as I have done above.
So try that first., but this method is handy when more than one criteria.

It will not work as an expression source for a control, but you can at least test with this method.

Try

=DlookUp("[LastEntryTimeStamp]","[CameraHealthCheck ALLCompiled RepairDue Query2]","[Bus]= " & [Forms]![CamInputToMaintT Form]![Bus])

and please start using code tags, makes reading code much easier.

 

[Burnrose]

Dim strCriteria as String, dtDate as Date
strCriteria = "[Bus]= " & Forms]![CamInputToMaintT Form]![Bus]
Debug.print strcriteria
dtDate = =DlookUp("[LastEntryTimeStamp]","[CameraHealthCheck ALLCompiled RepairDue Query2]",strCriteria)

However, copying your inordinately long names , I spotted that you dor not start the criteria with a quote, nor end it, as I have done above.
So try that first., but this method is handy when more than one criteria.

It will not work as an expression source for a control, but you can at least test with this method.

Try

=DlookUp("[LastEntryTimeStamp]","[CameraHealthCheck ALLCompiled RepairDue Query2]","[Bus]= " & [Forms]![CamInputToMaintT Form]![Bus])

and please start using code tags, makes reading code much easier.

I just tried this Dlookup with the same #Error result.
Please excuse my ignorance with the string criteria code, as I am self taught. I am not sure where to place this snippet to test. I tried inserting a module to run it but that didn't work out :/ Where should I be placing this?