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
 

[NeoPa]

Angie,

Please feel free to post newer copies of the database with your posts when appropriate. I can ensure all but the last is deleted when you are happy that the problems are all resolved (It's a bonus of being and Admin ;)). Your current practice of including just enough to make the point without reams of data is a good one and should ensure that they don't take up too much space.
As in the PM - I hope to be able to give this some proper attention before tomorrow is through.

[NeoPa]

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.

Not a problem.
A quick glance at your code (while at work so can't spend much time on it) tells me you are possibly unaware of the Compile facility (Debug / Compile {your project name} from the code window). Give this a try and let me know if this adjusts (or causes you to adjust) your code. This is also a very good general tip whenever generating code by the way ;)

[CatrionnaRain]

Bless you - am definitely making some progress - just not quite there - lol.... but you are correct - I had forgotten that was there. The teeny bit of exp I have was a class about 5 yrs ago or so that was like Advanced Access with a little VBA thrown in to confuse you :) I think I'm fairly logical most of the time and therefore 'can be taught' but the syntax is hard to pick up - especially from multiple sources and learning when and how and where to use VBA or SQL or ADO references has been difficult :)

[NeoPa]

I haven't received a private message (PM) response from you so I'm not sure if you've received my PM. Can you confirm in here that you know what I'm talking about.

PS. I noticed you weren't stupid and knew how to approach a problem from your posts. You can learn a lot about that sort of thing just by reading how someone expresses themselves; how much preparation they've put in before asking the question; etc.
I anticipate making good progress if we can arrange better communication.

[NeoPa]

While I'm waiting for a response I'll look at your latest posted code.
I'm not sure this is required in this form yet, but I'll just go through it making some changes anyway (with comments).

Private Sub AddCable_Click()
On Error GoTo Err_AddCable_Click
 
'Function AddNewCables() Unrequired and misleading
'functions return values and don't occurr within other procedures
 
'  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
 
'Following SQL doctored to remove explicit references to the table.
'This is not required with a single table query
'and only serves to make it harder to read.
'Also, though SQL is freeform, it's much easier to work with if it's layed
'out in a structured fashion
  strSQL = "SELECT CableID, SWO_ID, [Date], [C/N], [KIT], [S/N], " & _
                  "RMA, [R&D], [DWG Rev], [PWO Rev], [AC Rev], " & _
                  "[Issued To], [Issued By], [Qty Issued], MOrder, " & _
                  "Comments, Adjustments, PK1 " & _
           "FROM TblCableRecords " & _
           "WHERE CableID = " & Me.CableID
'.Value is the default property.  It only detracts from readability.
  QtyInput = ![QtyInput]
 
'Each D???() function (Domain Aggregate) processes through a whole dataset.
'Avoid unnecessary extra calls.
  FstSN = DMax("S/N", "tblCableRecords") + 1
  LstSN = FstSN + QtyInput - 1
'intNum not used anywhere
'    ReDim intNum(1 To QtyInput)
'Merged two lines.  db only used once so doesn't need to be stored.
  Set rs = CurrentDb.OpenRecordset(strSQL) ' Create recordset based on SQL
'Following line moved out of the loop for performance reasons.
  NewCN = DMax("C/N", "tblCableRecords", "SWO_ID =" & Me.SWO_ID)
'Use SNs in the loop rather than a 1 based counter
  For NewSN = FstSN To LstSN
'Line added to maintain correct value.
    NewCN = NewCN + 1
'Not required - the rs.AddNew will always position the record correctly
'      Do While Not rs.EOF
'Unrequired
'      NewSN = DMax("S/N", "tblCableRecords") + i
'Wrong variable name used - always select "Require Variable Declaration"
'Also moved out of the loop.
      'CableSN = DMax("C/N", "tblCableRecords", "SWO_ID =" & Me.SWO_ID) + 1
    With rs
      .AddNew
'.Value is the default property.  It only detracts from readability.
      ![Date] = Me.[Date]
      ![DWG Rev] = Me.[DWGRev]
      ![PWO Rev] = Me.[PWORev]
      ![AC Rev] = Me.[ACRev]
      ![Issued To] = Me.[IssuedTo]
      ![Issued By] = Me.[IssuedBy]
      ![Qty Issued] = Me.[QtyInput]
      ![MOrder] = Me.[MOrder]
      ![Comments] = Me.[Comments]
      ![KIT] = Me.[KIT]
      ![RMA] = Me.[RMA]
      ![S/N] = [NewSN]
      ![RMA] = Me.[RMA]
      ![SWO_ID] = Me.[cboSWO]
      ![CableID] = Me.[CableID]
'[S/N] was repeated.  I assumed [C/N] required here.
      ![C/N] = NewCN
      .Update
    End With
'      Loop
  Next NewSN
 
  Debug.Print "Serial Numbers " & FstSN & " - " & LstSN
 
  rs.Close
 
  Set rs = Nothing
'  Set db = Nothing
 
'Not required
'End Function
 
Exit_AddCable_Click:
  Exit Function
 
Err_AddCable_Click:
  MsgBox Err.Description
  Resume Exit_AddCable_Click
 
End Sub

NB. With the blank lines, commented lines and comments removed this comes out at only 52 lines of code.
You would keep some of the blank lines but most of the comments and commented lines would go when used live.

[sierra7]

Hi
I don't want to detract from NeoPa's excellent guidance but can chuck-in a few observations and at the end have a question of my own. I don'y fully understand the app and realise there may be bits missing, either removed or not yet built. However, I understand that you want to build assemblies (cable harnesses), create them by a batch process but then monitor them individually as they are built and tested.

Looking at your code, firstly, I'm surprised that you are not getting errors by not declaring a recordset as DAO.Recordset, because you are using the .AddNew & .Update methods (which is DAO not ADO). I see you have references to DAO 3.6 and Access 10 and my experience is that Access 10 will create ADO.recordsets unless told otherwise, then error when it come across an .Update

I agree with your strategy of looping to create the multiple records (DAO is ok but I find SQL faster) I'm surprised you are duplicating so many fields (15 or so) which suggests that the data is not normalised. Browsing your data I note that certain fields must have been updated with different values since they were initially created (e.g. Date, Issued To, Issued By , and obviously Comment) but that still leaves 10 or so fields that may all have the same data for a particular SWO.

Your form FrmDataEntry would work better (work properly!) if the Master/Child relationship between the main form and the [tblSWO subform] was changed from [Assembly] to [CableID]. You could then use the navigation buttons on the main form to browse the data. A better solution may be to change the two controls for[Customer] and [Assembly] to be unbound and then populate them (synchronize them) during the Form_Current event. This way you could use them to input selection criteria, either by creating a filter or by using the 'Bookmark' and 'Recordset Clone' method that you have used elsewhere.

I'm puzzled by the lack of VBA code. The procedures/functions in your two modules will not work because they refer to "Me." You will have to pass a formname to them via a parameter.

Now to my question! (Sorry to go off track NeoPa) How the hell have you made certain rows in a multi-record subform appear RED and where is the criteria? (e.g. form frmCableView, subform TblCableRecords, Assembly = '22-11325-2' etc)
I can do this but overlay duplicate controls which are evaluated by an 'IIF' to determine whether they display data (in the chosen colour) or just a blank space to allow an underlying control to show through in a different colour.

I look forward to seeing the resolution to you problem but suspect it may have gone into a PM session.

But good luck anyway!

[NeoPa]

Sierra,

Some intelligent comments :)
I haven't yet received any PM response from the OP though. If and when I do, I will try to ensure that all relevant communication is published in the thread. I was hoping to establish an IM link to get some immediate responses but that hasn't come to pass yet. A project of this size could take a very long time without some more direct communication I think. Anyway, we'll see how it goes.

[CatrionnaRain]

Sorry - I've been sick - I'm looking at the responses now and will reply shortly. I appreciate everyone's help.

Thank you :)

[NeoPa]

Me too ;(

I'm trying to catch up with everything today. I will switch over shortly to the PMs, which I now know contains one from you.

[CatrionnaRain]

Submitting a new copy of the DB - the biggest change is the addition of the cableID to the cablerecords table. I did this because the S/N is incremental based on the CableID and I was having problems with it working indirectly through the link with the SWO table. I honestly think it might not have been necessary but I'm trying anything to help at this point.

Ideally the user starts at [frmAssySearch] and if the input exists it opens [frmCableView]. If the person wants to add additional records they would push the 'Add' button at the bottom of the page which opens [frmAddRecord]. I have the form based off a query [qryAssySearchTest] that uses the input from [frmAssySearch] as the parameters. The user inputs how many cables and inputs the information required for them and when the user pushes the 'Add Cables' button at the bottom of the form it triggers the event which is supposed to autopopulate [tblCableRecords] with the # of records in [QtyInput] and is also supposed to autoincrement the C/N (Cable Number) and S/N (Serial Number) based on a DMAX count. At the end of the procedure I'd like it to kick a msgbox to the user telling them which S/N's were added to the db.

Sounds like fun right :) Sorry if any of this repeats - I just wanted to try to clearly recap :) Thanks for your help!

[sierra7]

Hi Angie

I have your code working now but cannot figure how to attach the zip file. I will list the code here but have a number of concerns that i will write in a separate post.

The code now does what you described in your last post but I am not sure that the C/N and S/N are picking up correctly because the lookups to find the last values are not qualified by Company and Assembly, as specified in an earlier post.

For clarity (selfishly?) I have removed previous comment but left in some of my own MsgBox rubbish which I use to track where the programme is up to. (One day I will learn to use the Debug window properly!)

Dim rs As DAO.Recordset 'Not having DAO was the cause of a problem
Dim strSQL As String
Dim iQtyInput As Integer    'S7: re-specified to not conflict with field name
                            'actually i would not use a variable in this case
 
Dim NewSN As Integer
Dim NewCN As Integer
Dim FstSN As Integer
Dim LstSN As Integer
 
 
'S7: - could not get your recordset to load. Error msg 'mis-match' or something
'      Initially thought it was in NexCN lookup syntax
'      After that was sorted it had to be the recordset statement in preceeding line
'      Your string looks OK but I hate blanks and ampersands
'      so selected * to simplify
'      Finally realised not specified as DAO. (I should have re-read my first post!)
strSQL = "SELECT * FROM TblCableRecords " & _
        "WHERE TblCableRecords.CableID = " & Me.CableID & ";"
 
 
iQtyInput = Me![QtyInput]   'S7 Use respecified variable
'MsgBox "OK1", vbInformation, "S7 "
 
FstSN = DMax("[S/N]", "tblCableRecords") + 1
'S7 added square brackets around field name [S/N]
LstSN = FstSN + iQtyInput - 1
 
'MsgBox "OK2" & " FstSN=" & FstSN & " and LstSN=" & LstSN, vbInformation, "S7 "
 
Set rs = CurrentDb.OpenRecordset(strSQL) 
'S7; test whether this exists and has loaded OK
'MsgBox "Number of Records =" & rs.RecordCount
 
NewCN = DMax("[C/N]", "tblCableRecords", "[SWO_ID] =" & Val(Me.SWO_ID))
'S7 added square brackets around field name [C/N]
'MsgBox "OK3" & " NewCN=" & NewCN, vbInformation, "S7 "
 
For NewSN = FstSN To LstSN
    'Line added to maintain correct value.
    NewCN = NewCN + 1
 
    With rs
    .AddNew
        ' S7; some programmers prefer Me. to be Me! 
        ![Date] = Me.[Date]
        ![DWG Rev] = Me.[DWGRev]
        ![PWO Rev] = Me.[PWORev]
        ![AC Rev] = Me.[ACRev]
        ![Issued To] = Me.[IssuedTo]
        ![Issued By] = Me.[IssuedBy]
        ![Qty Issued] = Me.[QtyInput]
        ![MOrder] = Me.[MOrder]
        ![Comments] = Me.[Comments]
        ![KIT] = Me.[KIT]
        ![RMA] = Me.[RMA]
        ![S/N] = [NewSN]
        ![RMA] = Me.[RMA]
        ![SWO_ID] = Me.[cboSWO]
        ![CableID] = Me.[CableID]
        ![C/N] = Right((NewCN + 1000), 3)       'S7 padded with zeros
    .Update
 
    End With
    '      Loop
Next NewSN
'Debug.Print "Serial Numbers " & FstSN & " - " & LstSN
 
'S7 a no-frills message box
MsgBox "Cables with Serial Numbers S/N from " & FstSN & _
        " to " & LstSN & "  have been added ", vbInformation, "Addition Complete"
 
rs.Close
Set rs = Nothing
 
'S7; requery CableView to ensure new data displayed
Forms!frmCableView.Requery
 
'S7; close this form
DoCmd.Close acForm, "frmAddRecord"

The looping and inserting of records works OK here but, as above, I am not sure that the right serial numbers and cable numbers are being used.

Also, the form will only let you add for an existing SWO (because the new SWO is populated from a combo-box of existing SWO). But I will address this in a seperate post.

[NeoPa]

To attach a (Zip) file to any post :

1. Post message.
2. Edit post (before expiry time of 1 hour has passed).
3. Click on "Manage Attachments" button.
4. Add remove attachments as required.

Have fun.

[NeoPa]

Also, check out (Debugging in VBA) for some tips about debugging and how to use all the various aspects available.

[sierra7]

Just looking at that code again, you don't need the WHERE clause in strSQL because you are not counting these records just Appending new ones.

I have been giving some thought to rationalising your data structure but it is difficult because there are too many unknowns about your Business Model.

For instance, you don't really need CableID on tblCableRecords if a SWO can only sporn one cable type.

What do you do if a cable is damaged or rejected during manufacture? Do you raise a new Works Order (SWO) or does the replacement cable just assume the PK1 id of the defunct cable?

I still feel you are duplicating too much data with the insert loop but this depends on your final data structure.

I hope this has been some help

[CatrionnaRain]

Just looking at that code again, you don't need the WHERE clause in strSQL because you are not counting these records just Appending new ones.

I have been giving some thought to rationalising your data structure but it is difficult because there are too many unknowns about your Business Model.

For instance, you don't really need CableID on tblCableRecords if a SWO can only sporn one cable type.

What do you do if a cable is damaged or rejected during manufacture? Do you raise a new Works Order (SWO) or does the replacement cable just assume the PK1 id of the defunct cable?

I still feel you are duplicating too much data with the insert loop but this depends on your final data structure.

I hope this has been some help

The WHERE clause was because S/N is populated based on the CableID. That is also why CableID got added to [tblCableRecords]. I didn't have it in there initially because I didn't think it was needed as my relationships should have been enough but I was having so many problems I thought perhaps having it directly in the table would be helpful. You can say I'm grasping at straws :)

As far as if a cable is returned, the RMA field is there so that the user can add an R after the S/N to indicate a return :) In most cases they do reuse the S/N for the cable.

I'll take a look at the code you posted and pray :) After not being able to work on this for a week all of a sudden it's a priority again as they had another duplication. I'm hoping to get it working quickly.

I understand the concerns about rationalization - I truly do - but at the end of the day we need each cable to be it's own line item so that we may make changes as needed to the individual cable. If there's a better mousetrap I'm completely open but priority one is getting the functionality in place even if that isn't the ideal way. Unfortunately dealing with data that's 10+ yrs old in some cases and trying to move it forward digitally has presented a lot of unique situations here. I'd love to be optimistic enough to call them opportunities but frustration and time no longer allow me the benefit of that rosy glassed view of things :)

I sincerely appreciate everyone's help :)
Angie

[NeoPa]

I had another look at things tonight Angie, but I'm afraid that I still have far too many questions about what you are trying to achieve to make any progress.
I'm happy to have another attempt if we can get an MSN link going so that I can get some detailed answers (I work at a detailed level. Simply guessing what you're after at that level is a complete waste of time for me. My guesses always lead me to the way I do things - I find that rarely matches how others think and see things so my assumptions would lead me off track.) I'm not trying to criticise as I appreciate you've made every attempt to explain things to the best of your ability. I just can't make any progress at this time.
Luckily enough Sierra7 seems to be making some good headway with you and I will still be monitoring how things develop. Any message in here will get me in a fairly short time assuming no repetition of my recent illness or any other unfortunate mishaps. Clearly if there are any specific questions I can help with I will do what I can.

[sierra7]

NeoPa
Thanks for the tip on adding a zip file. I'll try it now.
I have already read your article on Debuging (I need to check it links to the same one) but must be missing something on how I can apply it. I will set it as a New Year resolution to master the debug window!

Angie
Hopefully, you should find the zip attached. I had to remove a Reference to Outlook because seemingly that particular file does not exist on my computer and for some reason that stopped the Right() function from working (to pad C/N with leading zeros)

The code I posted should work but after pasting it in this message window I realised there were so many comments that it was difficult to see the code, so I deleted all comments but my own, which runs the risk of take out something critical.

You don't need the WHERE clause when stuffing things in the table, infact I would just use the table name rather than a query. (NeoPa may wish to comment here!) Also, I suspect that some of the data values you are adding may be 'blank' at this stage. But this is where I agree with NeoPa because I am spending time double-guessing about your business.

I think I already said in my last post, that the criteria for finding Dmax() were not what I expected from your first post. I will look at this again if I get time today.

Meanwhile I hope you receive the attached and it runs for you!

[NeoPa]

NeoPa
Thanks for the tip on adding a zip file. I'll try it now.
I have already read your article on Debugging (I need to check it links to the same one) but must be missing something on how I can apply it. I will set it as a New Year resolution to master the debug window!
...

If you have a question on debugging, you can add it as a comment to the article. That way I will be notified of it. I will then separate it out into it's own question thread and give what help I can.
Debugging can be so useful and a number of good facilities are provided. I suspect many people never benefit from what's available simply because they aren't aware that the facilities exist.

[CatrionnaRain]

NeoPa
Thanks for the tip on adding a zip file. I'll try it now.
I have already read your article on Debuging (I need to check it links to the same one) but must be missing something on how I can apply it. I will set it as a New Year resolution to master the debug window!

Angie
Hopefully, you should find the zip attached. I had to remove a Reference to Outlook because seemingly that particular file does not exist on my computer and for some reason that stopped the Right() function from working (to pad C/N with leading zeros)

The code I posted should work but after pasting it in this message window I realised there were so many comments that it was difficult to see the code, so I deleted all comments but my own, which runs the risk of take out something critical.

You don't need the WHERE clause when stuffing things in the table, infact I would just use the table name rather than a query. (NeoPa may wish to comment here!) Also, I suspect that some of the data values you are adding may be 'blank' at this stage. But this is where I agree with NeoPa because I am spending time double-guessing about your business.

I think I already said in my last post, that the criteria for finding Dmax() were not what I expected from your first post. I will look at this again if I get time today.

Meanwhile I hope you receive the attached and it runs for you!

Wow - this is so freaking close - i am so excited! The cable number populates correctly based off the SWO_ID but the Serial number is populating off the whole table and counting the next one up - it needs to increment based on the CableID. I'm going to play with it a bit this morning and see if I can get it there. I am so thankful for your help :) I'll try to post this afternoon on where I'm at with it :)