Use Combobox to search for record on different form

[sbuckingham25]

Hi,

I am basically creating a "menu" form, where the user can find an existing job to continue to update by selecting it in a combobox. I have created a query with the JobID, Orgname, Location and job closed checkbox. The job will only display if the job is still open.

I want the Job form to open to that exact record (using the JobID) when the user selects it.

I am not sure how to go about it.

Thanks in advance!

 

[pbaldy]

See if this is what you're after:

http://www.baldyweb.com/wherecondition.htm

 

[sbuckingham25]

I am getting a pop-up box asking for the parameter value of "TxtTMJobID"

Here is my code:
Private Sub CmbExistingJob_AfterUpdate()
DoCmd.OpenForm "FrmTime&Materials", , , "TxtTMJobID = '" & Me.CmbExistingJob & " '"
End Sub

Suggestions?

 

[pbaldy]

You want the name of the field in the table there. Based on your first post, I would guess that to be JobID.

 

[sbuckingham25]

Ok I changed that and now have:

Private Sub CmbExistingJob_AfterUpdate()
DoCmd.OpenForm "FrmTime&Materials", , , "JobID = '" & Me.CmbExistingJob & " '"
End Sub

And now get a runtime error of '2501'

 

[pbaldy]

Is JobID a text data type in the table? If so, try getting rid of the space before the last single quote. If it's numeric, then get rid of the single quote before and everything after the form reference. You might have to bracket the form name too, given the inadvisable symbol in it.

 

[sbuckingham25]

I am sorry Paul for being a pain, but I did realize it was a numeric field. I removed the single quotes but still not working. Now getting a Run time 3075 error:

Private Sub CmbExistingJob_AfterUpdate()
DoCmd.OpenForm "FrmTime&Materials", , , "JobID = " & Me.CmbExistingJob & ""
End Sub

I hate being such a novice at this. Should the brackets be on the FrmTime&Materials?

 

[sbuckingham25]

Sorry, it is:

Private Sub CmbExistingJob_AfterUpdate()
DoCmd.OpenForm "FrmTime&Materials", , , "JobID = " & Me.CmbExistingJob
End Sub

 

[pbaldy]

Try this

DoCmd.OpenForm "[FrmTime&Materials]", , , "JobID = " & Me.CmbExistingJob

If that doesn't work, can you post a sample db?

 

[sbuckingham25]

Still didn't work, now getting that the form is misspelled...hmm....

I attached the db the form the combobox is in is the FrmExistingJob

 

[pbaldy]

The problem is the bound column of the combo box is 3 (location I think it was). Either change that to 1 or this is working as is:

DoCmd.OpenForm "FrmTime&Materials", , , "JobID = " & Me.CmbExistingJob.Column(0)

I thought it was not working until I realized you have it going to a new record, so the form appears blank when it opens.

 

[sbuckingham25]

Awesome!! While on the subject of it going to a new record. How can I have it go to a new record when it is opened by my switchboard button "new job", but not go to a new record with this search? Is that possible?

 

[pbaldy]

I don't use switchboards, but I think you can control the data mode of the form being opened. I'd set it to open in add mode from the switchboard, and normal from here. Now that I look at it, you already have it set up in the switchboard to open in add mode, so try just taking out that macro.

 

[sbuckingham25]

Perfect....thank you thank you!!