Need help autopopulating rows in subform based on form criteria

[CatrionnaRain]

Hi All -

I'm using 2002 and consider myself an intermediate Access user but newbie programmer :) I've been tasked with the db project from hell and have been combing through posts for the last week looking for relevant snippets. The problem is that my problem is multi-tiered and although I've seen hints I haven't yet come across anything like this problem. So - without further ado here's the situation.

I've got a db that has 3 tables. Customer, SWO, and CableRecords. Essentially the purpose of the database is to log serial numbers for cables to ensure unauthorized duplication doesn't occur. Unfortunately for me there are some cases where it's allowed so I can't set a hard and fast rule to not allow them, just have to make the user aware that it exists and let them make the determination based on the data.

The part that I'm struggling with is this.... I need a data entry form of some type that would allow the user to select the customer, assembly (both from the customer table) and SWO (from the SWO table) and enter "# of cables to be built". I then need it to prompt for additional information if it's not an existing assembly (perhaps popup form?) and then autopopulate the cable records table incrementally starting at the next available number based on the number entered in "# of cables to be built".

At this point I feel like a zombie - I've been reading anything and everything I can and have seen different references to either using append or insert. I really need some help on this one as I'm definitely in over my head and there's nobody else able to help me here. I have attached a stripped down version of the db. I'm happy to provide additional info on fields, etc but wanted to see if there's anyone out there that's done something similar. I would normally say that I'm sure there is but after the last week I'm just lost :)

Thanks everyone for your help!
Angie

[NeoPa]

I guess you didn't get very far with the class idea yet Angie.

I may be able to help here (I hope so).
Can you explain in real-world terms what your database is for. I was unable to guess what a cable is or an SWO (some sort of Work Order perhaps).
The attached database saves asking a whole heap of questions, but I'd like a better handle on what you're attempting here.

[CatrionnaRain]

Lol - a cable is literally that - my company produces electrical cables that are used in cool stuff like rockets and tanks :)

Because of that, the Assembly alone isn't unique - it's the Customer/Assembly combination that makes it unique hence the CableID field in tblCustomer is used as the key.
Essentially any action in the database will start with a form that allows the user to select the Customer from a combo box and enter the Assembly into a text box. When they click the command button it searchs via loading a form (frmCableView) that populates based off a pquery and shows the related SWOs (Sales Work Orders) in a list box and has a subform that displays each individual cable ordered by S/N (serial number).

This part all works beautifully and life is good.

The problem is that I need a way for the user to be able to add X number of records automatically based off the Customer & Assembly (from the tblCustomer) and SWO (from tblSWO).

I would like the user to be able to click the SWO from the listbox on the frmCableView and click an 'Add Record' button to pop open a form where she could enter the number of records she'd like to create [Qty Issued] and enter the information that would repeat in each field of tblCableRecords ([Date], [KIT], [RMA], [DWG Rev], [PWO Rev], [AC Rev], [Issued To], [Issued By], [MOrder], [Comments], [Adjustments]). C/N (cable number) is a number that increases incrementally for each SWO and S/N is incremental for each Customer/Assembly. So, when said user enters the pertinent info and hits the 'Create Records' button then it would autopopulate all 3 tables based off the information contained on that form. Additionally it needs to calculate the C/N and S/N and populate them.

I have done lots and lots of reading and I know I need to use the DMAX function for the incremental records. I think I need to use a Do/While loop for the count and I'm fairly certain this has to do with the AddNew using a recordset (or maybe insert record?). But how those things go together is beyond me at this point my brain feels like a pretzel.

To assist I'm going to attempt to attach a new copy of the db as I've made some changes and the forms I referenced above aren't the same as my intial entry.

[NeoPa]

OK Angie, I'll try to loook at this when I get home from work this evening.
I'm based in England. What time-zone are you in so that I know when I SHOULDn't expect any responses?

[CatrionnaRain]

i'm in CST US (Arkansas). My workday ends at 2:30 however my account here is keyed to email my personal email so I should be able to reply up until 9pm or so.

Thank you so much for any help you can give me - I'm learning fast but this is still way above my current level.

[NeoPa]

I'm sorry I didn't get much time to look at this this evening :(
I'm out tomorrow and Friday evenings, but if you can wait for me I will make some time at the weekend if I don't manage anything before then. It's difficult working on downloaded databases at work, but I'll download the latest version here (@home) now just in case I can make any progress in the mean-time.

[NeoPa]

I've had a very quick look and noticed there are a whole bunch of new items. Mostly queries, but a CustomerLU table too. What is that?
I also noticed that the TblCustomer table didn't seem to contain simple customer data (The same Customer is repeated many times). I think the concepts described in Normalisation and Table structures would be well worth looking at. Can you take a look and let me know if you feel that your database fits into this structure please. At first glance it didn't seem to but I can't check it thoroughly to see if I've misunderstood something somewhere. If it doesn't then my first recommendation before any further progress is made would be to make it so. I know from experience how much more difficult it can be when trying to force a non-normalised database to work for you. It also makes the database a bit harder to "read" from an outsider's point of view.
Let me know what you think about this. If changes are required then I can probably help with ideas of how to go about it when I understand better exactly what you're looking for and how your structure should be working.

[CatrionnaRain]

The CustomerLU table is actually so I can test how the forms work once we get multiple customers in the DB. The data loaded currently is all for the same customer. Part of the problem and the reason why you see repeating data in the Customer table is that the Assembly is simply one type of cable. So many customers can order the same type of cable but the serialization must follow each customer/assembly combination. The other option is to break Assembly out from Customer and have a PK code for Customer that's referenced in Assembly I just didn't do that because it is the combination of the two that make the key. I'm open to learning though - so if I'm looking at it wrong please tell me :D So then your CableID (customer/assembly) can have multiple Sales Work Orders (SWO) which has it's own set of data that is unique and can repeat across cables so that's why it's in its own table. And in that case it's the CableID and SWO that make the ID the PK in that table. When you get to cablerecords the C/Ns follow each SWO set and the S/Ns follow each CableID (customer/assembly).

I hope that was clarifying and not just redundant. It's been hard to get my head around this one because for one thing I'm working with existing data going back 10+ yrs that has been data entered from log books. There's not much hope of filling in missing info since there aren't people from then that remember these kind of details. Also, I had extreme difficulty finding unique identifiers for the data because there are so many variables in how they're handled (ie, initial thought was assembly until I found out that it repeats across customers).

I've already learned a ton in this project and I'm truly enjoying that part but am just a little frustrated at knowing what I want and yet not quite sure how to get there. I would truly appreciate any help you have to offer.

Thank you :)

PS - Assembly is the cable - cable in this case just references a completed cable vs. assembly being a plan. Sort of like Assembly is the recipe and cable is the baked cookie. Hope that helps :)

[CatrionnaRain]

Ok - so 8 more hours of beating my head against the wall and internet, and books.... has had me make a few more changes. I've now added the cableID field to tblCableRecords since one of the requirements is that S/N is keyed off of the CableID which wasn't previously available. Below is my sad attempt at programming what I'm trying to accomplish. I'm hoping that it will at least illustrate what I'm attempting and not further muddy the waters. At the very least it will explain why my brain feels like a pretzel right now :)

So.... some things I can't get my head around - S/N is incremental based on cableID and C/N is incremental for each SWO. The sql below is supposed to pull out only the records for the selected SWO because the new cables being added will be using that particular one but I still need the S/N to populate off the DMax of the S/N field by CableID without the SWO filter.

Thank you all for your time and consideration of this brain torturing puzzle!

Private Sub AddCable_Click()
On Error GoTo Err_AddCable_Click
 
Function AddNewCables()
 
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim QtyInput As Integer
Dim i As Integer
Dim intNum() As Integer
Dim NewSN As Integer
Dim NewCN As Integer
 
strSQL = "SELECT TblCableRecords.CableID, TblCableRecords.SWO_ID, TblCableRecords.Date," _
    & " TblCableRecords.[C/N], TblCableRecords.KIT, TblCableRecords.[S/N]," _
    & " TblCableRecords.RMA, TblCableRecords.[R&D], TblCableRecords.[DWG Rev]," _
    & " TblCableRecords.[PWO Rev], TblCableRecords.[AC Rev], TblCableRecords.[Issued To]," _
    & " TblCableRecords.[Issued By], TblCableRecords.[Qty Issued], TblCableRecords.MOrder," _
    & " TblCableRecords.Comments, TblCableRecords.Adjustments, TblCableRecords.PK1" _
    & " FROM TblCableRecords" _
    & " WHERE TblCableRecords.CableID = [Forms]![frmAddRecord]![CableID] " _
    & " And TblCableRecords.SWO_ID = [Forms]![frmAddRecord]![SWO_ID]"
 
QtyInput = [Forms]![frmAddRecord]![QtyInput].Value
 
ReDim intNum(1 To QtyInput)
 
Set db = CurrentDb() ' Open pointer to current database
  Set rs = db.OpenRecordset(strSQL) ' Create recordset based on SQL
 
  For i = 1 To QtyInput
    Do While Not rs.EOF
 
    NewSN = DMax("S/N", "tblCableRecords") + i
 
        With TblCableRecords
            .AddNew
            ![Date] = [Forms]![frmAddRecord]![Date].Value
            ![DWG Rev] = [Forms]![frmAddRecord]![DWGRev].Value
            ![PWO Rev] = [Forms]![frmAddRecord]![PWORev].Value
            ![AC Rev] = [Forms]![frmAddRecord]![ACRev].Value
            ![Issued To] = [Forms]![frmAddRecord]![IssuedTo].Value
            ![Issued By] = [Forms]![frmAddRecord]![IssuedBy].Value
            ![Qty Issued] = [Forms]![frmAddRecord]![QtyInput].Value
            ![MOrder] = [Forms]![frmAddRecord]![MOrder].Value
            ![Comments] = [Forms]![frmAddRecord]![Comments].Value
            ![KIT] = [Forms]![frmAddRecord]![KIT].Value
            ![RMA] = [Forms]![frmAddRecord]![RMA].Value
            ![S/N] = [NewSN]
            ![RMA] = [Forms]![frmAddRecord]![RMA].Value
            .Update
        End With
    Loop
    Next i
 
    For i = 1 To QtyInput
        Debug.Print "Serial Numbers " & NewSN & " - "; intNum(NewSN)
    Next i
 
    rs.Close
 
Set rs = Nothing
Set db = Nothing
 
End Function
 
Exit_AddCable_Click:
    Exit Function
 
Err_AddCable_Click:
    MsgBox Err.Description
    Resume Exit_AddCable_Click
 
End Function

[CatrionnaRain]

Sorry - trying to put the tags in now. I've made some more changes since the last set that I _think_ are closer to correct.

Private Sub AddCable_Click()
On Error GoTo Err_AddCable_Click
 
Function AddNewCables()
 
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim QtyInput As Integer
Dim i As Integer
Dim intNum() As Integer
Dim NewSN As Integer
Dim NewCN As Integer
Dim FstSN As Integer
Dim LstSN As Integer
 
strSQL = "SELECT TblCableRecords.CableID, TblCableRecords.SWO_ID, TblCableRecords.Date," _
    & " TblCableRecords.[C/N], TblCableRecords.KIT, TblCableRecords.[S/N]," _
    & " TblCableRecords.RMA, TblCableRecords.[R&D], TblCableRecords.[DWG Rev]," _
    & " TblCableRecords.[PWO Rev], TblCableRecords.[AC Rev], TblCableRecords.[Issued To]," _
    & " TblCableRecords.[Issued By], TblCableRecords.[Qty Issued], TblCableRecords.MOrder," _
    & " TblCableRecords.Comments, TblCableRecords.Adjustments, TblCableRecords.PK1" _
    & " FROM TblCableRecords" _
    & " WHERE TblCableRecords.CableID = [Forms]![frmAddRecord]![CableID]"
 
QtyInput = [Forms]![frmAddRecord]![QtyInput].Value
 
FstSN = DMax("S/N", "tblCableRecords") + 1
 
LstSN = DMax("S/N", "tblCableRecords") + QtyInput
 
ReDim intNum(1 To QtyInput)
 
Set db = CurrentDb() ' Open pointer to current database
  Set rs = db.OpenRecordset(strSQL) ' Create recordset based on SQL
 
  For i = 1 To QtyInput
    Do While Not rs.EOF
 
    NewSN = DMax("S/N", "tblCableRecords") + i
 
    CableSN = DMax("C/N", "tblCableRecords", "SWO_ID =" & Me.SWO_ID) + 1
 
        With rs
            .AddNew
            ![Date] = [Forms]![frmAddRecord]![Date].Value
            ![DWG Rev] = [Forms]![frmAddRecord]![DWGRev].Value
            ![PWO Rev] = [Forms]![frmAddRecord]![PWORev].Value
            ![AC Rev] = [Forms]![frmAddRecord]![ACRev].Value
            ![Issued To] = [Forms]![frmAddRecord]![IssuedTo].Value
            ![Issued By] = [Forms]![frmAddRecord]![IssuedBy].Value
            ![Qty Issued] = [Forms]![frmAddRecord]![QtyInput].Value
            ![MOrder] = [Forms]![frmAddRecord]![MOrder].Value
            ![Comments] = [Forms]![frmAddRecord]![Comments].Value
            ![KIT] = [Forms]![frmAddRecord]![KIT].Value
            ![RMA] = [Forms]![frmAddRecord]![RMA].Value
            ![S/N] = [NewSN]
            ![RMA] = [Forms]![frmAddRecord]![RMA].Value
            ![SWO_ID] = [Forms]![frmAddRecord]![cboSWO].Value
            ![CableID] = [Forms]![frmAddRecord]![CableID].Value
            ![S/N] = NewSN
            .Update
        End With
    Loop
    Next i
 
    Debug.Print "Serial Numbers " & FstSN & " - " & LstSN & ""
 
    rs.Close
 
Set rs = Nothing
Set db = Nothing
 
End Function
 
Exit_AddCable_Click:
    Exit Function
 
Err_AddCable_Click:
    MsgBox Err.Description
    Resume Exit_AddCable_Click
 
End Function