VBA Code Required to Update Field Value In Form B from Underlying Field Value Form A

[DaveCollins181]

I have two forms, both of which have a field called JobID (Form A is the primary Key and Form B is the linked field - Relationship: One to Many).

Form A = FrmJobs
Form B = FrmPurchaseOrders

I have put a command button on Form A. When I click the button I need it to open Form B and then automatically fill the field JobID in Form B with the same value in Form A from which it was opened. I have tried the following:

Private Sub Command214_Click()
Me.Refresh
On Error GoTo Err_Command214_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FrmPurchaseOrders"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec
Forms![FrmPurchaseOrders].[JobID] = Forms![FrmJobs].[JobID]

Exit_Command214_Click:
Exit Sub

Err_Command214_Click:
MsgBox Err.Description
Resume Exit_Command214_Click

End Sub

The above code works on my old database but not on my new database. I get the following message "Object does not support this property or method"

Does anyone know of any alternative code I could use to get the above to work?

Thanks David.

 

[spikepl]

When asking for help, provide the error message (which you did) and provide the line that triggered it (which you didn't). You cannot debug properly with an error handler running - it's like flying blind. Comment it out.

 

[DaveCollins181]

Hi Spikepl, it does not give the option to open the debug window as no line triggers the message (Usually the line at fault is highlighted in yellow in the debug window but not in this case?) It just displays the error message. If we ignore my code, do you know any code that I can use to get the same result? Thanks, David.

 

[spikepl]

You cannot debug properly with an error handler running - it's like flying blind. Comment it out.

I did not put this message in just to pass the time - if there are things you do not understand (that's fair enuff) then ask - don't just ignore them (because that is just silly)!

throw out this line:
On Error GoTo Err_Command214_Click

 

[DaveCollins181]

Hi Spikepl, I appreciate you taking the time to look at my question. You are correct I do not fully understand why the code is not working as I'm not that familiar with VBA and took the code from an old database. I am looking for an alternative piece of code that will do the same job on my new database. If you know some code that could do what I am trying to achieve then that would be great as mine currently isn't working. Cheers David.

 

[spikepl]

The way this works is that the helper asks questions or makes suggestions, and the helpee responds to the questions or follows the suggestions.

If you simply repeat what you've already written then this undertaking does not move forward and I cannot help you.

Good luck.

 

[DaveCollins181]

Hi Spikepl, I just noticed the following line in your earlier post which said:

throw out this line:
On Error GoTo Err_Command214_Click

I have done this and I now get the option to debug. The error message I now get is Run-Time Error 438 Object doesn't support this property or method. The line that triggered the error is:

Forms![FrmPurchaseOrders].[JobID] = Forms![FrmJobs].[JobID]

Both forms and fields are named correctly. The current code now reads

Private Sub Command214_Click()
Me.Refresh

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FrmPurchaseOrders"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec
Forms![FrmPurchaseOrders].[JobID] = Forms![FrmJobs].[JobID]

Exit_Command214_Click:
Exit Sub

End Sub

Regards David.