Insert record based on fields on form

[Guest]

Hi,

I have a form based on a table called Quotes when the user changes the
status field on the form to Order, I would like to insert a new record into
the Order table. My key field on the Order table is QuoteNum. This is the
code I have now which I based on postings from this newsgroup. The code is
not inserting anything into the Order table.

Any help is appreciated.
Thank you,
Karen


Private Sub QuoteStatus_Change()
If QuoteStatus.Value = "2" Then
strSQL = "INSERT INTO Order (CustomerID, JobName, QuoteNum, ManufacturerID,
Notes)"
strSQL = strSQL & "VALUES ( " & [Forms]!

![CustomerID].Value
strSQL = strSQL & ", " & [Forms]!

![JobName].Value
strSQL = strSQL & ", " & [Forms]!

![QuoteNum].Value
strSQL = strSQL & ", " & [Forms]!

![ManufacturerID].Value
strSQL = strSQL & ", " & [Forms]!

![Notes].Value
strSQL = strSQL & ") "

End If
End Sub

 

[Larry Daugherty]

Hi Karen,

Unless there are other machinations you haven't mentioned, why not just make
Quotes and Orders the same table with a Status field and an accompanying
Date and Time that shows when it changed from a quote to an order?

HTH
--
-Larry-
--

Hi,

I have a form based on a table called Quotes when the user changes the
status field on the form to Order, I would like to insert a new record into
the Order table. My key field on the Order table is QuoteNum. This is the
code I have now which I based on postings from this newsgroup. The code is
not inserting anything into the Order table.

Any help is appreciated.
Thank you,
Karen


Private Sub QuoteStatus_Change()
If QuoteStatus.Value = "2" Then
strSQL = "INSERT INTO Order (CustomerID, JobName, QuoteNum, ManufacturerID,
Notes)"
strSQL = strSQL & "VALUES ( " & [Forms]!

![CustomerID].Value
strSQL = strSQL & ", " & [Forms]!

![JobName].Value
strSQL = strSQL & ", " & [Forms]!

![QuoteNum].Value
strSQL = strSQL & ", " & [Forms]!

![ManufacturerID].Value
strSQL = strSQL & ", " & [Forms]!

![Notes].Value
strSQL = strSQL & ") "

End If
End Sub

 

[Guest]

I thought about that, but I have other fields needed on the Order table and
form. That was next on my "to do" list. I would like to then open the order
form so the user can continue to enter the additional data.

Karen

Hi Karen,

Unless there are other machinations you haven't mentioned, why not just make
Quotes and Orders the same table with a Status field and an accompanying
Date and Time that shows when it changed from a quote to an order?

HTH
--
-Larry-
--

Hi,

I have a form based on a table called Quotes when the user changes the
status field on the form to Order, I would like to insert a new record into
the Order table. My key field on the Order table is QuoteNum. This is the
code I have now which I based on postings from this newsgroup. The code is
not inserting anything into the Order table.

Any help is appreciated.
Thank you,
Karen


Private Sub QuoteStatus_Change()
If QuoteStatus.Value = "2" Then
strSQL = "INSERT INTO Order (CustomerID, JobName, QuoteNum, ManufacturerID,
Notes)"
strSQL = strSQL & "VALUES ( " & [Forms]!

![CustomerID].Value
strSQL = strSQL & ", " & [Forms]!

![JobName].Value
strSQL = strSQL & ", " & [Forms]!

![QuoteNum].Value
strSQL = strSQL & ", " & [Forms]!

![ManufacturerID].Value
strSQL = strSQL & ", " & [Forms]!

![Notes].Value
strSQL = strSQL & ") "

End If
End Sub

 

[Larry Daugherty]

Hi Karen,

Now I'm mystified; what other fields in table Order than in table Quote? It
just seems that they'd be the same.

If you're sure that Quotes and Orders are that dramatically different then
you can indicate the change of status on your Quotes form and have the
action event:

dim ID as Long

open a recordset on tblOrders
add a record
one by one name the fields in rstOrder as
rstOrder!MyField=me!ControlName
rstOrder.Update

ID=rstOrder!OrderID

rstOrder.close
rstOrder=nothing

Open Form frmOrders Where OrderID = ID

HTH
--
-Larry-
--

I thought about that, but I have other fields needed on the Order table and
form. That was next on my "to do" list. I would like to then open the order
form so the user can continue to enter the additional data.

Karen

Hi Karen,

Unless there are other machinations you haven't mentioned, why not just make
Quotes and Orders the same table with a Status field and an accompanying
Date and Time that shows when it changed from a quote to an order?

HTH
--
-Larry-
--

Hi,

I have a form based on a table called Quotes when the user changes the
status field on the form to Order, I would like to insert a new record into
the Order table. My key field on the Order table is QuoteNum. This

is
the

code I have now which I based on postings from this newsgroup. The

code
is

not inserting anything into the Order table.

Any help is appreciated.
Thank you,
Karen


Private Sub QuoteStatus_Change()
If QuoteStatus.Value = "2" Then
strSQL = "INSERT INTO Order (CustomerID, JobName, QuoteNum, ManufacturerID,
Notes)"
strSQL = strSQL & "VALUES ( " & [Forms]!

![CustomerID].Value
strSQL = strSQL & ", " & [Forms]!

![JobName].Value
strSQL = strSQL & ", " & [Forms]!

![QuoteNum].Value
strSQL = strSQL & ", " & [Forms]!

![ManufacturerID].Value
strSQL = strSQL & ", " & [Forms]!

![Notes].Value
strSQL = strSQL & ") "

End If
End Sub