Building Form - Struggling on Updating a Textbox that Concatenates Multiple Form Field Values

[EFJK]

Hello,




I am building a lookup tool to find a specific product by serial number. We use two methods to do so at this time: 1) Using the full serial number if it is a "new style" serial number. Unfortunately, we also have an old style S/N that must be found via a series of questions. Upon the customer answering, I am filtering down the options to derive the part number that we combine with the Serial Number to look up their product.

Where I am struggling to complete the tool is when I've filtered down to a specific Part Number, but now need to manually enter the Serial Number in a text box and then afterwards have that then update another field with the fully concatenated Serial Number Value. An example below:



Here, I need to end with a value of "3640-1234", assuming the user entered "1234" into the Serial Number field. I then want the concatenated value to update the form based on the associated table values (not shown in the image, but just done as an AfterUpdate).


I've searched for while and just can't seem to figure it out. Any help is greatly appreciated.


Erik

[pbaldy]

In VBA code:

Me.ConcatenatedTextbox = Me.PartNumbertextbox & "-" & Me.SerialNumberTextbox

replacing with the appropriate control names. You can do it in the after update event of the serial number textbox.

[EFJK]

Ahh, I was close, but was using a list box for the Part Number. I switched it to a text box and used DLookUp to get the value needed and your solution worked great.

Now, what is the proper way to prompt the form to navigate to the record for that Serial Number? Should my "On Click" with the Submit button replace the combo box value at the top of the form as that is already a control for the form? I am running into trouble just trying to filter or navigate through the text boxt with the combo box still as the control on the form.

Thanks for the help!

[pbaldy]

If the combo has the "find a record" code/macro behind it, you should be able to copy it. The code that used to be produced by the wizard looked like this for a text value:

  Dim rs As Object

  Set rs = Me.Recordset.Clone
  rs.FindFirst "FieldName = '" & Me.TextboxName & "'"
  Me.Bookmark = rs.Bookmark

[EFJK]

Hi pbaldy,

My form is freezing up when attempting to do this through the button. It shouldn't be an issue of table size, should it? The combo box handles it just fine with 150,000 records.

[pbaldy]

No, though that's way more records than I'd ever have loaded at once. By a factor of 1000. What exactly is your code?

[EFJK]

Private Sub btnSubmit_Click()
Dim rs As Object

  Set rs = Me.Recordset.Clone
  rs.FindFirst "SerialNumber = '" & Me.txtFullSN & "'"
  Me.Bookmark = rs.Bookmark
  
End Sub

Where SerialNumber is the field of the Table that I'm searching in and txtFullSN is the name of the textbox that we've created the concatenated value in.

[pbaldy]

I don't see anything wrong with that. It just hangs? What's the code behind the combo? Can you copy/adapt that since it works?

[ssanfu]

PMFJI,

I've always used Me.RecordsetClone because at some point I tried Me.Recordset.Clone and had problems. Still not sure what the difference is.

Me.Recordset.Clone
Creates a duplicate Recordset object that refers to the original Recordset object.

Me.RecordsetClone
You can use the RecordsetClone property to refer to a form's Recordset object specified by the form's RecordSource property. Read-only.


Also, whenever you use "FindFirst", MS says you should check the "NoMatch" property to check the results.


You might try

Private Sub btnSubmit_Click()
    Dim rsClone As DAO.Recordset

    'Create a clone of the form's recordset
    Set rsClone = Me.RecordsetClone

     ' -------- for testing --------
    Msgbox.Print Me.txtFullSN
    ' -------- for testing --------

    'Search for a record
    rsClone.FindFirst "SerialNumber = '" & Me.txtFullSN & "'"

    'Test the result of the search
    If rsClone.NoMatch Then
        'NoMatch returned True (not a match)
        MsgBox "Record not found."
    Else
        'NoMatch returned False (found)
        'The clone's bookmark is now set to its current position
        'which is the row returned by the FindFirst method
        'Move the form's current cursor position
        'to the one pointed to by the clone's bookmark
        Me.Bookmark = rsClone.Bookmark
    End If

    Set rsClone = Nothing

End Sub

[EFJK]

It is able to complete the event, it just hangs for 5 minutes while it is processing. This has to be because of the table size, although I know larger companies index through tables with millions of records easier than this. How can I speed up the tool to make it useful?


Really appreciate the help, both of you!


Erik

[EFJK]

Is there anyone who can help with this issue? Are there workarounds I could/should look into, such as taking the concatenated value from the text box and upon clicking "Submit", copy and paste it into the combo box to then search for the record? The combo box continues to work great and quickly.

[pbaldy]

I would never have a form returning that many records. I have forms bound to tables with millions of records, but they're always opened with a wherecondition so they return a single record, or a very limited number.

In your case, I might have a form where they enter the desired criteria with a button to open a second form with a wherecondition applied so only that record is returned. Or an unbound form with a bound subform, where the subform's record source is set to SQL that returns that record (and starts out empty so it's not loading them all).