Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

Use Combobox to navigate records

Hi experts,

Here is my question,

I have a table with a related form.  The key data fields are vendor, distribution method, and year.  There is a vendor combobox that is unbound to the table that has the vendor names.  There is a distribution combobox that has 3 choices (retail, mail, specialty).  Each vendor/distribution combination can have different deal years: generally it will be year's 1-3.  The vendor combobox is unbound but there is textbook on the form that is bound to the table.  The textbox is vendorid which is tied to the vendor name.

For the user of the form:

If the user selects a specific vendor using the vendor combobox, I want Access to first check if there is a a record for that vendor with whatever distribution (mail, retail, or specialty) method is selected on the distribution combobox.  If there is I want tthe table to go to year 1 of the vendor/distribution combination.

If the user selects a specific distribution method using the distribution combobox, I want Access to first check if there is a a record for that distribution method with whatever vendor that  is selected on the vendor combobox.  If there is I want tthe table to go to year 1 of the vendor/distribution combination.

Is this possible?

Thanks!

Berm
0
berm100
Asked:
berm100
  • 11
  • 9
1 Solution
 
shanesuebsahakarnCommented:
OK, if I can just verify your design - all of your "navigation" combo boxes are unbound, right?

Are you trying to navigate to a record on the *current* form or open up a new form that contains the matching records?
0
 
berm100Author Commented:
I have 2 navigation combo boxes.  One is for vendor; the other is for distribution.  Only the vendor CB is unbound.

This approach is actually based on a previous question you answered for me relating to the same project where you suggested I create a vendor table (the one table) that is joined to the deal table (the many table).  Thus the reason the vendor box is unbound is because the join is on vendor number and I want the user to be able to select a vendor name rather than a vendor number.  I want the user to select a vendor name via the cb and use code to bind the correct vendor number to the record.

For background, each vendor can have retail, mail, or specialty deals.  Each deal has multiple years (generally 3) because the contract is multi year.  Its not just that each vendor can have the 3 deal types.....they all will have the 3 deal types.

Thanks so much for your help

Berm
0
 
berm100Author Commented:
Also, I am trying to navigate to a record on the current form

Berm
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
shanesuebsahakarnCommented:
I recall the question - is the distribution combo unbound as well?

You can build a filter string like this:
Dim strFilter As String

If Not IsNull(Me!cboVendorID) Then strFilter="[VendorID]=" & Me!cboVendorID & " AND "
If Not IsNull(Me!cboDistribution) Then strFilter="[Distribution]='" & Me!cboVendorID & "' AND "
If strFilter<>"" Then strFilter=Left$(strFilter,Len(strFilter)-5)

Now you have a string that contains a criteria for the vendor ID and distribution. You mention that the user should select a vendor name not a number. You can achieve this in the same combo by making it a two-column combo with the ID in the first (hidden) column and the name in the second column. The user will see only the name, but the actual value stored in the combo will be the ID. Now you can use this filter string to navigate to the correct record in the current form with something like:

Me.RecordsetClone.FindFirst strFilter
If Not Me.RecordsetClone.NoMatch Then Me.Bookmark=Me.RecordsetClone.Bookmark

Or open a new form containing the record(s) you are interested in via:
DoCmd.OpenForm "MyForm", , , strFilter
0
 
berm100Author Commented:
The distribution is bound.  The only reason the vendor was unbound was to deal with the vendor name.....to allow the user to select a vendor name rather than number..

How do you set up a multi column combobox?
0
 
shanesuebsahakarnCommented:
If you use the wizard, choose the primary key (vendor ID) and the vendor name when you get to the page that lets you pick fields, and choose "hide key column" on the page after that (I think).

To do it manually, set the combo box's row source to an SQL statement that selects the two fields from your table, set the ColumnCount to 2 and set the column widths to:
0;5cm

This would hide the first column and make the visible column 5cm wide. Make sure the Bound Column is set to 1.

Note that if you are using the combos to find an existing record, they should really both be unbound, and you should provide a separate set of combos for data entry.
0
 
berm100Author Commented:
Hi...why do you say both combos should be unbound?  Why are unbound preferred?  Also, if I do this, is your filter suggested above still applicable?  Thanks
0
 
shanesuebsahakarnCommented:
Think of it this way - as soon as you put something into the combo, it replaces the data in the underlying table. If you were on a record belonging to Vendor 1, and you changed the combo, what you've actually done is to change the record to belong to Vendor 2. Equally, if you're on a new record and you enter "Retail" into the other combo, the database doesn't know if you want to create a new record with a "Retail" distribution value or navigate to an existing record with the same.

Navigation controls should definitely not be used for data entry, so they need to be unbound so you don't have to include a whole bunch of additional logic to prevent records from accidently being changed.
0
 
berm100Author Commented:
Hi....great points.  If I use unbounded combos to navigate the form, what is the best way to populate the underlying record with the necessary data.

One thing I may not have made clear was that if there is no underlying record to go to then I want to create a new record.  For example, if someone chooses vendor A with retail and there is no record matching this, then I want to create a new record.  In this case, how do I get the distribution method on the underlying record if the combo is unbounded.  Thanks
0
 
shanesuebsahakarnCommented:
You'd use code like this to add a new record:

Me.RecordsetClone.FindFirst strFilter
If Not Me.RecordsetClone.NoMatch Then
   Me.Bookmark=Me.RecordsetClone.Bookmark
Else
   DoCmd.GotoRecord , , acNewRec
End If

If your form is based on the table with all of the records you need, the above will navigate to the existing record (if one exists) or else a new record (if it doesn't exist). You need to provide other (bound) combos where the user actually enters the vendor and distribution channel. I'd put the navigation combos in the form header and distinguish them in some way.
0
 
berm100Author Commented:

Hi....Can you help me understand what this means?  I put this code in the change event of my unbound 2 coulmn vendor combobox.  This does navigate between vendors but only if the distribution combobox has a null or blank value in it.  Otherwise no navigation occurs.

What specifically do lines 1 and 2 mean, what are they doing, and should these be in my vendor combo.  If so, what should be in my distribution combo?



If Not IsNull(Me!cboVendorID) Then strFilter="[VendorID]=" & Me!cboVendorID & " AND "
If Not IsNull(Me!cboDistribution) Then strFilter="[Distribution]='" & Me!cboVendorID & "' AND "
If strFilter<>"" Then strFilter=Left$(strFilter,Len(strFilter)-5)


Thanks....Berm
0
 
shanesuebsahakarnCommented:
What the two lines do is that they build up a filter string. So if your Vendor combo and your Distribution combo contain 1 and Retail respectively, the string ends up looking like this:

[VendorID]='1' AND [Distribution]='Retail'

We then use this string to find the record which matches both criteria. Is this the behaviour you want?
0
 
berm100Author Commented:
Yes.  This  is the behavior I want.  In line 2 of the code, did you say Me!cbovendorID by mistake instead of Me!cbodistribution??
0
 
shanesuebsahakarnCommented:
Sorry, yes you're right, it should be cbodistribution in that line. You should also put exactly the same code into the AfterUpdate or Change event of the distribution combo as well.
0
 
berm100Author Commented:
Hi.....One thing I am finding is that the vendor combobox only changes vendors when the distribution combobox is null or has a blank entry in it.  If I select anything (such as retail, mail, etc) in the distribution combobox then changing the vendor combobox does nothing.

Why is this?

Berm
0
 
shanesuebsahakarnCommented:
Hmm, it should - could you post your new modified code?
0
 
berm100Author Commented:


Private Sub cbvendor_nav_Change()

Dim rs As DAO.Recordset
Dim strfilter As String

If Not IsNull(Me!cbvendor_nav) Then strfilter = "[vendorid]=" & Me!cbvendor_nav & " AND "
If Not IsNull(Me!CBDist_nav) Then strfilter = "[distribution]='" & Me!CBDist_nav & "' AND "
If strfilter <> "" Then strfilter = Left$(strfilter, Len(strfilter) - 5)

Me.RecordsetClone.FindFirst strfilter
If Not Me.RecordsetClone.NoMatch Then Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub
0
 
shanesuebsahakarnCommented:
That seems to indicate that it isn't finding any matches for records with that VendorID and that distribution method. Can you verify that those records exist with the distribution spelled exactly as you have it showing in the distribution combo? I'm assuming that the distribution field is a text field contain the words "Retail" etc.
0
 
berm100Author Commented:
Could it be because me deal table has 3 deal years for each distribution methods (year 1,2, or 3) and Access does not know which record to pull?  Do I also need to filter on deal year.

The deal table has vendors, distribution methods, and deal years as well as a lot of financial data for each particular row.

So when the combobox code asks Access to pull up the record with vendor A and distribution of mail, there is more than 1 row to pick up?  Is this the problem?
0
 
berm100Author Commented:
I figured it out.  The second line of the code is missing a "strfilter =" in the middle of the code so the string concatonates

The new code is:


Private Sub cbvendor_nav_Change()

Dim rs As DAO.Recordset
Dim strfilter As String

If Not IsNull(Me!cbvendor_nav) Then strfilter = "[vendorid]=" & Me!cbvendor_nav & " AND "
If Not IsNull(Me!CBDist_nav) Then strfilter = strfilter & " [distribution]='" & Me!CBDist_nav & "' AND "
If strfilter <> "" Then strfilter = Left$(strfilter, Len(strfilter) - 5)

Me.RecordsetClone.FindFirst strfilter
If Not Me.RecordsetClone.NoMatch Then Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub
0
 
moduloCommented:
PAQed with points refunded (250)

modulo
Community Support Moderator
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 11
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now