Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 14th, 2004, 11:31 AM
Registered User
 
Join Date: Dec 2004
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default how to browse a record through a combo box

I have a table whose data need to be viewed through the form. There are no primary key fields. The database is built to record the transactions of same 10 customers on a daily basis.So I have 10 customers IDs, for each day I can have those 10 customers ID only once. So When I choose date and customer ID through a combo box, the form should bring up all the fields for that customer Id on that day.

Any help one this.

Thanks
Reply With Quote
  #2 (permalink)  
Old December 15th, 2004, 08:49 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Can you lay out the table structure where you are doing the look up? And are you displaying results in a subform, or a form?

There should always be primary key fields. You can add one at any time. Just add a PK field, give it an autonumber, and then designate it the primary key. This is essential for indexing your data.

In any event, the problem with a combo box look up without a primary key, and correct me if I'm wrong, but you can only bind the combo box to one column of data. That column will either be the Customer, or the Date, but not both. This is a problem if you are relying solely on a superkey for look ups. Superkeys are a bad idea that dates back to pre-relational database days of big, flat databases. Typically the combo box binds on the PK, which you don't have. So I don't see how you are going to get this to work.

mmcdonal
Reply With Quote
  #3 (permalink)  
Old December 15th, 2004, 08:50 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Really, it wouldn't be any trouble to add a primary key and use it as your bound column on the combo box. That is the solution to this problem.

mmcdonal
Reply With Quote
  #4 (permalink)  
Old December 22nd, 2004, 08:49 PM
Authorized User
 
Join Date: Oct 2003
Location: Cleveland, OH, USA.
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Something else to keep in mind, one can retrieve more than one selected value from a combo or list box, provided that the RowSource contains 2 or more columns of data, regardless of the Control Source.

One of the other properties in a combo box control is the .Column property. For example, if you create a 2-column combo box (it can be bound or unbound as well), you can retrieve 2 columns of data from a selected row within the AfterUpdate property once the user has made a selection. A sample SELECT query used for the combo box's rowsource could be:

"SELECT DATE, CUSTOMER FROM CUSTOMER_ORDERS ORDER BY DATE, CUSTOMER"

Here, you have a 2-column combo box (ColumnCount = 2). You can use the AfterUpdate() property from the combo box to retrieve the two values from the user and display the selected row in a bound form like this:

Private Sub cboDateCust_AfterUpdate()
   Dim rs As DAO.Recordset

   Dim strSearchString As String
   Dim varDate As Variant
   Dim varCustomer As Variant

   varDate = Me.cboDateCust.Column(0)
   varCustomer = Me.cboDateCust.Column(1)

   ' to go to the selected row, assuming customer is numeric (no quotes)
   strSearchString = "DATE = #" & varDate & "# AND CUSTOMER = " & varCustomer

   Set rs = Me.RecordsetClone
   rs.FindFirst

   Me.Bookmark = rs.Bookmark
   rs.Close

End Sub

When using the .Column property in a combo or list box, enumeration starts with 0, which means the first column is Column(0), the second column is Column(1), etc.

Grant you, I'm a little bit of a dinosaur from the v1.1 days of MS Access using DAO here, but I haven't found a way to do bookmarking with ADO (open to suggestions, however).

If you DO use the "Dim rs As DAO.Recordset" as I have used in this example, be sure to check "Microsoft DAO 3.6 Object Library" (or "2.5/3.51", whichever is in your list) from Tools | References in your VBA editor.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Combo box to display items from parent combo box Gini Visual Studio 2008 0 June 18th, 2008 12:30 AM
Combo Box posting to Wrong Record alaxmen Access 1 February 3rd, 2006 12:56 PM
Find Record Use Combo Box martinaccess Access 2 October 17th, 2004 07:42 AM
Find Record Combo Box Wizard Won't Show 3rd Choice HenryE Access 0 February 2nd, 2004 11:52 PM
Combo box DisplayMember only shows on 1st record jraymond VB.NET 2002/2003 Basics 1 June 13th, 2003 09:51 AM



All times are GMT -4. The time now is 06:24 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.