Link textbox's (1 Viewer)

[Rick Stanich]

I have four textbox's, the first sets a value compared to a table. If the value entered match's a value in the table the user advances to the next textbox. The value entered in the next textbox needs be evaluated to the relationship of the previous table.

Textbox1 = Part Number
TextBox2 = Part Revision

This code looks at the table for part number.

Private Sub sPartNumber_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblParts", "txtPartNo = '" & Me.sPartNumber.Text & "'") Then
    'MsgBox "Match found" 'for testing
    'create link to next textbox here?
Else
    Cancel = False
    MsgBox "The part number doesn't exist in this database. Please re-enter the part number."
    'return focus to sPartNumber textbox
End If
End Sub

Textbox2 value is to be evaluated by th erelationship of textbox1 value and not the entire table for Part Revision.

Private Sub sPartRev_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblPartRev", "txtRev = '" & Me.sPartRev.Text & "'") Then
    'MsgBox "Match found" 'for testing
Else
    Cancel = False
    MsgBox "The revision level doesn't match part numbers in this database. Please re-enter the revision level."
    'return focus to sPartRev textbox
End If
End Sub

Strikingly similar eh!?

I do not know how to write the code to set the relationship to tblParts from tblPartRev.
As is, tblPartRev evaluates the entered value to its entire set of data regardless of tblParts.

This making sense? (Its killing me).


(Code samples courtesy of RuralGuy)

 

[Galaxiom]

I think you could do this more easily (and intuitively for the user) in the form design using comboboxes instead of code.

With Limit to List set to Yes only allowed values can be entered.

The user can simply choose the item they require or start typing to trigger the autocomplete.
The second box uses the value in the first box in the criteria of its Row Source query.
Search the site for cascaded combobox.

 

[Rick Stanich]

Thanks for the reply.
I am working with both scenerios, teaching myself "how to". I have the combo boxes and they have been set to "Limit to List", unfortunately this still allows a wrong choice. The first combo box has more than one choice, thus the other three combo boxes have lists consisting of data related to the main table.
My goal in this is to set the first parameter (combo box1) and have the remaining parameters based on that selection.
Same for textbox as in my original posting.
If textbox1 is this value then textbox2 can only allow values related to textbox1. etc...

Then again, I may have the combo boxes setup all wrong.

 

[Galaxiom]

You need cascading combos.

The row source query of the subsequent boxes is set to only get the records which match the selection in the previous combo.

It will usually be something in the style of:

SELECT DISTINCT RevisionID FROM RevisionTable WHERE [DocumentFK] = [previouscombo]

The OnChange or AfterUpdate procedure of the combo includes a command to requery the next in the cascade.

But search the site for cascading combos. It is a very common request and has been thoroughly covered

 

[boblarson]

But search the site for cascading combos. It is a very common request and has been thoroughly covered

And if you want a sample of a cascading combo, check out my sample from my website.

 

[Rick Stanich]

Thank you Galaxiom and boblarson for your input.

 

[boblarson]

Thank you Galaxiom and boblarson for your input.

We are happy to help