Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Table record updating - Very complicated
This will get complicated I'm sure.
I have a form which has a field which has this code for the afterupdate event:
If DSum("[Qty]", "tblFinGoods", "[PartN] = '" & Me.txtPart_No & "'") > 0 Then
txtAppliedInvQty = InputBox("Excess inventory exists for this part number. Qty = " & (DSum("[Qty]", "tblFinGoods", "[PartN] = '" & Me.txtPart_No & "'") - Nz(DSum("[AppliedInvQty]",
End If
This code is working just fine but the form needs to have code written to the onclose event of the form . That code needs to be:
If on the form the checkbox named "chkbxSerialize" is false then accept the number entered in the inputbox and write JUST ONE RECORD per the above code to a separate unbound table named tblCast. The fields that need to be written to that table are for example,
OpenOrdRecID = (from the open form named frmOpenOrders!txtRecId
Qty = (from the open form named frmOpenOrders!txtAppliedIn
Date = which will always be 10/10/2020
PartN = (from the open form named frmOpenOrders!txtPart_No
SerialUsed = False
and so forth. There are more fields but if someone can help me with the above I will fill them in.
But if the checkbox is true then I need to write ONE record for each entry in the input box. For example, if the user entered "4" then four separate records should be written to the table like:
OpenOrdRecID = (from the open form named frmOpenOrders!txtRecId
Qty = 1 (will always be "1" unlike the above code)
Date = which will always be 10/10/2020
PartN = (from the open form named frmOpenOrders!txtPart_No
Serial = "To Come"
SerialUsed = True (different than the above code)
and so forth. There are more fields but if someone can help me with the above, again I will fill them in.
If anyone can help with this I sure appreciate it. Thank you.
--Steve
I have a form which has a field which has this code for the afterupdate event:
If DSum("[Qty]", "tblFinGoods", "[PartN] = '" & Me.txtPart_No & "'") > 0 Then
txtAppliedInvQty = InputBox("Excess inventory exists for this part number. Qty = " & (DSum("[Qty]", "tblFinGoods", "[PartN] = '" & Me.txtPart_No & "'") - Nz(DSum("[AppliedInvQty]",
End If
This code is working just fine but the form needs to have code written to the onclose event of the form . That code needs to be:
If on the form the checkbox named "chkbxSerialize" is false then accept the number entered in the inputbox and write JUST ONE RECORD per the above code to a separate unbound table named tblCast. The fields that need to be written to that table are for example,
OpenOrdRecID = (from the open form named frmOpenOrders!txtRecId
Qty = (from the open form named frmOpenOrders!txtAppliedIn
Date = which will always be 10/10/2020
PartN = (from the open form named frmOpenOrders!txtPart_No
SerialUsed = False
and so forth. There are more fields but if someone can help me with the above I will fill them in.
But if the checkbox is true then I need to write ONE record for each entry in the input box. For example, if the user entered "4" then four separate records should be written to the table like:
OpenOrdRecID = (from the open form named frmOpenOrders!txtRecId
Qty = 1 (will always be "1" unlike the above code)
Date = which will always be 10/10/2020
PartN = (from the open form named frmOpenOrders!txtPart_No
Serial = "To Come"
SerialUsed = True (different than the above code)
and so forth. There are more fields but if someone can help me with the above, again I will fill them in.
If anyone can help with this I sure appreciate it. Thank you.
--Steve
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try this -
When adding to this, use delimiters appropriate for the data type -
Text needs quotes
Dates need hash marks (#)
Numbers get no delimiters
I'm assuming that Part_No is text, Qty is numeric, and OpenOrdRecID is numeric in this code.
dim strSQL As string
dim I as integer
If chkbxSerialize = TRUE then
strSQL = "INSERT INTO YOUR TABLE " _
& " (OpenOrdRecID, Qty , PartN, Serial, SerialUsed ) " _
& " VALUES ( " & frmOpenOrders!txtRecId & ", 1, #10/10/2020#, '" _
& frmOpenOrders!txtPart_No & "','To Come', TRUE)"
If frmOpenOrders!txtAppliedInvQty > 0 THen
For I = 1 to frmOpenOrders!txtAppliedInvQty
CurrentDB.execute strsql, dbFailOnError
Next
End if
Else
strSQL = "INSERT INTO YOUR TABLE " _
& " (OpenOrdRecID, Qty , PartN, SerialUsed ) " _
& " VALUES ( " & frmOpenOrders!txtRecId & ", " _
& frmOpenOrders!txtAppliedInvQty & ", #10/10/2020#, '" _
& frmOpenOrders!txtPart_No & "','To Come', False)"
CurrentDB.execute strsql, dbFailOnError
End if
When adding to this, use delimiters appropriate for the data type -
Text needs quotes
Dates need hash marks (#)
Numbers get no delimiters
I'm assuming that Part_No is text, Qty is numeric, and OpenOrdRecID is numeric in this code.
Here is what I have so far. I just don't know how to do the "looping" part in here...
'Code must loop here number of times = txtAppliedInvQty
Private Sub Form_Close()
If Me.txtAppliedInvQty > 0 Then
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("t
rs.AddNew
rs!OpenOrdRecID = Me.txtRecID
rs!Date = #10/10/2020#
rs!Cust = Me.txtCustomer_1
rs!PartN = Me.txtPart_No
rs!QtyGood = Me.txtAppliedInvQty
rs!QtyScrap = 0
rs!PO = Me.txtPurchase_Order
rs!AckDate = Me.txtAck_Date
rs!OrdQty = Me.txtOrder_Qty
rs!Serial = "To Come"
rs!SerialUsed = "True"
rs.Update
Else
'Code must loop here number of times = txtAppliedInvQty
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("t
rs.AddNew
rs!OpenOrdRecID = Me.txtRecID
rs!Date = #10/10/2020#
rs!Cust = Me.txtCustomer_1
rs!PartN = Me.txtPart_No
rs!QtyGood = 1
rs!QtyScrap = 0
rs!PO = Me.txtPurchase_Order
rs!AckDate = Me.txtAck_Date
rs!OrdQty = Me.txtOrder_Qty
rs!Serial = "To Come"
rs!SerialUsed = "True"
rs.Update
End If
Set rs = Nothing
DoCmd.OpenForm "frmMainMenu"
End Sub
'Code must loop here number of times = txtAppliedInvQty
Private Sub Form_Close()
If Me.txtAppliedInvQty > 0 Then
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("t
rs.AddNew
rs!OpenOrdRecID = Me.txtRecID
rs!Date = #10/10/2020#
rs!Cust = Me.txtCustomer_1
rs!PartN = Me.txtPart_No
rs!QtyGood = Me.txtAppliedInvQty
rs!QtyScrap = 0
rs!PO = Me.txtPurchase_Order
rs!AckDate = Me.txtAck_Date
rs!OrdQty = Me.txtOrder_Qty
rs!Serial = "To Come"
rs!SerialUsed = "True"
rs.Update
Else
'Code must loop here number of times = txtAppliedInvQty
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("t
rs.AddNew
rs!OpenOrdRecID = Me.txtRecID
rs!Date = #10/10/2020#
rs!Cust = Me.txtCustomer_1
rs!PartN = Me.txtPart_No
rs!QtyGood = 1
rs!QtyScrap = 0
rs!PO = Me.txtPurchase_Order
rs!AckDate = Me.txtAck_Date
rs!OrdQty = Me.txtOrder_Qty
rs!Serial = "To Come"
rs!SerialUsed = "True"
rs.Update
End If
Set rs = Nothing
DoCmd.OpenForm "frmMainMenu"
End Sub
Sorry, I posted my attempt right after you suggested. I will definitely try your way. I do see how the "looping works"
You can apply that for-next loop to the code you posted too (your recordset code looks good overall). If you do that, be sure to include a > 0 check for your txtQty. If that quantity is mis-entered as a zero, you'll have an infinite loop.
You should probably also beef this up with a null check -- ie: something like:
You should probably also beef this up with a null check -- ie: something like:
if Trim ("" & txtQty) = "" then
msgbox "Qty?"
Exit Sub
End if
Steve -
Just a thought here. If you are not real comfortable with embedding those delimiters in SQL strings, the recordset method you posted will probably be easier for you to add to/maintain.
Just a thought here. If you are not real comfortable with embedding those delimiters in SQL strings, the recordset method you posted will probably be easier for you to add to/maintain.
Steve,
1. Why are you using an Inputbox rather than a form to capture this quantity figure? By using the inputbox, you limit your ability to validate the number, what if someone enters the number 1000, or -3? You need to include a textbox for this value on your form.
2. you use the phrase "I have a form", do you mean frmOpenOrders? Are you using the AfterUpdate event of this form to insert this record(s) into the other table? If so, add the Excess Quantity textbox to that form.
3. You could do this insert operation with a loop, or with a single SQL statement. I prefer to use a single SQL statement, although the SQL will be a little more complicated.
a. First, you need a table (or a query) which contains as many records as you will possibly need to insert (ExcessQuantity). I do this with a table (tbl_Numbers) which contains a single field (lng_Number) and ten records (the values 0-9). With this table, I can create a query (qry_Numbers) that generates numbers as large as I want (for this example, I'll use 0-99). That query looks like:
SELECT Tens.lng_Number * 10 + Ones.lng_Number as lng_Number
FROM tbl_Numbers as Tens, tbl_Numbers as Ones
This will generate the numbers 0-99.
b. I would put some code in the forms BeforeUpdate event to ensure that if chkbxSerialize is checked, the user has entered a numeric value in the new textbox that is within parameters (in this case 1-100)
You will notice that in a couple of instances, I have used the IIF() function to determine either what to write to a particular field, or how many records to include in the Insert.
1. Why are you using an Inputbox rather than a form to capture this quantity figure? By using the inputbox, you limit your ability to validate the number, what if someone enters the number 1000, or -3? You need to include a textbox for this value on your form.
2. you use the phrase "I have a form", do you mean frmOpenOrders? Are you using the AfterUpdate event of this form to insert this record(s) into the other table? If so, add the Excess Quantity textbox to that form.
3. You could do this insert operation with a loop, or with a single SQL statement. I prefer to use a single SQL statement, although the SQL will be a little more complicated.
a. First, you need a table (or a query) which contains as many records as you will possibly need to insert (ExcessQuantity). I do this with a table (tbl_Numbers) which contains a single field (lng_Number) and ten records (the values 0-9). With this table, I can create a query (qry_Numbers) that generates numbers as large as I want (for this example, I'll use 0-99). That query looks like:
SELECT Tens.lng_Number * 10 + Ones.lng_Number as lng_Number
FROM tbl_Numbers as Tens, tbl_Numbers as Ones
This will generate the numbers 0-99.
b. I would put some code in the forms BeforeUpdate event to ensure that if chkbxSerialize is checked, the user has entered a numeric value in the new textbox that is within parameters (in this case 1-100)
Private Sub Form_BeforeUpdate (Cancel as Integer)
'Set the default value for the Cancel argument
Cancel = True
'Check to make sure that if chkbxSerialize is checked, there is a value in the field
'txtExcessQuantity and that the value is within parameters
if me.chkbxSerialize = True Then
if Len(me.txtExcessQuantity & "") = 0 then
msgbox "enter an Excess Quantity value!
me.txtExcessQuantity.SetFocus
Exit Sub
elseif Isnumeric(me.txtExcessQuantity) = False then
msgbox "Excess Quantity must be numeric between 1 and 100"
me.txtExcessQuantity.SetFocus
Exit Sub
elseif Val(me.txtExcessQuantity) < 1 then
msgbox "Excess Quantity value must be between 1 and 100"
me.txtExcessQuantity.SetFocus
Exit Sub
elseif Val(me.txtExcessQuantity) >100 then
msgbox "Excess Quantity value must be between 1 and 100"
me.txtExcessQuantity.SetFocus
Exit Sub
End If
End If
'If the code passes these tests, then set Cancel = False
Cancel = False
End Sub
Private Sub Form_AfterUpdate
Dim strSQL as string
'This assumes that your PartN field is a string
'It also assumes that the absence of the [Serial] field in the top part of your explanation
'means that that field should be NULL if chkbxSerialize = False
'I've included examples for the "other fields". If the field is numeric use syntax
'similar to the [FieldX] row; if a string, use the [FieldY] example
strSQL = "INSERT INTO tblCast (OpenOrdRecID, Qty, [Date], PartN, Serial, SerialUsed, [FieldX], [FieldY]) " _
& "SELECT " & me.txtRecID & ", " _
& iif(me.chkbxSerialize, 1, me.txtAppliedInvQty) & ", " _
& "#10/10/2020#, " _
& chr$(34) & me!txtPart_No & chr$(34) & ", " _
& iif(me.chkbxSerialize, "'To Come'","NULL") & ", " _
& iif(me.chkbxSerialize, -1, 0) & ", " _
& [FieldX] & ", " _
& chr$(34) & [FieldY] & chr$(34) & " " _
& "FROM qry_Numbers " _
& "WHERE [lng_Number] < " & iif(me.chkbxSerialize, me.txtExcessQuantity)
Debug.print strSQL
Currentdb.Execute strsql, dbfailonerror
End Sub
You will notice that in a couple of instances, I have used the IIF() function to determine either what to write to a particular field, or how many records to include in the Insert.
Sorry for the delay. Here is my code so far but it is not adding the record to tblCast if the part is serialized and the txtAppliedQty = 1 for example. Also, it is not adding the record if the part is NOT serialized and the txtAppiedQty is "5", for example. Can someone tell me what is wrong with my code?
Private Sub Form_Close()
'Routine to add records to tblCast if part is not a serialized part and applied qty is greater than 0
If chkbxSerialize = False And txtAppliedQty > 0 Then
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("tblCast")
rs.AddNew
rs!OpenOrdRecID = txtRecID
rs!QtyCast = txtAppliedInvQty
rs!Date = #10/10/2020#
rs!PartN = txtPart_No
rs!PO = txtPurchase_Order
rs!AckDate = txtAck_Date
rs!OrdQty = txtOrder_Qty
rs.Update
End If
'Routine to add records to tblCast if part IS a serialized part and applied qty is greater than 0
If chkbxSerialize = True And txtAppliedQty > 0 Then
rs.AddNew
For I = 1 To frmOpenOrders!txtAppliedInvQty
rs!OpenOrdRecID = txtRecID
rs!QtyCast = 1
rs!Date = #10/10/2020#
rs!PartN = txtPart_No
rs!PO = txtPurchase_Order
rs!AckDate = txtAck_Date
rs!OrdQty = txtOrder_Qty
rs!Serial = "To Come"
rs!SerialUsed = True
Next
rs.Update
End If
'Close the form
DoCmd.OpenForm "frmMainMenu"
End Sub
I also tried...
Private Sub Form_Close()
If Me.txtAppliedInvQty > 0 Then
'Routine to add records to tblCast if part is not a serialized part and applied qty is greater than 0
If chkbxSerialize = False Then
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("t
rs.AddNew
rs!OpenOrdRecID = txtRecID
rs!QtyCast = txtAppliedInvQty
rs!Date = #10/10/2020#
rs!PartN = txtPart_No
rs!PO = txtPurchase_Order
rs!AckDate = txtAck_Date
rs!OrdQty = txtOrder_Qty
rs.Update
End If
'Routine to add records to tblCast if part IS a serialized part and applied qty is greater than 0
If chkbxSerialize = True And txtAppliedQty > 0 Then
rs.AddNew
For I = 1 To frmOpenOrders!txtAppliedIn
rs!OpenOrdRecID = txtRecID
rs!QtyCast = 1
rs!Date = #10/10/2020#
rs!PartN = txtPart_No
rs!PO = txtPurchase_Order
rs!AckDate = txtAck_Date
rs!OrdQty = txtOrder_Qty
rs!Serial = "To Come"
rs!SerialUsed = True
Next
rs.Update
End If
End If
'Close the form
DoCmd.OpenForm "frmMainMenu"
End Sub
Private Sub Form_Close()
If Me.txtAppliedInvQty > 0 Then
'Routine to add records to tblCast if part is not a serialized part and applied qty is greater than 0
If chkbxSerialize = False Then
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("t
rs.AddNew
rs!OpenOrdRecID = txtRecID
rs!QtyCast = txtAppliedInvQty
rs!Date = #10/10/2020#
rs!PartN = txtPart_No
rs!PO = txtPurchase_Order
rs!AckDate = txtAck_Date
rs!OrdQty = txtOrder_Qty
rs.Update
End If
'Routine to add records to tblCast if part IS a serialized part and applied qty is greater than 0
If chkbxSerialize = True And txtAppliedQty > 0 Then
rs.AddNew
For I = 1 To frmOpenOrders!txtAppliedIn
rs!OpenOrdRecID = txtRecID
rs!QtyCast = 1
rs!Date = #10/10/2020#
rs!PartN = txtPart_No
rs!PO = txtPurchase_Order
rs!AckDate = txtAck_Date
rs!OrdQty = txtOrder_Qty
rs!Serial = "To Come"
rs!SerialUsed = True
Next
rs.Update
End If
End If
'Close the form
DoCmd.OpenForm "frmMainMenu"
End Sub
The close event might be too late for this code. Give this a try... add a commandbutton (cmdClose) to run this code and then close the form when it is done. Also note the correction I made in the second half of the code:
Private Sub cmdClose_Click()
'Routine to add records to tblCast if part is not a serialized part and applied qty is greater than 0
If chkbxSerialize = False And txtAppliedQty > 0 Then
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("tblCast")
rs.AddNew
rs!OpenOrdRecID = txtRecID
rs!QtyCast = txtAppliedInvQty
rs!Date = #10/10/2020#
rs!PartN = txtPart_No
rs!PO = txtPurchase_Order
rs!AckDate = txtAck_Date
rs!OrdQty = txtOrder_Qty
rs.Update
End If
'Routine to add records to tblCast if part IS a serialized part and applied qty is greater than 0
If chkbxSerialize = True And txtAppliedQty > 0 Then
For I = 1 To frmOpenOrders!txtAppliedInvQty ' Add new needs to come after the For statement
rs.AddNew
rs!OpenOrdRecID = txtRecID
rs!QtyCast = 1
rs!Date = #10/10/2020#
rs!PartN = txtPart_No
rs!PO = txtPurchase_Order
rs!AckDate = txtAck_Date
rs!OrdQty = txtOrder_Qty
rs!Serial = "To Come"
rs!SerialUsed = True
Next
rs.Update
End If
'Close the form
DoCmd.OpenForm "frmMainMenu"
Docmd.Close acForm, Me..Name
End Sub
This is not going to work in the Close event. By the time the close event has fired, the form has already unloaded and the of your controls will have been lost.
I would put it in the Click event of a "Close" button on your form. I've modified your code slightly so that you only have one loop.
I would put it in the Click event of a "Close" button on your form. I've modified your code slightly so that you only have one loop.
Private Sub cmd_Close_Click()
Dim intLoop as Integer
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("tblCast")
For intLoop = 1 to iif(me.chkbxSerialize = False and me.txtAppliedQty > 0, 1, me.txtAppliedInvQty)
rs.AddNew
rs!OpenOrdRecID = txtRecID
rs!QtyCast = iif(me.chkbxSerialize = False, me.txtAppliedInvQty, 1)
rs!Date = #10/10/2020#
rs!PartN = me.txtPart_No
rs!PO = me.txtPurchase_Order
rs!AckDate = me.txtAck_Date
rs!OrdQty = me.txtOrder_Qty
if me.chkbxSerialize = True Then
rs!Serial = "To Come"
rs!SerialUsed = True
end if
rs.Update
End If
'Close the form
docmd.close acform, me.name
DoCmd.OpenForm "frmMainMenu"
End Sub
If you are concerned that a user might close the form without clicking the "Close" button, you should add the following code to your form.
1. In the declarations section add:
Private AllowClose as Boolean
2. In the Form_Open event add:
AllowClose = False
3. In the cmd_Close_Click code, add the following just prior to calling the Close method
AllowClose = True
4. Then, in the Form_Unload event use:
Private Sub Form_Unload(Cancel as Integer)
If AllowClose = False then
msgbox "Click the 'Close' button to close this form!"
Cancel = true
End If
End Sub
The above steps will prevent your users from being able to close the form by clicking on the X in the upper right corner of the form.
1. In the declarations section add:
Private AllowClose as Boolean
2. In the Form_Open event add:
AllowClose = False
3. In the cmd_Close_Click code, add the following just prior to calling the Close method
AllowClose = True
4. Then, in the Form_Unload event use:
Private Sub Form_Unload(Cancel as Integer)
If AllowClose = False then
msgbox "Click the 'Close' button to close this form!"
Cancel = true
End If
End Sub
The above steps will prevent your users from being able to close the form by clicking on the X in the upper right corner of the form.
Still not writing the record to the tblCast. As a test I added a command button to the form just to write the record, not including the closing of the form and it still didn't work. Here's the code for the test command button...
Private Sub Command132_Click()
'Routine to add records to tblCast if part is not a serialized part and applied qty is greater than 0
If chkbxSerialize = False And txtAppliedQty > 0 Then
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("t
rs.AddNew
rs!OpenOrdRecID = txtRecID
rs!QtyCast = txtAppliedInvQty
rs!Date = #10/10/2020#
rs!PartN = txtPart_No
rs!PO = txtPurchase_Order
rs!AckDate = txtAck_Date
rs!OrdQty = txtOrder_Qty
rs.Update
End If
'Routine to add records to tblCast if part IS a serialized part and applied qty is greater than 0
If chkbxSerialize = True And txtAppliedQty > 0 Then
For I = 1 To frmOpenOrders!txtAppliedIn
rs.AddNew
rs!OpenOrdRecID = txtRecID
rs!QtyCast = 1
rs!Date = #10/10/2020#
rs!PartN = txtPart_No
rs!PO = txtPurchase_Order
rs!AckDate = txtAck_Date
rs!OrdQty = txtOrder_Qty
rs!Serial = "To Come"
rs!SerialUsed = True
Next
rs.Update
End If
End Sub
Private Sub Command132_Click()
'Routine to add records to tblCast if part is not a serialized part and applied qty is greater than 0
If chkbxSerialize = False And txtAppliedQty > 0 Then
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("t
rs.AddNew
rs!OpenOrdRecID = txtRecID
rs!QtyCast = txtAppliedInvQty
rs!Date = #10/10/2020#
rs!PartN = txtPart_No
rs!PO = txtPurchase_Order
rs!AckDate = txtAck_Date
rs!OrdQty = txtOrder_Qty
rs.Update
End If
'Routine to add records to tblCast if part IS a serialized part and applied qty is greater than 0
If chkbxSerialize = True And txtAppliedQty > 0 Then
For I = 1 To frmOpenOrders!txtAppliedIn
rs.AddNew
rs!OpenOrdRecID = txtRecID
rs!QtyCast = 1
rs!Date = #10/10/2020#
rs!PartN = txtPart_No
rs!PO = txtPurchase_Order
rs!AckDate = txtAck_Date
rs!OrdQty = txtOrder_Qty
rs!Serial = "To Come"
rs!SerialUsed = True
Next
rs.Update
End If
End Sub
Whoops - Your update is in the wrong place too. It should be before the Next:
Private Sub Command132_Click()
'Routine to add records to tblCast if part is not a serialized part and applied qty is greater than 0
If chkbxSerialize = False And txtAppliedQty > 0 Then
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("tblCast")
rs.AddNew
rs!OpenOrdRecID = txtRecID
rs!QtyCast = txtAppliedInvQty
rs!Date = #10/10/2020#
rs!PartN = txtPart_No
rs!PO = txtPurchase_Order
rs!AckDate = txtAck_Date
rs!OrdQty = txtOrder_Qty
rs.Update
End If
'Routine to add records to tblCast if part IS a serialized part and applied qty is greater than 0
If chkbxSerialize = True And txtAppliedQty > 0 Then
For I = 1 To frmOpenOrders!txtAppliedInvQty ' Add new needs to come after the For statement
rs.AddNew
rs!OpenOrdRecID = txtRecID
rs!QtyCast = 1
rs!Date = #10/10/2020#
rs!PartN = txtPart_No
rs!PO = txtPurchase_Order
rs!AckDate = txtAck_Date
rs!OrdQty = txtOrder_Qty
rs!Serial = "To Come"
rs!SerialUsed = True
rs.Update
Next
End If
End Sub
To fyed:
Using this code on my test command button I get...
"Compile Error: Method or data member not found"
Code:
Private Sub Command132_Click()
Dim intLoop As Integer
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("t
For intLoop = 1 To IIf(Me.chkbxSerialize = False And Me.txtAppliedQty > 0, 1, Me.txtAppliedInvQty)
rs.AddNew
rs!OpenOrdRecID = txtRecID
rs!QtyCast = IIf(Me.chkbxSerialize = False, Me.txtAppliedInvQty, 1)
rs!Date = #10/10/2020#
rs!PartN = Me.txtPart_No
rs!PO = Me.txtPurchase_Order
rs!AckDate = Me.txtAck_Date
rs!OrdQty = Me.txtOrder_Qty
If Me.chkbxSerialize = True Then
rs!Serial = "To Come"
rs!SerialUsed = True
End If
rs.Update
End If
'Close the form
DoCmd.Close acForm, Me.Name
DoCmd.OpenForm "frmMainMenu"
End Sub
Using this code on my test command button I get...
"Compile Error: Method or data member not found"
Code:
Private Sub Command132_Click()
Dim intLoop As Integer
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("t
For intLoop = 1 To IIf(Me.chkbxSerialize = False And Me.txtAppliedQty > 0, 1, Me.txtAppliedInvQty)
rs.AddNew
rs!OpenOrdRecID = txtRecID
rs!QtyCast = IIf(Me.chkbxSerialize = False, Me.txtAppliedInvQty, 1)
rs!Date = #10/10/2020#
rs!PartN = Me.txtPart_No
rs!PO = Me.txtPurchase_Order
rs!AckDate = Me.txtAck_Date
rs!OrdQty = Me.txtOrder_Qty
If Me.chkbxSerialize = True Then
rs!Serial = "To Come"
rs!SerialUsed = True
End If
rs.Update
End If
'Close the form
DoCmd.Close acForm, Me.Name
DoCmd.OpenForm "frmMainMenu"
End Sub
and if you are trying the shortened version that I posted, then you need to replace line 27:
End If
with:
Next
And then should probably add a line to close the recordset as well. Line 28 would be:
rs.Close
End If
with:
Next
And then should probably add a line to close the recordset as well. Line 28 would be:
rs.Close
Steve,
Try the changes mentioned above, then if you are still getting an error, indicate the line that the error is occurring on. My code assumes that the form this button is on, and the controls referenced are on on frmOpenOrders, and not on a subform within the main form.
It would be helpful if you could take a screen shot of the form in design view and post it here.
Try the changes mentioned above, then if you are still getting an error, indicate the line that the error is occurring on. My code assumes that the form this button is on, and the controls referenced are on on frmOpenOrders, and not on a subform within the main form.
It would be helpful if you could take a screen shot of the form in design view and post it here.
Check your control names.
You are using both txtAppliedInvQty and txtAppliedQty. Which one is correct? (replace as needed).
Also, add the following to the top of your form's code - right under Option Compare Database:
That will force you to declare all of your variables (and doing so will help you spot any typos).
You are using both txtAppliedInvQty and txtAppliedQty. Which one is correct? (replace as needed).
Also, add the following to the top of your form's code - right under Option Compare Database:
Option Explicit
That will force you to declare all of your variables (and doing so will help you spot any typos).
Premium Content
You need an Expert Office subscription to comment.Start Free Trial