Solved

Use Combobox to navigate records

Posted on 2004-09-14
22
236 Views
Last Modified: 2012-05-05
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
Comment
Question by:berm100
  • 11
  • 9
22 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12059550
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
 

Author Comment

by:berm100
ID: 12060465
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
 

Author Comment

by:berm100
ID: 12060468
Also, I am trying to navigate to a record on the current form

Berm
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12060481
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
 

Author Comment

by:berm100
ID: 12060593
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12060608
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
 

Author Comment

by:berm100
ID: 12060899
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12063466
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
 

Author Comment

by:berm100
ID: 12063873
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12064063
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:berm100
ID: 12067469

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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12067497
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
 

Author Comment

by:berm100
ID: 12067815
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12068055
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
 

Author Comment

by:berm100
ID: 12068479
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12068544
Hmm, it should - could you post your new modified code?
0
 

Author Comment

by:berm100
ID: 12068677


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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12068761
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
 

Author Comment

by:berm100
ID: 12068989
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
 

Author Comment

by:berm100
ID: 12069314
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
 

Accepted Solution

by:
modulo earned 0 total points
ID: 14147972
PAQed with points refunded (250)

modulo
Community Support Moderator
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now