Update ComboBox based off of another combobox

  • Thread starter Thread starter NeoFax99
  • Start date Start date
N

NeoFax99

I have a Combobox that shows a trackingnumber. I would like to be able
to select a trackingnumber and then four other comboboxes change based
on the info from the original trackingnumber combobox. The other
comboboxes are pulled from different tables than the one the
trackingnumber is in. Would I have to code the other comboboxes to
grab the info or vice-versa? Also, later I would like to be able to
add data in the trackingnumber and it fills the other comboboxes with
blank data until I input the information.
 
For the first question, see The Access Web article:

http://www.mvps.org/access/forms/frm0028.htm

For the second question, I don't use "place-holder" records (and the records
you want to add sound like "place-holders") and I haven't seen any ComboBox
filled with blank place-holders. I would suggest you think through the
process and design your interface + data so that your database behaves
similarly to most commercial applications ...
 
OK, not quite what I was looking for as it still shows blank values in
the other comboboxes even though if you look at the table a value is
there. Here is how I have the data:

tblAssett
tblAgency --> AgencyNumber
OrgNumber --> tblOrg
tblForm --> FormNumber
EmployeeNumber --> tblEmployee
TrackingNumber
...

So, there are 5 tables and the form is based off of the middle table.
There is a combobox for each of the outlying tables. i.e. Agency is a
combobox, Org is a combobox The TrackingNumber field is a combobox and
I would like to select the TrackingNumber and the AfterUpdate event
populates the other comboboxes with the value from the middle table
lookup. i.e. if tblAssett has a AgencyNumber of 1 then it shows the
first record Currently this does not happen and leaves the other
comboboxes blank.

As for the second request, I guess I didn't explain myself that well.
What I want is if the user adds a new form creating the TrackingNumber,
the form requires the individual to input the Agency Combo, Org combo,
Form combo... Otherwise, the database would contain holes. Thank you
for the help so far!
 
Are you saying that you want to use the TrackingNumber to *navigate* to the
record in the Table tblAssett that has that TrackingValue, assuming that the
TrackingNumber uniquely identify the Record?
 
Yes, once the user selects the TrackingNumber it then populates the
other fields based off of what tblAssett shows. However, the
comboboxes in the form are unbound for the AgencyName, OrgName,
FormName and EmployeeName.
 
Is the ComboBox for TrackingNumber bound to the Field TrackingNumber in the
Form's RecordSource or unbound?
 
In that case, use the Form_Current Event to assign the the Field values of
the current Record to the 4 unbound ComboBoxes ...

Code should be something like:

****
Private Sub Form_Current()

With Me
.cboAgencyNumber = .AgencyNo
.cboFormNumber = .FormNumber
...
End With

End Sub
****
 
OK, that didn't work. It errors out as it doesn't know what the info
is. The unbound box has a rowsource say for cboAgency is:

SELECT tblAgency.AgencyNumber, tblAgency.AgencyName FROM tblAgency;

Thanks for the help so far!
 
Do you have any Field in tblAssett (and the Form's RecordSource) that
corresponds to AgencyNumber in your usage? If you do and assume that the
BoundColumn of the ComboBox is Column 1 (i.e. AgencyNumber), then use this
Field in the assignment statement.

Remember that I can't see your database so what I post is just my normal
practice and you have to "translate" / adapt it into your database ...
 
Thanks! That did the trick. I just had to change the name in
tblAssett to FormNo as the name FormNumber was used in two different
tables, along with the other Number names.
 
Back
Top