Use form text box search to pull up query recordset (1 Viewer)

[mctimoth]

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

 

[theDBguy]

Hi Tim. When and if you can, it might be helpful if you can post a small (demo version) copy of your db to help us better understand your situation.

 

[mctimoth]

@theDBguy - It would be difficult to minimize. It uses all linked tables to a SQL back-end.

 

[theDBguy]

@theDBguy - It would be difficult to minimize. It uses all linked tables to a SQL back-end.

We just need to see the part where you're having a problem or need help. Are you able to create a demo of that section then?

 

[mctimoth]

Here goes. I pretty much took everything out that was not necessary. I did leave some cleaned records in the tables so the queries and forms show data. The AfterUpdate for frmPackingSlipHeader.JOB is the only code. That is new. If you strike that you will see the form can use Access search Ctrl+F and pull up records and advance and retreat through them.

 

[theDBguy]

Here goes. I pretty much took everything out that was not necessary. I did leave some cleaned records in the tables so the queries and forms show data. The AfterUpdate for frmPackingSlipHeader.JOB is the only code. That is new. If you strike that you will see the form can use Access search Ctrl+F and pull up records and advance and retreat through them.

Okay, thanks. I have one more favor to ask. I have your db open right now. Can you please give me a step-by-step instruction on how to duplicate your problem? (I got lost reading your original post.)

 

[mctimoth]

Sure theDBguy. My hope is to be able to type in the JOB text box a valid job number (string) and AfterUpdate (ie hitting Enter), qryMAIN_Master is queried and if the string entered in frmPackingSlipHeader.JOB exists in qryMAIN_Master, its recordset would populate the form, if it did not exist, the string in frmPackingSlipHeader.JOB would be the string used to start a new recordset and would be entered in MAIN.JOB along with other recordset entries as the form's fields were completed. Currently and without the AfterUpdate the only way to find an existing job is to use Access'es Find (Ctrl+F) which is a little too much with having to remember which switches to use and whether to search the beginning or whole field and even which field to search. Unknowing users find Access'es Find dialog difficult to get right and often it ends up spinning endlessly because the correct set of choices and switches were not selected.

Btw, thanks for your time.

 

[theDBguy]

Sure theDBguy. My hope is to be able to type in the JOB text box a valid job number (string) and AfterUpdate (ie hitting Enter), qryMAIN_Master is queried and if the string entered in frmPackingSlipHeader.JOB exists in qryMAIN_Master, its recordset would populate the form, if it did not exist, the string in frmPackingSlipHeader.JOB would be the string used to start a new recordset and would be entered in MAIN.JOB along with other recordset entries as the form's fields were completed. Currently and without the AfterUpdate the only way to find an existing job is to use Access'es Find (Ctrl+F) which is a little too much with having to remember which switches to use and whether to search the beginning or whole field and even which field to search. Unknowing users find Access'es Find dialog difficult to get right and often it ends up spinning endlessly because the correct set of choices and switches were not selected.

Btw, thanks for your time.

Hi. Thanks for the clarification. Take a look at the attached modified version of your db and try out the new Textbox I added to your form.

 

[mctimoth]

Umm... theDBguy... I don't see a "new" text box. Did you make changes to an existing one perhaps? Or am I missing something?

 

[theDBguy]

Umm... theDBguy... I don't see a "new" text box. Did you make changes to an existing one perhaps? Or am I missing something?

I added it to the header section of the form frmPackingSlipHeader.

 

[mctimoth]

Umm... I do see that the Back Color has been set to black for Form Header but I do not see ANY controls. I dragged and dropped a selection box over the whole area and nothing becomes selected, just in case it was all transparent. I do not see a text box in the Form Header. I've expanded Form Header down to 7.667 inches and see nothing other than black Back Color and unobstructed grid lines and dots.

 

[mctimoth]

Regardless of seeing your new text box, are you telling me that the same text box cannot be used to search qryMAIN_Master.JOB and receive the returned JOB field string in the case of an existing, found recordset?

 

[theDBguy]

Umm... I do see that the Back Color has been set to black for Form Header but I do not see ANY controls. I dragged and dropped a selection box over the whole area and nothing becomes selected, just in case it was all transparent. I do not see a text box in the Form Header. I've expanded Form Header down to 7.667 inches and see nothing other than black Back Color and unobstructed grid lines and dots.

Interesting. Try the attached again and tell me if you still don't see it. Incidentally, the black background wasn't me. It was already black when I got it.

 

[theDBguy]

Regardless of seeing your new text box, are you telling me that the same text box cannot be used to search qryMAIN_Master.JOB and receive the returned JOB field string in the case of an existing, found recordset?

You can use the exiting Textbox; however, I would advise against it, because it is bound to the field in your table. That means when you enter anything in that Textbox, you are telling Access that is the new value that you want for that record.

 

[mctimoth]

I still cannot see the text box in Design or Form view!

I will see if having the searched text box alone in the header will meet expectations.

 

[theDBguy]

I still cannot see the text box in Design or Form view!

I will see if having the searched text box alone in the header will meet expectations.

Hi. Let me ask you this, when you opened the last database I sent you, did the form open by itself, or did you have to manually open it?

 

[mctimoth]

No forms opened automatically. I manually opened frmPackingSlipHeader.

 

[theDBguy]

No forms opened automatically. I manually opened frmPackingSlipHeader.

Okay, that tells me you are not opening the file I gave you. How exactly were you downloading it and how are you opening it?

 

[mctimoth]

I opened the attachments attached to your messages sent at 16:00 and 16:48. I clicked on the attachment in Edge which went to my Downloads folder. I then extracted the files into the default folder in Downloads and then opened the accdb. Nothing ground breaking there, nothing out of the norm.

 

[theDBguy]

I opened the attachments attached to your messages sent at 16:00 and 16:48. I clicked on the attachment in Edge which went to my Downloads folder. I then extracted the files into the default folder in Downloads and then opened the accdb. Nothing ground breaking there, nothing out of the norm.

Okay, then I don't understand, because I made sure the second attachment I sent you will automatically open the form right away, so you won't have to manually open it. As one more test, I am resending the same file as a third attachment. But this time, I am changing the name of the file, just in case it's conflicting with the same copy you already have on your machine. Let me know what happens, please.