I came across this Access World hit - forums/threads/find-record-in-table-and-populate-fields-on-form.34780 - that is as close as I have come to my issue after searching for about 16 hours! I have a similar issue. I may not even be barking up the right tree. I do want to bring up a queries' record in a form based on a form's text box searching on a field. The form - frmPackingSlipHeader - has text box JOB (I inherited it!) and other fields related to the Record Source - qryMAIN_Master. qryMAIN_Master.JOB (which actually comes from the MAIN table but I think is abstracted at this point) is the matching query field to be searched. I also want frmPackingSlipHeader.JOB, used to enter the searched string, to be the text box existing record's JOB string will come into, and will also be used to take the same JOB string entered and use that for a new JOB if searched JOB does not exist in qryMAIN_Master.JOB. I took the linked above code and changed it to meet my needs but am missing something. I have two issues: 1: When I step through the search using a valid existing JOB in the MAIN table, the record in qryMAIN_Master is not found and the form goes on to start a new record. I can see the valid string for JOB in both the MAIN table and qryMAIN_Master. Previously, before I used the AfterUpdate event on frmPackingSlipHeader.JOB, I could use Access'es search function and bring up the JOB and its recordset; now when I search, the existing record's (I think) JOB string comes up but the rest of the form is blank and it seems to bring up a record in the middle of the queries' dataset. I can navigate back and forth and see previous and subsequent records in the queries dataset but it seems when the initial search comes back, which should have returned a valid recordset, I only see the JOB string entered, like I say, as if to create a new record, seemingly in the middle of the queries' dataset. 2: Entries in JOB are alphanumeric and may contain hyphens (-), so I will need to accommodate for that. Right now when I enter a JOB with a hyphen (ie 163511-TB) I get the error: "Run-time error '3070: The Microsoft Access database engine does not recognize 'TB' as a valid field name or expression." Here's my code for the AfterUpdate event on frmPackingSlipHeader.JOB:
Private Sub JOB_AfterUpdate()
Dim rs As DAO.Recordset
Dim msg, style
If Job <> "" Then
Set rs = Me.RecordsetClone
rs.FindFirst "[JOB]=" & Me!Job
If rs.NoMatch Then
msg = "This record does not exist. Do you want to Make a new record?"
style = vbYesNo
If MsgBox(msg, style) = vbYes Then
Me!Job = Job
Else
Job = ""
End If
Else
Me.Bookmark = rs.Bookmark
End If
End If
End Sub
I will need to do the same on a separate text field in the form, frmPackingSlipHeader.txtSUIJob (a new field I have control over and developed) which will become academic when I can get this figured out.
TIA,
Tim
Private Sub JOB_AfterUpdate()
Dim rs As DAO.Recordset
Dim msg, style
If Job <> "" Then
Set rs = Me.RecordsetClone
rs.FindFirst "[JOB]=" & Me!Job
If rs.NoMatch Then
msg = "This record does not exist. Do you want to Make a new record?"
style = vbYesNo
If MsgBox(msg, style) = vbYes Then
Me!Job = Job
Else
Job = ""
End If
Else
Me.Bookmark = rs.Bookmark
End If
End If
End Sub
I will need to do the same on a separate text field in the form, frmPackingSlipHeader.txtSUIJob (a new field I have control over and developed) which will become academic when I can get this figured out.
TIA,
Tim
Last edited: