Lookup data from access using VB6

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

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

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.

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

why not try it on the after update event of the combo?

there isnt an "after update" option available.

oh yeah VB6.
try the click event, guessing here. i don't have VB6 in this computer

Private Sub cboRentalTitle_Click()

navRentals.Recordset.MoveFirst
navRentals.Recordset.Find "RentalTitle='" & cboRentalTitle.Text & "'"
Debug.Print navRentals.Recordset.Fields("Price").Value

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

seraph_matrix_631,
use the Click event of the combo box

i get the error: this action was cancelled by an associated object"
<clicked debug>
Highlights Line  --  navRentals.Recordset.FindFirst "RentalTitle='" & Me.cboRentalTitle

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

it comes up saying, "sub or function not defined"  and highlights  cboRentalTitle  and the  Private Sub cboRentalTitle_Click()

are you ADO or DAO?

how do i find that out?

sorry im a noob in visual basic 6.

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?

all i am using is the "Data Control" in vb6 sp6

>> 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 & "'"

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

<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")

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?

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

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

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?

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 ...

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

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

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

Do you have the correct items listed in your comboboxes?

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?

do you want to continue coding with DAO or do you want to switch to ADO?

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.

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