Auto-increment Control on Form and Increase Quantity

[dlab85]

I believe so. I will attempt to make the modifications tomorrow, using your tips, code changes, etc. I will resolve the thread tomorrow if I can get that basic premise solved, but maybe post back here if the coding is just not cooperating. Thanks for all the help June7!!!!!

[dlab85]

DoCmd.RunSQL "Insert into tblChemicalID(chemicalID, Received_Date, [Catalog Number]) Values ('"&GetNewChemicalID()&"', #"&Date&"# ,'" & Me.Catalog_Number&"')"

I am getting a compile error, expected end statement, which hilights the red text above. I have tried many alterations with no success. Ideas?

[June7]

Spaces before and after &s might help.

DoCmd.RunSQL "Insert into tblChemicalID(chemicalID, Received_Date, [Catalog Number]) Values ('" & GetNewChemicalID() & "', #" & Date & "#, '" & Me.Catalog_Number & "')"

[dlab85]

June7,
While we are at it, the function NewChemicalID is not adding 1 to the last used ChemicalID, it just enters -0001. I can't figure out why the +1 from the coding is not working. I have an AfterUpdate event on the Lot Number control that is supposed to call the Function and insert the chemicalID into the field, which it does; but maybe this is also why the code is not going on to the next chemical id.

Option Compare DatabaseOption Explicit
Public Function NewChemicalID() As String
Dim strChemicalID As String
strChemicalID = Nz(DMax("ChemicalID", "tblChemicalID"), 0)
If strChemicalID <> "" Then
    If Left(strChemicalID, 4) = CStr(Year(Date)) Then
        NewChemicalID = Left(strChemicalID, 5) & Format(Right(strChemicalID, 4) + 1, "0000")
    Else
        NewChemicalID = Year(Date) & "-0001"
    End If
Else   'this accommodates very first generated number of blank database
    NewChemicalID = Year(Date) & "-0001"
End If
End Function

Here is the AfterUpdate Event on control Lot Number

Private Sub Lot_Number_AfterUpdate()Call NewChemicalID
Me.ChemicalID = NewChemicalID
End Sub

[June7]

I am testing from your last posted project. The ChemicalID is incrementing but subform is not retaining data because the subform DataEntry property is set Yes. This means after saving the record the form refreshes to a new record because DataEntry Yes means form cannot display existing records.

Decision time:

Set DataEntry No and code to move to new record on subform

or

Unbound subform

[dlab85]

I solved my first issue. Here is the fixed coding that properly increments the ChemicalID generation. It does create gaps if you delete a record that had been assigned a chemicalID, but I am okay with that.

Option Compare DatabaseOption Explicit
Public Function NewChemicalID() As String
Dim strChemicalID As String
strChemicalID = Nz(DMax("ChemicalID", "tblChemicalID"), 0)
If strChemicalID <> "" Then
   NewChemicalID = Left(strChemicalID, 5) & Format(Right(strChemicalID, 4) + 1, "0000")
    Else
        NewChemicalID = Year(Date) & "-0001"
    End If
End Function

There had been an extra If statement that appeared to just be a duplication of NewChemicalID=Year(Date) & "-0001" that June7's code had a comment that it was needed to generate new increment of -0001 for a new year. Where should that line have been June7? Because I think the coding as written will change the Year to 2014, but keep the numbering increasing from the last 2013 ChemicalID (i.e. 2014-0987 instead of 2014-0001)?

The second problem is the looping code is not doing anything that I can notice. It doesn't error out, but it does not create any number of records based on the value in txtQuantity. I am not sure how to tackle that code:

Private Sub cmdAddAnotherChemical_Click()Dim strChemicalID As String
'search for aborted ChemicalID and use it, else if none then NewChemicalID
strChemicalID = Nz(DLookup("ChemicalID", "tblChemicalID"), "")
DoCmd.SetWarnings False
strChemicalID = NewChemicalID()
If strChemicalID <> "" Then
 DoCmd.RunCommand acCmdSaveRecord
 For i = 1 To Me.txtQuantity - 1
    DoCmd.RunSQL "INSERT INTO tblChemicalID(chemicalID, [Received Date], [Catalog Number]) VALUES ('" & NewChemicalID() & "', #" & Date & "# ,'" & Me.Catalog_Number & "')"
    Next
End If
DoCmd.SetWarnings True
End Sub

Thoughts would be greatly appreciated. Thanks!

[dlab85]

Sorry for the post as you were editing your last post I think it would be less hassle to have DataEntry set to No and code to move on to new record, Agreed?

[June7]

The sample code I provided for generating sequential ID works for me. Each year starts the sequence over at 0001 with new year prefix. I don't allow gaps in sequence. All IDs are accounted for.

Either option requires code.

[dlab85]

How about we go with the coding to move on to new record. And I will set the DataEntry to No, and this should allow the looping to function properly correct?

PS Your coding was correct, it just appeared that some of the IF statements got shuffled up on my end. It is truly solved now I believe. I am going to attach my most recent database copy so you can verify that, and maybe it will assist in seeing where my coding is placed and my thought process for it.
Thanks again for the help!

[dlab85]

I believe all I have to do is modify this coding, by including the additional fields that should be transcribed over, i.e. Received Date, Expiration Date, Lot Number, etc. Is there an easy way adding those fields rather than typing all of them (there are 10 fields that would be "copied")? This is working without adjusting the DataEntry Property (still set to Yes for subform ChemicalID), any reason for that?

Private Sub cmdAddAnotherChemical_Click()Dim strChemicalID As String
'search for aborted ChemicalID and use it, else if none then NewChemicalID
strChemicalID = Nz(DLookup("ChemicalID", "tblChemicalID"), "")
DoCmd.SetWarnings False
strChemicalID = NewChemicalID()
If strChemicalID <> "" Then
 DoCmd.RunCommand acCmdSaveRecord
 For i = 1 To Me.txtQuantity - 1
    DoCmd.RunSQL "INSERT INTO tblChemicalID(chemicalID, [Catalog Number]) VALUES ('" & NewChemicalID() & "','" & Me.Catalog_Number & "')"
    Next
End If
DoCmd.SetWarnings True
End Sub

So I would include the controls for Received Date, Expiration Date, Lot Number, etc. in the Insert Into portion of the SQL. Does it matter if a control is bracketed or not? Then I would have to include those same fields in the Values portion of the SQL. Would they all have the same format as '" & Me.Control & "'?

[June7]

This works because the Catalog_Number is on the main form and this code is run from the main form and generating the ID has nothing to do with what is on the subform. When you add the other fields to the INSERT that require data from the subform it won't work with the subform DataEntry Yes.

Bracketing is required if names have spaces or are reserved words. Advise to avoid those features.

I prefer CurrentDb.Execute instead of DoCmd.RunSQL because don't need the SetWarnings code.

If you aren't allowing for aborted IDs then don't need that code.

Just took another look at the LotNumber AfterUpdate event code you posted earlier. Consider:

Private Sub Lot_Number_AfterUpdate()
If IsNull(Me.ChemicalID) Then Me.ChemicalID = NewChemicalID
End Sub

[dlab85]

I am struggling. So I made the subform's DataProperty set to No. And then tried to put the coding of

DoCmd.GoToRecord acDataform, "frmChemicalIDLogIn", acNewRec

in multiple places: Load Event for frmLogIn2, Click and GotFocus and BeforeUpdate events for the subform, with no luck of accomplishing what I needed . On the Load Event it would error when I opened frmLogIn2 because the subform technically wasn't open yet. So I am not sure where I should put coding to make the subform go to a new record (otherwise, when I select a catalog number, it displays the record that had been entered, and won't add a new record)? I was thinking the coding needs to be run when the subform is activated, but I can't get it to work.
Also, I am still struggling with get the Insert SQL to add the subform fields to the tblChemicalID (see post 25 for my coding). Even when I had the DataEntry setting of the subform set to No, the lot number (the only additional field I was trying to get to copy over) was not recognized in the Loop. I know it's because that control is not on the form frmLogIn2, where the coding is located. Do I have to designate the control's location on the subform, and if so, what is the wording for that (i tried Me.Form!frmChemicalIDLogIn!Lot_Number, and Me.Form!Form_frmChemicalIDLogIn!Lot_Number and they did not work). I feel like I'm trying to piece meal this together the wrong way. Should I move the looping code to the subform instead, since the subform has the controls to be inserted into tblChemicalID (the frmLogIn2 only has the catalog number, but that catalog number is displayed in the subform as well, so it should be linked by it's relationship at that point)?

[June7]

Set the CatalogNumber and ChemicalID textboxes as Locked Yes, TabStop No. I also normally set BackStyle Transparent for non-editable controls. Set LotNumber TabIndex to 1. Set the TabIndex of CatalogNumber combobox to 1.

Then this works:
Private Sub cmboCatalogNumber_AfterUpdate()
Me.Requery
Me.subfrmChemicalIDLogIn.SetFocus
DoCmd.GoToRecord , , acNewRec
End Sub

The looping code behind subform would simplify the SQL statement. However, still have the issue of what event to run the procedure. Did you decide on form Close? If so, the 'Add Another Chemical' and 'AddCatalog Number' buttons are not needed.

[dlab85]

Man, I knew I was in the ballpark of trying to focus on the subform, still a learning process.
I need to keep the "Add Catalog Number" button so that if a new chemical catalog number has arrived, and never been entered into the database, the user can quickly click that button to take them to a Catalog Number Log In form, without having to close the log in form (just a quicker navigation for them). I'm going to try and modify the looping code to run on the subform, to simplify my sql statement. I think I am going to just have a "Done" (Close) button on the log in, which would then run the looping code. So my log in form would have the Add Catalog Number button, Done button, and then an abort button. I will let you know if I have problems, but keeping my fingers crossed. Thanks!

[June7]

Instead of Add Catalog Number button consider the NotInList event of the combobox.