Command Button - Visual Basic Problem

[DaveCollins181]

I have created a command button on a form called FrmJobs. It opens a form called FrmPurchaseOrders, adds a new record and gives the JobID field in FrmPurchaseOrders the same entry as the JobID field from the form FrmJobs:

The following Event Procedure works on my previous database but when I try on my new database, I get an error message (See below for error):-

Private Sub 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

ERROR MESSAGE: Microsoft Visual Basic - Run-time error '438': Object doesn't support this property or method

The above script work up until the point Forms![FrmPurchaseOrders].[JobID] = Forms![FrmJobs].[JobID]. Does anyone have any idea how to overcome? If there is a Macro or some other code that would provide the same result then that would be great.

Thanks, David

 

[RuralGuy]

Does it also fail if you use:
Forms![FrmPurchaseOrders].[JobID] = Me.JobID

 

[DaveCollins181]

Hi RuralGuy - Yes it gives the same run-time error 438 with:

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

David

 

[RuralGuy]

Let's eliminate corruption. Start by *importing* your db into a fresh, new db. It only takes a few seconds. http://btabdevelopment.com/how-to-import-everything-into-a-new-mdb-file/

 

[DaveCollins181]

I created a new DB and imported all the forms, queries etc. The same error occurs on the new database. I tried shutting the PC down and re-starting incase something was wrong with MS Access. I have also tried renaming the individual forms as this has sometimes cured errors in the past. None of this seems to resolve the issue?

 

[RuralGuy]

Do you have any BOGUS Missing References?
What does
MsgBox Forms![FrmPurchaseOrders].[JobID]
...show?

 

[RuralGuy]

Do you have a control on the FrmPurchaseOrders Form that displays the JobID and is the name of the control "JobID"?

 

[DaveCollins181]

Yes the control on both forms is JobID.

I'm not sure about missing references RE: MsgBox Forms![FrmPurchaseOrders].[JobID]

I copied the original code from another database and changed the control name references so there may be something missing?

 

[RuralGuy]

It is not a good idea to name a control the same as the field to which it is bound. Access can get confused.
How to check for missing references: http://btabdevelopment.com/how-to-check-for-missing-references/

 

[The_Doc_Man]

If you get the runtime error and it offers you the chance to debug, click that button.

Now hover the mouse over the right-hand side of your attempt to copy the JobID from one form to the other. If you hover over the JobID of the Forms![FrmJobs].[JobID], you should see a value for JobID. Do you? If so, the problem is the left-hand side of the expression.

 

[RuralGuy]

@Doc: I think because of post #3 that the problem is with the left side of the equation.

 

[DaveCollins181]

After pressing debug, the following line is highlighted in yellow:
Forms![FrmPurchaseOrders].[JobID] = Forms![FrmJobs].[JobID]

When I hover over JobID of both references above I see the following:

acNewRec=5

I checked for missing references but couldn't find anything?

 

[RuralGuy]

That does not sound right. It should only show what is in the "JobID" field.

 

[RuralGuy]

Is JobID a text field or a numeric one?

 

[DaveCollins181]

It is a numeric field in both tables. Thanks for your help so far. I have left the office now so will continue to try and resolve tomorrow. Dave

 

[DaveCollins181]

Perhaps my original coding is incorrect.

Is there any coding or macro that I can apply to a button to perform the same action that I am trying to achieve. Eg: Click a command button from Form "A" that opens Form "B" and gives the JobID in form "B" the same value as JobID in Form "A"? (Both fields are numeric)
Table - Jobs - [JobID] is the key field AutoNumber (One Side)
Table - PurchaseOrders - [JobID] is the linked field and is numeric (Many Side)
Any assistance would be appreciated.
Thanks, Dave

 

[RuralGuy]

Here's the method I prefer: http://www.baldyweb.com/OpenArgs.htm

 

[DaveCollins181]

Thanks RG, I will give this a try. Thanks for your help on this issue. Dave

 

[Libre]

My method:
(may not be the BEST method but it has always worked for me)

First of all, avoid interacting with physical controls on the forms. Seems to me to be more efficient to use variables and write directly to tables or queries, instead of trying to write to a text box control on a form. You can READ from a text box but avoid writing to any data bound control in a form. You will get errors - like the one you are experiencing.

So the way I do what you are asking about (funny but I was doing this exact thing myself when I got tired and figured I'd take a break and check out the forum to see if there were any interesting questions).

First of all in a code module you declare a global variable:

 Global giJobID as Integer

Now you can assign a value to that variable from anyplace in your code, and the value will be known anywhere else in your code.

So when you hit the command button, first you read the JOB ID from the Purchase Orders form and then create a new record in the related table:

giJobID = txtJobID.Value
Set rst = CurrentDb.OpenRecordset("tblJobs")
rst.AddNew
rst![JobID] = giJobID
rst.Update

At that point you can open the new form using whatever method you normally use to open a specific record - I have my method for doing that as well.

I omitted it for clarity, but I also generally prevent NULL errors by using the Nz function, like so:

giJobID = Nz(txtJobID.Value, 0)

This will assign the number 0 to the variable in case the text box happens to be blank - then you write the code to avoid trying to write to the table, such as:

 giJobID = Nz(txtJobID.Value, 0)
If giJobID<>0 Then
       Set rst = CurrentDb.OpenRecordset("tblJobs")
.
.
.

and so on.

For a string variable, instead of 0 for a null text box, use "" in the Nz like so:

gsJobID = Nz(txtJobID.Value, "")

Anyway - I've read a lot and did go to programming school but as far as Access and VBA I'm mostly self taught.

 

[DaveCollins181]

Hi Libre, thanks for the coding. If I can't get RGs solution to work then I'll give yours a try. I don't have any programming experience but I'll give it a go. I'm trying to get a new DB in place as the old one I created years back has had its day. I'm nearly there with the new one so just tweaking it a bit to avoid the inevitable call from my work colleagues to say something isn't working