Auto-increment Control on Form and Increase Quantity

[dlab85]

Hey everyone,
Here is what I am trying to do, and have stumbled on hurdles. I have attached pictures of the form, and inserted my attempted coding below.
1. User opens form to log in a quantity of chemicals received.
2. User selects a catalog # from drop down box and the selection fills in the Chemical Name information (this is purely a visual representation for the user, I have locked all data entry on the left side of the form, except for Lot Number).
3. User starts to enter the received date, expiration date, etc. etc. on the right side of the form.
4. User enters the number of containers at the bottom of the form, to enter the same chemical information for that many records, and then clicks Add Chemical(s) or Close Form buttons (close only if there is one chemical container to log in)
---Preferably when the user clicks the Add Chem or Close Form button, I need the control "Chemical ID" to generate the year followed by the next sequential number, i.e. 2012-0001. I need the year to represent the system year (so that it changes every year), and the sequential number to both increase throughout that year, and then start over at 1 the next year (and also be in the format of 0001, so 2013-0001, 2014-0001, etc.) And since there could be multiple containers entered on the Log In form, I also need the "Chemical ID" to increment that many times, i.e. 2013-0026 through 2013-0030 for 5 chemical containers.
I have attempted to modify June7's code from the following post: http://forums.aspfree.com/microsoft-...ta-403208.html...here is my coding:

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

I placed that coding into a module (not sure if that is part of the problem)
And then the second portion I placed in a After_Update event for Received Date (but I will be putting it on the Click event for my buttons).

Private Sub Received_Date_AfterUpdate()
Dim strChemicalID As String
'search for aborted ChemicalID and use it, else if none then
GetNewChemicalIDstrChemicalID = Nz(DLookup("ChemicalID", "tblChemicalID", "IsNull(Date_Received)"), "")
DoCmd.SetWarnings False
If strChemicalID <> "" Then
    DoCmd.RunSQL "UPDATE tblChemicalID SET Date_Received=#" & Date & "# WHERE ChemicalID='" & strChemicalID & "'"
Else
    strChemicalID = GetNewChemicalID()
    DoCmd.RunSQL "INSERT INTO tblChemicalID (ChemicalID, Date_Received) VALUES ('" & strChemicalID & "', #" & Date & "#)"
End If
DoCmd.SetWarnings True
Me.ChemicalID.SetFocus
DoCmd.OpenForm "frmLogIn", acNormal, , "ChemicalID='" & strChemicalID & "'", , , strChemicalID & "," & "frmLogin"
End Sub

I receive a compile error stating that strChemicalID was expecting a variable or function, not a module.
Thoughts, suggestions, or modifications would be greatly appreciated. Here a copy of my form as well:

[June7]

Where on that form is it indicated there are 5 containers - Size?

Your posted code is not very readable.

If you want to save multiple records that will require looping structure.

[dlab85]

The 5 containers would be indicated in the text box Quantity at the bottom of the form. I have misplaced my coding so unfortunately I can't get it more clearly posted than that, whatever I had tried to accomplish with copy and past messed up the formatting. I am attaching a copy of my database, which has a small amount of VBA, which on the After Update event in the control "Received Date" on frmLogIn, a ChemicalID that I WANT created fills in, but only for the first record created, because then the code tries to add the year again to the chemical ID (so 2013-0001 tries to become 20132013-0001).

I'm starting to think that I will just create an autoincrement field using DMax, and call that IDAutoNumber, and then run an append query to add the year onto the IDAutoNumber, to create a ChemicalID as 2013-0001, etc. Unfortunately the last four digits will just continue into the future, and not reset back to 0001 at the start of every year, which would be preferred. And I would have to have a second field that seems unnecessary.

[June7]

Modules and functions cannot have same name. Change the module name.

You can copy/paste the code from your post.

All modules should have these two lines in their header:
Option Compare Database
Option Explicit

Your form is probably more complicated than need be. It is a form/subform/subsubform arrangement. I think a form/subform would be adequate.

Looping code to create additional records in tblChemicalID could be like:

For i = 1 to Me.txtQuantity-1
    DoCmd.RunSQL "INSERT INTO tblChemicalID (ChemicalID, Date_Received, [Catalog Number]) VALUES ('" & GetNewChemicalID() & "', #" & Date & "#, '" & Me.[Catalog Number] "')"
Next

The real trick is figuring out what event to put code in and making sure the code runs when needed and not more than once.

Not sure you want to use the code you show in AfterUpdate event. That is from my example but in my project the record is committed to table then form is opened for user to complete other data entry. I have code to allow for entry abort and to later use that record for next login. In my db it is the main form that requires the generated ID, it has a subform for entering related child records about that login.

[dlab85]

June7, thank you very much for the pointers and info. I will give your input a try and see what I can come up with.

[dlab85]

Alright, road block time. So I modified the coding, see attached database. I have not tried to tie in the looping aspect, since I am trying to go one step at a time to make sure I both understand what I am doing, and troubleshoot if possible. The coding creates 2013-0001 (which is what I want), but it is placing it directly into the table, without any regard for the form values, which I know is because that is what the coding is supposed to do. But I want to fill out the form, and then create the chemicalID for the information I just entered and have that ChemicalID and the controls from Chemical ID Informatino saved to my table (tblChemicalID). I modified the form, so look at my setup for frmLogIn2. Also, the coding goes wonky on subsequent chemicalID's, with 2013-00002 (1 extra 0), and then won't create a unique ID for -0003, and onward. My brain is boggled.

[June7]

The first issue must resolve is what to do with the record in frmChemicalIDLogIn. Once you enter data into those bound fields a record is initiated. It will be committed to the table when form closes, move to another record, or run code.

Do you want to save this record as the first of quantity x then use the looping code to create x-1 more records?

[dlab85]

Yes, I would like that to be the first of the quantity, i.e. 2013-0001 to 2013-0005, with 5 bottles total quantity.

[June7]

Then code must commit the record with the generated ID.

DoCmd.RunCommand acCmdSaveRecord

Then followed by the looping code to generate remainder of quantity. I provided that example some time ago.

[dlab85]

Can we rehash where these bits and pieces are supposed to be added into the coding? Also, as stated in my previous post, your code that I modified (in the attached database from post 6) isn't behaving properly. It is adding additional zero's for the -000X aspect. I don't need you to physically change the coding in my database and send the copy back, but at least aid me in finding where the problem is, so I can troubleshoot; also, where to add the additional DoCmd.RunCommand line in the coding once the coding is working as it's suppose to.

[June7]

The real trick is figuring out what event to use. Could be a 'Save Record' button click but then how to prevent user from clicking twice or not clicking at all? Could be safest would be to run code from a 'Save and Close' button. I always disable the X close button when I want to manage form close from a command button. All depends on the behavior you want this form to do. Do you want it to remain open for adding another record?

You are not including the "A" that was in my example in your ID so need to adjust by 1 space:

NewChemicalID = Left(strChemicalID, 5) & Format(Right(strChemicalID, 4) + 1, "0000")

[dlab85]

Oh DUH! Thanks for the correction on that simple line there. I think that I would disable the X close button like you said, and it would probably be easiest to use a 'Save and Close' Button. But, this is what I'm thinking:
1. User opens frmLogIn2 (which I am going to rename to frmLogIn).
2. User selects catalog Number from drop down box at top of form.
3. User types in the fields that are applicable on the right side of the form (the right side of the form is the subform from tblChemicalID)
4. User enters a quantity into the Quantity text box.
5. User clicks a 'Save' Button, which triggers the code to assign the appropriate ChemicalID 2013-XXXX (which also shows up in the control ChemicalID on frmLogIn), uses the looping in the code to repeat the information the appropriate number of times while creating the unique ID's each time (2013-0001 through -0005 for quantity of 5), and code adds those records to the tblChemicalID
6. User can click 'Add Another Chemical' button to clear the form for another entry, OR click 'Close Form' if no further entry.
****If it more complicated to have the 'Save' button and 'Add Another Record'/'Close Form' options separate like that, as opposed to a simpler 'Save and Close' button method, due to the concern of double clicking, or easier just to code with 'Save and Close', I am okay with having the form close and then the user can just Click Log In Chemical to add another.

[June7]

If you save the initiated record of ChemicalID form (the right side) then the looping would save records for 2013-0002 through -0005.

If you don't want to save the initiated record then this should not be a bound subform. Just put unbound controls for those entries on the main form and use only looping code to save all x quantity records. Of course, by using unbound controls the form cannot be used to display existing related ChemicalID records.

6. Your choice. How much code do you want do and how much do you want to deal with user mistakes?

[dlab85]

The first sentence is correct. I want the initiated record 2013-0001 saved, and then looped through for the remaining four, -0002 through -0005.

[June7]

Looks like I edited my previous post while you posted.

Sounds like time to code and test. Good Luck.

Shall we call this one resolved and if you have more issues come back with a new thread?