Object doesn't support..... 1

[lars7]

Hi,
I'm trying to put a location Id in my staff table where the staff members pay number is equal to a textbox in a subform on my main form. I think I am missing something in the first part of the "where paynum" clause. I am getting a message saying "Object doesn't support this property or method"

CurrentDb.Execute "UPDATE tbltruststaff SET S_LocationId = '" & Forms!frmmain!List5 & "'&" _
& "WHERE paynum = '" & Forms!frmmain!tblrecordsubform.Paynum & "'"
Can anyone help me with this.

 

[PHV]

And what about this ?
CurrentDb.Execute "UPDATE tbltruststaff SET S_LocationId = '" & Forms!frmmain!List5 & "'[!]&[/!]" _
& "WHERE paynum = '" & Forms!frmmain!tblrecordsubform.[!]Form![/!]Paynum & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Hi PHV,
The message has changed to:

The expression you entered has a field, control, or property name that access can't find.

 

[PHV]

CurrentDb.Execute "UPDATE tbltruststaff SET S_LocationId = '" & Forms!frmmain!List5 & "'" _
& "[highlight] [/highlight]WHERE paynum = '" & Forms!frmmain!tblrecordsubform.Form!Paynum & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Hi PHV,
I got the same message again. I checked all the field names and they are ok. The first part worked ok before I added the where clause but it changed all the locationId's in the table. :~/

 

[PHV]

You may play with the expression builder (loaded forms) to discover how to properly reference your pay number textbox.
You may also have a look here:

http://support.microsoft.com/?kbid=209099

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Hi Phv,
I will have a look at that thanks for you time as always.

 

[lars7]

Hi Phv,
I put this code in the control source of a text box (that I will hide) on the main form and it worked. It will now update the staff table with a locationid.

=Forms!frmmain!tblrecordsubform.Form!Paynum

And the main code:

Private Sub Command57_Click()
CurrentDb.Execute "UPDATE tbltruststaff SET S_LocationId = '" & Forms!frmmain!List5 & "'" _
& " WHERE paynum = '" & Forms!frmmain!Text10 & "'"
Me.List5.Requery
End Sub

The text box shows the pay number that I have entered in the subform but only when the focus in on that record if I move the curser to the next record the text box goes to null.

If a user was to press the enter key after processing the last record it will not work. Is there a way to make it hold the value once it has it?

 

[PHV]

Get rid of the ControlSource property of Text10 in your main form.
Then in the Current event procedure of the subform:
If Not Me.NewRecord Then
Forms!frmmain!Text10 = Me!Paynum
End If

And in the AfterUpdate event procedure of Paynum:
Forms!frmmain!Text10 = Me!Paynum

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Hi Phv,
Thanks it worked great.

Tom.