Access Newbie here, so please bear with me....
I have a Jobs form. If the user wants to capture a repeat of a previous job, I have a button which opens a Search form (without closing the Jobs Form). When the user has selected the job they want to repeat, I want to add a new record to the Jobs table using the Job No selected, close the Search form and display the new record in the Jobs form. All of this happens when the user selects a client and then a job on the Search form and clicks on a Select button (btnCustSelect).
The following code is in the On Click event of the btnCustSelect.
Code:
'User is entering a repeat of a previous job
intRepeatJobNo = lstJobName
DoCmd.GoToRecord ObjectType:=acDataTable, ObjectName:="Jobs", record:=acNewRec
'Copy Values from existing record to new record
JobName = DLookup("JobName", "Jobs", "JobNo=" & intRepeatJobNo)
CustCode = DLookup("CustCode", "Jobs", "JobNo=" & intRepeatJobNo)
PFNo = DLookup("PFNo", "Jobs", "JobNo=" & intRepeatJobNo)
'Get the new Job number
intRepeatJobNo = Jobs.JobNo
Forms!frmJobs.RecordSource = "SELECT Jobs.*, Customer.CustName, Factory.FactoryName FROM Factory INNER JOIN (Customer INNER JOIN Jobs ON Customer.CustCode = Jobs.CustCode) ON Factory.FactoryCode = Jobs.FactoryCode WHERE Jobs.JobNo = intRepeatJobNumber"
Forms!frmJobs.Requery
SetJobDetailTrue
'Close the search form
DoCmd.Close acForm, Me.Name
When I run this, I get "Compile error: Variable not defined" on "JobName" (a field in the Jobs table) on this line:
JobName = DLookup("JobName", "Jobs", "JobNo=" & intRepeatJobNo)
I have tried it as Jobs.JobName - same error.
When I comment out this code
Code:
JobName = DLookup("JobName", "Jobs", "JobNo=" & intRepeatJobNo)
CustCode = DLookup("CustCode", "Jobs", "JobNo=" & intRepeatJobNo)
PFNo = DLookup("PFNo", "Jobs", "JobNo=" & intRepeatJobNo)
the error changes to "Error 2489 - The object 'Jobs' isn't open.
Surely if I haven't closed the Jobs form, the Jobs table should still be open? Or do I need to open it before I can add the new record? If so, how?
TIA