Expression working in query not doing it in VBA from form?

[Perfac]

The expression in red is in the query and returns the result wanted. I have to do it in code on the form for “Wek_ID075a” must be in the table to link the query q01Week to “Wek_ID075a”. If I do an expression in the query “Wek_ID075a” is not in the table.
Wek_ID075a: DLookUp("Wek_ID075","q03WagesProcessingx","WgsPrcI D075 =" & 1)

Adding a write able field “Wek_ID075a” in the table and query, and trying the same expression in blue on the form VBA returns Null. It is linked on an event “Click” of the only combo box on the form. This expression retrieves a number from its own query. I tried adding Me! In fornt of field names, see in green but it errors.
Me!Wek_ID075a = DLookup("Wek_ID075", "q03WagesProcessingx", "WgsPrcID075 =" & 1)
Me!Wek_ID075a = DLookup("Me!Wek_ID075", "q03WagesProcessingx", "Me!WgsPrcID075 =" & 1)


Wanting to keep it short, but here is what I am trying. If you have an answer for above no need to read further.


The single control(combo) on my wages form is the wages date. There is a query storing the active employees for that week. Clicking on that field it appends the active employees to the table “t03WagesProcessingx”, and they end up in the query. The form is not an input form, just a set up that sets up a comprehensive report. One click gives the selected weeks wages. Waiting two minutes for the result means I must program better. The field "Wek_ID075" is bounded to the one combo(Image). It results in only the first line showing the ID. My need is to get the ID in all the rows, by an expression from the form. Any other ideas?

[June7]

Not clear to me why two fields (one text and one number) have same data.

Why are you hard-coding the parameter of 1?

If you need to update multiple records, run an UPDATE action SQL.

[Perfac]

If there is a text field on a form storing a date, bounded or unbounded. What expression will return that date in a field in a query, local or foreign query? If there are multiple records, it will work for me if every record in the query shows that date.

[Perfac]

If there is a text field on a form storing a date, bounded or unbounded. What expression will return that date in a field in a query, local or foreign query? If there are multiple records, it will work for me if every record in the query shows that date.

[June7]

SELECT query can reference textbox but that will not save to record in table.

[Micron]

Must not be the answer - too simple. These are not the same
WgsPrcI D075
WgsPrcID075

[Perfac]

Not clear to me why two fields (one text and one number) have same data.
Both should be number, I fixed that, but the challenge is to get the selected Week ID in every row. The combo box on the form is the only field on the form. Selecting the wage date sets up a report no matter how many employees on the list. I got the expression in red in the query to result correctly in what I need. I need to return the same through VBA. Or any way where the date in the combo box can be saved in field Wek_ID075.

Why are you hard-coding the parameter of 1?
Clicking on the one combo box there is an event that clear the table t03WagesProcessing and ID will start at 1 again, it then appends the correct list of employees for the requested week to the table. Since my current programming results in only the first row to show the Week ID, you can see the expression in red did the job. But it will have to be from the VBA.

If you need to update multiple records, run an UPDATE action SQL.

I assume an update query can not do it. Will let you know. Thanks. If I can learn how to retrieve data from a form(unbounded), to be placed in a field in a query, my issue is solved.

[Perfac]

Must not be the answer - too simple. These are not the same
WgsPrcI D075
WgsPrcID075

Even clicking on edit here the space disappears. It worked, there was no space. I missed something like that yesterday wasting an hour.

[Perfac]

Looking at the image on my first thread. I don't like "home made fixes" but if it works, it works. I added Wek_ID075b to the table, and query. Then added in the VBA; Me!Wek_ID075b = Me!Wek_ID075a, on the afterupdate event. This is now after update of the first of three fields Wek_ID075. It returns Null?

[Perfac]

Ruben must be a genius! Issue solved.

Private Sub Wek_ID_AfterUpdate()
Dim sqlstr As String
Me.Requery
sqlstr = "UPDATE t03WagesProcessingx SET t03WagesProcessingx.Wek_ID075a =" & Me.Wek_ID & ""
CurrentDb.Execute sqlstr
End Sub