Insert into multiple tables

[Noob_Za1B00t]

Hi all

I'm new to microsoft access and vba programming. So to start i made two tables. One called customers with these fields:
CustId(Primary Key)
Firstname
Lastname
Company
Email

The other called Orders with these fields:
OrderId(Primary Key)
OrderDate
CustId(Foreign Key)
PaymentMethod

I have two forms. In the customers form there are textboxes that get firstname, lastname etc and a button. In the onclick event on the button I have this code:

Private Sub cmdDone_Click()
    Dim mySql As String
    
    mySql = "INSERT INTO Customers (FirstName, LastName, Company, Email) VALUES (txtFirst.Value, txtLast.Value, txtCompany.Value, txtEmail.Value)"
    
    DoCmd.RunSQL mySql
    
    Forms![frmOrder]![cmbCust].Requery
    DoCmd.Close acForm, "frmCust", acSaveYes
End Sub

The thing I want to do is get a record entered into the orders table with the CustId matching the CustId in the Customers table when I click the button.

Any help would be appreciated.

 

[bob fitz]

When you click the button and create a new record in the Orders table, what values will be asigned to the PaymentMethod and OrderDate fields?
Is OrderID an Autonumber?

 

[Noob_Za1B00t]

Hey thanks for the reply.

OrderDate is a date/time type and payment method is a text type. And yes OrderId is an autonumber.

 

[bob fitz]

Hey thanks for the reply.

OrderDate is a date/time type and payment method is a text type. And yes OrderId is an autonumber.

So, what I am asking is: What values will be entered in the new record for OrderDate and PaymentMethod and where will those values come from. There doesn't seem much point in creating a new record without at least one of these values.

 

[Noob_Za1B00t]

Ah right. The OrderDate will be the current date. I will just enter that with the date() function. The payment method I haven't really looked at yet. I was thinking that maybe that can be amended to the record in another form once the record has been created.

 

[bob fitz]

IMO The way to do this is:
Create a form bound to the Customer table. On that form, create a sub form control bound to the Orders table. On the suf form: Set the Default Value of the OrderDate to Date(). OrderID will fill its self. If the linl between the forms is set correctly so will CusID. So, a new record will be created in the sub form just by entering a value in the PaymentMethod control. If you need a sample let me know.

 

[Noob_Za1B00t]

Yes a sample would be great if it's not too much bother

 

[bob fitz]

OK. Simple example db attached.
If you have any questions please post back.

 

[Noob_Za1B00t]

Bob, Thanks a million. That helped a lot.

 

[bob fitz]

Bob, Thanks a million. That helped a lot.

You're welcome. Glad to help.