Solved

Lookup data from access using VB6

Posted on 2007-03-20
34
275 Views
Last Modified: 2008-01-17
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.
0
Comment
Question by:seraph_matrix_631
  • 16
  • 11
  • 5
  • +1
34 Comments
 
LVL 39

Expert Comment

by:stevbe
ID: 18756714
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 18756721
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
 
LVL 39

Expert Comment

by:stevbe
ID: 18756806
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
 
LVL 1

Author Comment

by:seraph_matrix_631
ID: 18757078
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 18757119
why not try it on the after update event of the combo?
0
 
LVL 1

Author Comment

by:seraph_matrix_631
ID: 18757179
there isnt an "after update" option available.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 18757271
oh yeah VB6.
try the click event, guessing here. i don't have VB6 in this computer
0
 
LVL 26

Expert Comment

by:EDDYKT
ID: 18757341
Private Sub cboRentalTitle_Click()

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

Author Comment

by:seraph_matrix_631
ID: 18757371
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 18757410
seraph_matrix_631,
use the Click event of the combo box
0
 
LVL 1

Author Comment

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

Expert Comment

by:Rey Obrero
ID: 18757592
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
 
LVL 1

Author Comment

by:seraph_matrix_631
ID: 18757654
it comes up saying, "sub or function not defined"  and highlights  cboRentalTitle  and the  Private Sub cboRentalTitle_Click()
0
 
LVL 39

Expert Comment

by:stevbe
ID: 18757699
are you ADO or DAO?
0
 
LVL 1

Author Comment

by:seraph_matrix_631
ID: 18757733
how do i find that out?

sorry im a noob in visual basic 6.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 18757820
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
 
LVL 1

Author Comment

by:seraph_matrix_631
ID: 18757908
all i am using is the "Data Control" in vb6 sp6
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 26

Expert Comment

by:EDDYKT
ID: 18763103
>> 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
 
LVL 1

Author Comment

by:seraph_matrix_631
ID: 18886415
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
 
LVL 39

Expert Comment

by:stevbe
ID: 18892409
<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
 
LVL 1

Author Comment

by:seraph_matrix_631
ID: 18897464
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
 
LVL 1

Author Comment

by:seraph_matrix_631
ID: 18897764
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
 
LVL 39

Expert Comment

by:stevbe
ID: 18898417
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
 
LVL 1

Author Comment

by:seraph_matrix_631
ID: 18900553
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
 
LVL 39

Expert Comment

by:stevbe
ID: 18904513
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
 
LVL 1

Author Comment

by:seraph_matrix_631
ID: 18904652
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
 
LVL 39

Expert Comment

by:stevbe
ID: 18904793
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
 
LVL 1

Author Comment

by:seraph_matrix_631
ID: 18905792
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
 
LVL 39

Expert Comment

by:stevbe
ID: 18907381
Do you have the correct items listed in your comboboxes?

0
 
LVL 1

Author Comment

by:seraph_matrix_631
ID: 18912333
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
 
LVL 39

Expert Comment

by:stevbe
ID: 18917424
do you want to continue coding with DAO or do you want to switch to ADO?
0
 
LVL 1

Author Comment

by:seraph_matrix_631
ID: 18918960
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
 
LVL 39

Accepted Solution

by:
stevbe earned 500 total points
ID: 18919185
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
 
LVL 1

Author Comment

by:seraph_matrix_631
ID: 18920353
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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

759 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

21 Experts available now in Live!

Get 1:1 Help Now