Lookup data from access using VB6

I am using VISUAL BASIC 6 as the front end application linking through to a Microsoft Access database.

I was wodnering how i would lookup information stored in a table in access and display the results on the fly.


i have a data control called:     navRentals
linking to:                              Database.mdb

I then have a combobox called:  cboRentalTitle
this is populated with rentals from   (Database.mdb) tblRentals.RentalTitle   by using the following code (On form load)

***
Do
    cboRentalTitle.AddItem (navRentals.Recordset.Fields("RentalTitle").Value)
    navRentals.Recordset.MoveNext
Loop Until navRentals.Recordset.EOF
***

what i am after is a way to LOOKUP the 'AgeCert' value and the 'Price'  from  tblRentals based upon the value selected in the combo box (this is needing to be updated if a new rental is selected in the combo box - i think its combo.change property?)


I have a command button that adds the selected film title to a list box. (lstRentedItems).
I have the ability to add and remove rentals using 2 buttons - this also deducts the price.

many thanks
Skulls.
LVL 1
seraph_matrix_631Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

stevbeCommented:
I forget ... do comboboxes in VB have multiple columns? If so you can make the additional columns hold that info and set ther widths = 0 so the user can't see them and then geting the value form thos columns is a s easy as Me.cboRentals.Column(1) and Me.cboRentals.Column(2)

Steve
0
Rey Obrero (Capricorn1)Commented:
use the after update event of the combo
private sub combo_afterupdate()
dim ac, p
ac=dlookup("AgeCert","tblRentals","RentalTitle='" & me.combo &"'")

p=dlookup("Price","tblRentals","RentalTitle='" & me.combo &"'")

end sub
0
stevbeCommented:
if you can pump the values into multiple columns you could push back through your nav control ...


navRentals.RecordsetClone.FindFirst "RentalTitle='" & Me.cboRentalTitle
curPrice = CCur(navRentals.RecordsetClone.Fields("Price").Value)
strAgeCert = CCur(navRentals.RecordsetClone.Fields("AgeCert").Value)

if nav object does not have recordsetclone then you could use the .Recordset directly.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

seraph_matrix_631Author Commented:
they dont seem to work. tried all three on the   Private Sub cboRentalTitle_Change()

what am i doing wrong?
just to clarify this is a form in BISUAL BASIC 6 not Access.
Access is only used to store and retreive data.

many thanks
skulls
0
Rey Obrero (Capricorn1)Commented:
why not try it on the after update event of the combo?
0
seraph_matrix_631Author Commented:
there isnt an "after update" option available.
0
Rey Obrero (Capricorn1)Commented:
oh yeah VB6.
try the click event, guessing here. i don't have VB6 in this computer
0
EDDYKTCommented:
Private Sub cboRentalTitle_Click()

navRentals.Recordset.MoveFirst
navRentals.Recordset.Find "RentalTitle='" & cboRentalTitle.Text & "'"
Debug.Print navRentals.Recordset.Fields("Price").Value
0
seraph_matrix_631Author Commented:
i have tried creating a command button and putting it on there. still no luck.


Private Sub Command2_Click()
navRentals.Recordset.FindFirst "RentalTitle='" & Me.cboRentalTitle
curPrice = CCur(navRentals.Recordset.Fields("Price").Value)
stragecert = CCur(navRentals.Recordset.Fields("AgeCert").Value)

txtAgeCert.Text = stragecert
txtPrice.Text = curPrice
End Sub

0
Rey Obrero (Capricorn1)Commented:
seraph_matrix_631,
use the Click event of the combo box
0
seraph_matrix_631Author Commented:
i get the error: this action was cancelled by an associated object"
<clicked debug>
Highlights Line  --  navRentals.Recordset.FindFirst "RentalTitle='" & Me.cboRentalTitle
0
Rey Obrero (Capricorn1)Commented:
try this

private sub cboRentalTitle_click()
dim ac, p
ac=dlookup("AgeCert","tblRentals","RentalTitle='" & me.cboRentalTitle &"'")

p=dlookup("Price","tblRentals","RentalTitle='" & me.cboRentalTitle &"'")

end sub
0
seraph_matrix_631Author Commented:
it comes up saying, "sub or function not defined"  and highlights  cboRentalTitle  and the  Private Sub cboRentalTitle_Click()
0
stevbeCommented:
are you ADO or DAO?
0
seraph_matrix_631Author Commented:
how do i find that out?

sorry im a noob in visual basic 6.
0
stevbeCommented:
What is the name of the control type you used to create the navRentals connection object? VB6 can be a bear when talking to data, the data binding controls are notoriously slow, you are better off creating the connecitons and pulling dta directly with ADO or ADO instead.


<sorry im a noob in visual basic 6>
why start learning VB6 instead of VB.NET?
Or why not even use Access itself?
0
seraph_matrix_631Author Commented:
all i am using is the "Data Control" in vb6 sp6
0
EDDYKTCommented:
>> navRentals.Recordset.FindFirst "RentalTitle='" & Me.cboRentalTitle


you need to close the single quote

i.e

navRentals.Recordset.FindFirst "RentalTitle='" & Me.cboRentalTitle & "'"

or

navRentals.Recordset.Find "RentalTitle='" & Me.cboRentalTitle & "'"
0
seraph_matrix_631Author Commented:
I have taken on board what you have all said and have converted the system to using ADO.

currently this is how i am calling my recordsets.

*** CODE START ***

Set WS = DBEngine.Workspaces(0)
                        Set db = DBEngine.OpenDatabase("Database.mdb", False, False, ";PWD=")
                        Set rs = db.OpenRecordset("tblRentals", dbOpenTable)
                   
                        rs.AddNew
                        rs("Media") = txtMedia.Text
                        rs("RentalTitle") = txtRentalTitle.Text
                        rs("AgeCert") = txtAgeCert.Text
                        rs("TotalDisks") = txtTotalDisks.Text
                        rs("Released") = txtReleased.Text
                        rs("Price") = txtPrice.Text
                       
                        rs.Update
                        rs.MoveFirst
                        rs.Close
                           
                        MsgBox ("Rental Title Added Sucessfully"), vbInformation, "Rental Added"
*** CODE END***

This works fine. However I am having issues on the transaction screen i have setup. Now when you open the transaction form  (Form Name:  RENTAL)  from the switchboard  (Form Name: Switchboard) this loads code to hide all text from the customer recordset which is still using a VB6 Data control and all fields are linked to that data control. With this said an input prompt is shown asking for the customer name, with this input it finds the first customer based upon the name entered and shows the details.

We are now under that members account. (when a film is rented the CustomerID is logged and that is gained from the input box prompt etc)



I am currently getting the  cboRentalTitles  to populate from the database using this code:

*** CODE START ***
Set WS = DBEngine.Workspaces(0)
Set db = DBEngine.OpenDatabase("Database.mdb", False, False, ";PWD=")
Set rs = db.OpenRecordset("tblRentals", dbOpenTable)
   
Max = rs.RecordCount
If rs.RecordCount = 0 Then
        Exit Sub
Else
        rs.MoveFirst
End If

cboRentalTitle.Clear
For i = 1 To Max
            cboRentalTitle.AddItem rs!RentalTitle
            rs.MoveNext
Next i
*** CODE END ***


HOWEVER....
This is where my problems start. I have a database   (Database.mdb)  which is int he same folder as my project. And this has a table called    TBLRENTALS     and    TBLRENTALCOPIES.


I need the system to populate the combo box    cboRentalTitles with all rentals that have an available copy, and then in    cboRentalCopy to show the available copies.



TBLRENTALS:
RentalID, Media, RentalTitle, TotalDisks, AgeCert, Released


TBLRENTALCOPY
RentalID, CopyNumber, Available



I have spent a week trying different ways to do this and have finally given up and come to you experts as a last resort. I will be upping the question point allocation as this is a nightmare for me to do so may need some explaining along the way.  thanks a bunch!


*** INFORMATION NEEDED ***

Visual Basic 6: Service Pack 6     --   this is what i am using to make the front end of my system

Microsoft Access 2007                --   this is the database management software im using as the backend data store

ADO  -- this is what i am using to link to Access for the most part only exception is the "CUSTOMER DETAILS" on the transaction form, which is using the built in data control in Visual Basic 6




0
stevbeCommented:
<converted the system to using ADO.>
all of the code you posted is DAO not ADO.

to populate you comboboxes you can make a connection to your access database just like you did in the last code you posted and then loop through the resulting recordset, adding them to your combobox using the .AddItem method. I *think* the problem you are having is that you need to create a query that joins the two tables together to get the results you want.

open Access, add both tbales to a query, make sure there is a join line between the RentalID fields on both tables. Now drag the RentalID and RentalTitle from tblRentals onto the fields section, drag the Available field onto the field section and add = True for the criteria for that field. Save the query. Now you can use that query name directly when opening your recordset to get the available titles like this ...

Set rst = dbs.OpenRecordset("MyQuery")
0
seraph_matrix_631Author Commented:
Ah sweet!

Need to test it but that seems logical. I tried to use that and it wouldn't work so i was racking my brains as to how i would get a rental rented.


I am still uclear how i would return a rental that had been rented by a customer. Any suggestions as i am sure your expertise exceeds mine! Certinally as far as VB6 goes! this is the first time i have used VB6 ever so i dont think i am doing too badly being mainly self taught   :-D


what is the difference between ADO and DAO?
0
seraph_matrix_631Author Commented:
Rental Form     On Load Event:
========================

'Populate The Rental Titles Combo Box
Set WS = DBEngine.Workspaces(0)
Set db = DBEngine.OpenDatabase("Database.mdb", False, False, ";PWD=")
Set rs = dbs.OpenRecordset("qryAvailableRentals")
   
Max = rs.RecordCount
If rs.RecordCount = 0 Then
        Exit Sub
Else
        rs.MoveFirst
End If

cboRentalTitle.Clear
For i = 1 To Max
            cboRentalTitle.AddItem rs!RentalTitle
            rs.MoveNext
Next i


cboRentalCopy.Clear
For i = 1 To Max
            cboRentalCopy.AddItem rs!CopyNumber
            rs.MoveNext
Next i
=============

it is highlighting:   Set rs = dbs.OpenRecordset("qryAvailableRentals")

Error:       Runtime Error '424':
                Object Required

                [ End ]    [ Debug ]    [ Help ]



Not sure why this is happening, and with the above code will "cboRentalCopy" be populated with all availavble copies based upon the rental title selected in   CBORENTALTITLE
0
stevbeCommented:
either use db or dbs as your variable name ... make sure you add Option Explicit at the very top of all of your code modules, this will help you catch these kind of errors.
Set db = DBEngine.OpenDatabase("Database.mdb", False, False, ";PWD=")
Set rs = dbs.OpenRecordset("qryAvailableRentals")


and you can shorten you code a whiole bunch ...

Do While Not rs.EOF
    Me.cboRentalTitle.AddItem(rs.Fields("RentalTitle").Value
    rs.MoveNext
Loop
0
seraph_matrix_631Author Commented:
that partially worked.

the option explicit helped trap a few errors and meant adding a few more lines of code.

it adds all the rental titles in the query however it adds duplicate values.

================
qryAvailableRentals
================

rental ID         RentalTitle         CopyNumber        Available        Released       AgeCert      Price
===========================================================================
1                    SAW Trilogy      001                        yes                2006               18           £5.00
1                    SAW Trilogy      002                        no                   2006              18           £5.00
2                    Open Season    001                        yes                 2005              12           £3.95

the combo box has  SAW Trilogy TWICE and one of Open Season  if i was getting the data from this example. and the rental copies is not being populated using the following code


Do While Not rs.EOF
    Me.cboRentalCopy.AddItem(rs.Fields("CopyNumber").Value
    rs.MoveNext
Loop

Just has a blank combo box   -- what am i doing wrong?
0
stevbeCommented:
hold on a second ... do you have a table that is only the unique list of Titles? If not you should. Perhaps we should review you data structure before continuing because if it is not normalized then you will be fighting it every step along the way of making your app.

tblRentalTitle
RentalTitleID
RentalTitle
ReleaseYear
AgeCert
Price

tblRentalCopies
RentalTitleID
CopyNo
DateIn
DateOut
CustomerID

tblCustomer
CustomerID
LastName
FirstName
Address1
Address2
etc ...
0
seraph_matrix_631Author Commented:
TBLRENTALCOPIES
================
CopyNo
RentalID
CustomerID
DateIn
DateOut
Available


RELATIONSHIP = Many  to   1    with  tblRENTALS  
ONE rental can have MANY copies


tblRENTALS
==========
RentalID
Media
RentalTitle
TotalDisks
AgeCert
Released
Price

RELATIONSHIP:  1  to MANY with tblTRANSACTION
ONE Rental (consisting of a copyno) can be in MANY transactions


tblTRANSACTION
==============
TransactionID
TransactionDate
RentalID
CopyNumber
CustomerID
AnountDue
Method
AmountTendered
ChangeDue
DateDueBack


Relationship:  ONE to MANY with tblCustomer
ONE customer can have MANY transactions consisting of many rentals and many copies **


** i can only get it to rent out 1 dvd at a time for some reason   :-S but thats an issue for another time!


As far as i am awaree this database is normalised to third notmal Form
0
stevbeCommented:
Wouldn't you want your DateIn / DateOut to only be in your transactions table?Available is really just the rentals that do not have transaction DateOut without a DateIn. You already have CustomerID in your transactions so it is a duplicate to put it in RentalCopies. Now that you have me thinking about this some more unless you need to add more attributes to individual rental copies I don't see why you can't just add a CopyQty field to tblRental and drop the RentalCopies table entirely.

With the structure you have now you could add a DISTINCT to your query that you are using to get a list of Available titles.

Steve
0
seraph_matrix_631Author Commented:
OK that would work as an idea o help simplify matters

I have an ADO Data control and then a combo box for the rental titles (cboRentalTitles)

i have tried to put code on the combobox_change and combobox_click property to get the Price, AgeCert and CopyQty but it will only do it if i have the controls linked to the ADO Data Control's recordset and i need to click the ADO navigation to cycle through the rentals...this is a long winded way to find a rental as im sure you'd agree.

just not sure how i would get the data to be populated when i click on a rental tille in the combo box. i have tried the suggestions mentioned above to do this and they did not work.



many thanks
0
stevbeCommented:
Do you have the correct items listed in your comboboxes?

0
seraph_matrix_631Author Commented:
i have all the controls linked to the ADO_Rentals control
if i use the navigation bar the data changes according to what is in the table.


but i would like to use cboRentalTitle to search for the record data instead of the data control
how do i do this?
0
stevbeCommented:
do you want to continue coding with DAO or do you want to switch to ADO?
0
seraph_matrix_631Author Commented:
i am confused as to what i am coding in.

i am using an ADO Data Control and using code to pupulate the data using that ADO Data Control.

I dont understand how i am using DAO.
Still new to Visual Basic 6. Whatever coding method is easiest i would like to be corrected if my coding is wrong or if it can be optimized. No good creating a system if its all over the place.


I am still using the built in  Data Control for VB 6 for some aspects of the system such as finding a record as i do not know how to do this using ADO....which i am now being told is DAO.
0
stevbeCommented:
I am not telling you anything other than the code you have posted is DAO not ADO, I can help you write the code using either library ... I just want you to define what it is you want to use.

Why are you using VB6 instead of VB.NET or even Access?

as for the original question .... in the click event of you combobox ...

Private Sub cboTitle_Click()
    Dim dbs As DAO.Database    
    Dim strDBPath As String
    Dim curPrive As Currency
    Dim strAgeCert As String


    strDBPath = "c:\ ..."     '<-- path to your database, including file name
    Set dbs = DBEngine(0).OpenDatabase(strDBPath)
    Set rst =  dbs.Openrecordset("SELECT AgeCert, Price FROM tblRENTALS WHERE RentalTitle = '" & Me.cboTitle & "'"

    Msgbox "Title=" & rst.Fields("RentalTitle").Value
    Msgbox "Price=" & rst.Fields("Price").Value


    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = nothing
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
seraph_matrix_631Author Commented:
Thank you this worked.
i will look into ADO and DAO more closely and try and decipher what i am doing in the application.

Thank you for your help so far.
very much appreciated.


Skulls
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.