[RESOLVED] [Urgent] Problem in linking my CarRegNo to my database

[macky^^]

The problem arise is in the invoice form where i used this coding to retrieve data from my database. In the coding below, the combo1,text is retrived from an invoice table.

Private Sub Form_Load()
    Set Inv = New Class3 ' The text field and combo text is link to my invoice database
    Set Text1.DataSource = Inv
    Text1.DataField = "InvoiceID"
    Set Text2.DataSource = Inv
        Text2.DataField = "DateRent"
    Set Text3.DataSource = Inv
    Text3.DataField = "DateReturn"
    Set combo1.DataSource = Inv
    combo1.DataField = "CustID"

combo1 is the CarRegNO in MS access and is the foreign key in invoice table and also a primary key in car details. The car details in linked 1 - M relations to the invoice table.

Private Sub Combo1_Click() '<--- this combo1 is open from a car details table
Call connectDB
    If Combo1.Text <> "Select Car Reg No" Then
        sql = "SELECT DISTINCT * FROM CarDetails WHERE CarRegNo='" & Combo1.Text & "'"
        rs.Open sql, cn, adOpenStatic, adLockOptimistic, adCmdText

The same combo1 text, but they are open in different tables using ADODB connection. How can i form a relationship between them?

[macky^^]

Pls give a try to my query
Thanks.

[dee-u]

I don't really get what's your problem, would you elucidate? Are you looking for JOIN?

[macky^^]

I don't really get what's your problem, would you elucidate? Are you looking for JOIN?

maybe it's a join problem.
Cos i used different query commands to open different tables. For example, I used 1 query to open custID in customer table and 1 used 1 query to open carregno in cardetails. However, in my main form, i used 1 query to open my invoice table, which consist of custiD and carregno as the foreign key.

[Hack]

What do you mean by "form a relationship"?

[macky^^]

What do you mean by "form a relationship"?

Sorry to trouble you every night

sorry, i mean the link my custID from customer table with custid from invoice table and the link my carregno from cardetails table with carregno from invoice table

hence, i think i need to do a join sql ....

[Slaine]

Is this what you mean (see attached for sample app)

[macky^^]

hi Slaine,

Thanks for that sample file, but i'm looking for a join sql sample.

[Slaine]

An example of a join would be:

SELECT Car.*, Invoice.* FROM Invoice LEFT JOIN Car ON Invoice.CarReg = Car.CarReg WHERE InvoiceId = *somenumber*

But that won't magically link your combo box to your invoice - if that's what you are wanting.

[macky^^]

yes, how can i link the combo to my invoice?

[Slaine]

See my previous code.

You load the combo-box with the possible values from the database, then when you load the invoice you set the selected item in the combo-box to what ever is specified in the fetched invoice record.