stuck and need SQL help

[Cordeiro82]

Hi all...
I am working on a customer orders/quotes database and I created two forms. CustomerMain (created using the customers table) and QuotesMain(created using the quotes table)(both these tables are linked ON customers.RecordID = quotes.CustRecordID).
The QuotesMain form pops up when the user clicks on a button in the customermain form and wants to add a quote for that customer.
The QuotesMain has the following fields
HEADER: CustomerNumber (=Forms!customermain!customerNumber)
DETAILS: QuoteNumber (Quotes.QuoteNumber)
QuoteDate (Quotes.QuoteDate)
EffectiveTo (Quotes.EffectiveTo)
MiscNotes(Quotes.MiscNotes)
EnvironmentalSavings(Quotes.EnvironmentalSavings)
The details section also contains a subform with the product details.

Now the Problem... the problem I am having is when I enter a new record it saves the quoteNumber, QuoteDate, EffectiveTo, MiscNotes, and EnvironmentalSavings in the Quotes table but does not save the CustomerNumber in Quotes.CustomerNumber and I cant figure out the problem... prob cause i havent used access in ages.

I tried using an INSERT INTO statement in VB:

Dim strSQL As String

StrSQL = "INSERT INTO Quotes(CustomerNumber, QuoteNumber, QuoteDate, EffectiveTo, MiscNotes, EnvironmentalSavings) SELECT forms!customermain!customernumber, forms!quotesmain!quotenumber,...etc...

DoCmd.RunSQL strSQL

The code didnt seem to work.. it created two new records in the Quotes table instead.. one that only had the customernumber field populated and one that had the other 5 fields populated except the customernumber. I need all 6 fields in each record to be populated with the data entered into the QuotesMain form... do i need to include Quotes.CustRecordID or Customers.RecordID in there somewhere? if so where and how?

I have been at this all day and now into the night and I cant seem to figure it out.. could somebody please help me?

Thanks,
Kevin

 

[lupins46]

I would guess that you are saving the quotes record before you have saved the customer record.

 

[Cordeiro82]

Yes when I close the QuotesMain form it takes me back to the customerMain form. Is there anyway I can pass the data from Forms!customermain!customernumber... which is in both the header of the QuotesMain form and in the open CustomerMain form, to Quotes.CustomerNumber?

 

[PHV]

Have a look at OpenArgs

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886