Populating a set number of subform records by click 1

[calpolymis1]

Hi. I don't know if this would be possible, but here is the scenario. I have tblOrder and tblDetail connected by OrderID. tblOrder has briefly CustomerPO, ContainerQty, and ItemCode. tblDetail has CustomerPOTrack, ItemCode_Detail. CustomerPOTrack is generated by CustomerPO by adding "-1, -2..". For example, if CustomerPO is 12345, then CustomerPOTrack goes like 12345-1, 12345-2... ItemCode_Detail in tblDetail gets the value in ItemCode mostly.

What I want to do is to generate a set number (a number in ContainerQty)of subform records by clicking a button. For exmaple, if I have CustomerPO 12345, ContainerQty 30, and ItemCode is CCC0101, then 30 subform records are automatically generated in a way that CustomerPOTrack starts from 12345-1 to 12345-30, ItemCode is all CCC0101 in the subform record.

How could I do this? Thanks for your comments in advance.

 

[formerTexan]

Hi,

Try something like the following on the button's click event. I made some assumptions on data types and where the values are coming from, so you'll need to amend for your situation. Also, its written on the fly, so check for typos.

Cheers,
Bill


Dim strCustomerPO as string
Dim strCustomerPOTrack as string
Dim strItemCode as string
Dim iCt as integer
Dim iContainerQty as integer
iCt = 1
strCustomerPO = Me!CustomerPO
iContainerQty = Me!ContainerQty + 1
strItemCode = Me!ItemCode


Do While iCt < iContainerQty
strCustomerPOTrack = strCustomerPO & "-" & iCt

"INSERT INTO tblDetail(CustomerPOTrack, ItemCode) VALUES ('" & strCustomerPOTrack & "','" & strItemCode & "');"

iCt = iCt + 1
Loop

 

[calpolymis1]

Hi, Bill. It works great! I just modified your code a little bit, and it works. The following is the code.
************************************************
Private Sub cmdParse_Click()
Dim strCustPO As String
Dim strCustPOTrack As String
Dim strCustOrderID As Integer
Dim strItemCode As String

Dim iCt As Integer
Dim iContainerQty As Integer
Dim strSQL As String

iCt = 1
strCustPO = Me!CustPO
strCustOrderID = Me!CustOrderID
iContainerQty = Me!ContainerQty + 1
strItemCode = Me!ItemCode


Do While iCt < iContainerQty
strCustPOTrack = strCustPO & "-" & iCt

Dim CurDB As Database
Dim rs As Recordset
Set CurDB = CurrentDb()

DoCmd.RunSQL ("INSERT INTO tblDetail(CustOrderID, CustPOTrack, ItemCode_Detail) VALUES ('" & strCustOrderID & "','" & strCustPOTrack & "','" & strItemCode & "')")
DoCmd.SetWarnings (WarningsOff)

'Debug.Print strSQL

Set CurDB = Nothing

iCt = iCt + 1
Loop

MsgBox ("Rcord parfing successful!")
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

End Sub
**********************************************************

Can I ask one more thing? The requirements have been changed a little bit, and I need to make 2 more sets of CustomerPOTrack, ItemCode and CustOrderID. I added number2 for naming in necessary places and tried your code. It didn't work though-no error msg, no update. The following is the code.
########################################################
Private Sub cmdParse2_Click()

Dim strCustPO2 As String
Dim strCustPOTrack2 As String
Dim strCustOrderID2 As Integer
Dim strItemCode2 As String

Dim iCt2 As Integer
Dim iContainerQty2 As Integer
Dim strSQL2 As String

iCt2 = 1
strCustPO2 = Me!CustPO
strCustOrderID2 = Me!CustOrderID
iContainerQty2 = Me!ContainerQty2 + 1
strItemCode2 = Me!ItemCode2


Do While iCt2 < iContainerQty2
strCustPOTrack2 = strCustPO2 & "-" & iCt2

Dim CurDB As Database
Set CurDB = CurrentDb()

DoCmd.RunSQL ("INSERT INTO tblDetail(CustOrderID, CustPOTrack, ItemCode_Detail) VALUES ('" & strCustOrderID2 & "','" & strCustPOTrack2 & "','" & strItemCode2 & "')")
DoCmd.SetWarnings (WarningsOff)

'Debug.Print strSQL

Set CurDB = Nothing

iCt2 = iCt2 + 1
Loop

MsgBox ("Rcord parfing successful!")
'DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70


End Sub
###########################################################

What I need to achieve is that if button cmdParse2 is clicked, the second set of data is added to the last record of the subform, CustPOTrack starting from the next number of the last record. Thanks alot for your help.

 

[calpolymis1]

Hi, I found the code in my last post works except CustPoTrack. It starts from 1, not from the next of the last number-as I can see in the code. What kind of modification would be needed to make it start from the next of the last number, for example if the last number is 12345-30, then start from 12345-31?

 

[formerTexan]

There are a few ways though I am not sure what will be most appropriate for your situation.

1. You can use a module level variable to hold the last CustPOTrack in the set. Then refer to kick off the next set. But you will need to control the order in which the user creates the set.

2. Use DLookUp to find the last CustPOTrack entered.

3. Or use a SQL statement (with Top 1 or MAX) to find the last entered CustPOTrack.

I see that you have database and recordset references in your code, but don't actually use these objects. A bit of cleanup may be in order.

Cheers,
Bill

 

[calpolymis1]

Thanks, Bill. Your suggestion solved my problem. Thanks again.