Solved Bring data into form field from a different query

[Gasman]

I would just create a small procedure to run it. Form will need to be open with a valid value.
I tend to do that, to test anything out, before I post back here.

Failing that, upload enough of the db to see the issue, with instructions on where and how to find it.

Having spaces in names and fields only makes life harder.

 

[MajP]

I do not know if this has been answered, but Is the bus number field of the datatype number? If instead it is "short text" then the criteria will need ' '.
strCriteria = "[Bus]= " & Forms]![CamInputToMaintT Form]![Bus]
to
strCriteria = "[Bus]= '" & Forms]![CamInputToMaintT Form]![Bus] & "'"

That is a Single quote after [Bus] = ' and a single quote at the end between two double quotes.

 

[Burnrose]

I do not know if this has been answered, but Is the bus number field of the datatype number? If instead it is "short text" then the criteria will need ' '.
strCriteria = "[Bus]= " & Forms]![CamInputToMaintT Form]![Bus]
to
strCriteria = "[Bus]= '" & Forms]![CamInputToMaintT Form]![Bus] & "'"

That is a Single quote after [Bus] = ' and a single quote at the end between two double quotes.

The Data Type for Bus is Number. btw, I was working on that procedure then crashed the db. :/ I have to start from a backup. oh my life.........

 

[LarryE]

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) & "")

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

Your WHERE clause needs double quotes and the table name (CameraHealthCheck ALLCompiled RepairDue Query2) should not need brackets and in your WHERE clause the & is not used when you reference a form.

You are looking up the LastEntryTimeStamp field in the CameraHealthCheck ALLCompiled RepairDue Query2 query WHERE the Bus field is = to the Bus control on the open CamInputToMaintT Form. If you want the Maximum LastEntryTimeStamp, then use DMax instead of DLookUp.

 

[Burnrose]

I made a sample from scratch to replicate my issue db which is giving me the same result. Let me know what you think. I'm not getting a result at all after I enter a Bus number from the repairDue Query that should bring in the LastEntryTimeStamp data for that Bus.

 

[Burnrose]

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

Your WHERE clause needs double quotes and the table name (CameraHealthCheck ALLCompiled RepairDue Query2) should not need brackets and in your WHERE clause the & is not used when you reference a form.

This resolved the #Error, but there is no result. Just a blank field.

 

[Gasman]

You have that as a DefaultValue ? and you do not even have that form in that DB?

This works in default value

=DLookUp("[LastEntryTimeStamp]","[RepairDueQuery]","[Bus] = 845")

THis works with the only form you supplied?
To get it to work, I had to set a default value for the bus control as 845.

=DLookUp("[LastEntryTimeStamp]","[RepairDueQuery]","[Bus] = [Forms]![RepairInputToMaintTForm]![Bus]")

 

[MajP]

The default value happens on creation of a new record so that approach will not work because the record is already created when you select the bus number. You could do something like
Me.LastEntryTimeStamp = DMax("[LastEntryTimeStamp]", "[RepairDuequery]", "[Bus] = " & Me.Bus)

However, I do not see why you would save the last time stamp in the record.

 

[Burnrose]

You have that as a DefaultValue ? and you do not even have that form in that DB?

I made a quick sample database which I uploaded. Table/Query Names were simplified for ease. That basic function of what I am trying to accomplish is the same. Yes, I have been entering this in Default Value this whole time. Where should I be utilyzing this code. I tried entering into the Control Source to test it as suggested as well, but gave the same result.

 

[Burnrose]

The default value happens on creation of a new record so that approach will not work because the record is already created when you select the bus number. You could do something like
Me.LastEntryTimeStamp = DMax("[LastEntryTimeStamp]", "[RepairDuequery]", "[Bus] = " & Me.Bus)

However, I do not see why you would save the last time stamp in the record.

For my actual database the "Repairs Due Query" is an imported table with specific data fields which includes LastEntryTimeStamp. My maintenance Form records repairs made to the MaintT Table. which has "Repair Date". I have a query that looks at when the repairs were requested to be made "LastEntryTimeStamp" and gives a datediff between the "Repair Date" to show me repairs to took an excessive amount of days to repair. Once repairs are made, the database removes the Bus from the Repairs Due list so that the technician no longer needs to address that Bus. So when they enter a maintenance record for a bus, I want that record to include the Due Date (LastEntryTimeStamp) as well as the Completeion Date (RepairDate). I want the MaintT table to record the LastEntry TimeStamp for the Vehicle being entered to work on.

 

[Burnrose]

The default value happens on creation of a new record so that approach will not work because the record is already created when you select the bus number. You could do something like
Me.LastEntryTimeStamp = DMax("[LastEntryTimeStamp]", "[RepairDuequery]", "[Bus] = " & Me.Bus)

However, I do not see why you would save the last time stamp in the record.

Where should I place the Me. code other than default Value, Unless this usage is ok here?

 

[Burnrose]

Where should I place the Me. code other than default Value, Unless this usage is ok here?

This resulted in a #Name? error when used in Default Value

 

[MajP]

See if this is what you mean. In the afterupdate of the bus I set the value of the last time stamp in the after update event.

 

[Burnrose]

See if this is what you mean.

Yes there should be a date/time after entering a Bus number. But Your Test2 file won't change when the bus number is changed nor work at all on the next record. Or was this just an example of what it should be populated to look like?

 

[Burnrose]

Yes there should be a date/time after entering a Bus number. But Your Test2 file won't change when the bus number is changed nor work at all on the next record. Or was this just an example of what it should be populated to look like?

ah you didn't use any code you just manually entered the Date/Time?

 

[Burnrose]

ah you didn't use any code you just manually entered the Date/Time?

of missed that, After Update

 

[Burnrose]

That worked! I added it to the afterupdate and it now populates and changes!!!!!!! I love you sooooo much!!!!!!!

 

[Burnrose]

That worked! I added it to the afterupdate and it now populates and changes!!!!!!! I love you sooooo much!!!!!!!

Spoke to soon, But I still Love you! I'm doing something wrong of course. Works on the Test2 database but not in my original. Getting a Microsoft Access cannot fint the object 'Me.' message box.

Bus field After Update:

Me.LastEntryTimeStamp = DMax("[LastEntryTimeStamp]", "[CameraHealthCheck ALLCompiled RepairDue Query2]", "[Bus] = " & Me.Bus)

 

[Burnrose]

I am uploading my original extracted test database. The login below should take you right to the form. I am not sure if it will error not being conneted to the imported Healthcheck Repair Due report. I checks for the report and automaticall updates it after connecting. I believe I disabled that in the VBA temporarily because of the time it takes to update but... Let me know if this works for you.
User: test
Password: test

Spoke to soon, But I still Love you! I'm doing something wrong of course. Works on the Test2 database but not in my original. Getting a Microsoft Access cannot fint the object 'Me.' message box.

Bus field After Update:

Me.LastEntryTimeStamp = DMax("[LastEntryTimeStamp]", "[CameraHealthCheck ALLCompiled RepairDue Query2]", "[Bus] = " & Me.Bus)

I had to strip more out of the OG database to upload it due to size Let me know if this helps. not sure if I broke anything else in it.

 

[Gasman]

I made a quick sample database which I uploaded. Table/Query Names were simplified for ease. That basic function of what I am trying to accomplish is the same. Yes, I have been entering this in Default Value this whole time. Where should I be utilyzing this code. I tried entering into the Control Source to test it as suggested as well, but gave the same result.

God that is slow? :-(

Formname etc?