Auto-populate ID based on another form field 3

[btj]

I have a form that allows users to Add or Edit their records. I have an "Add" button that creates a blank record and a "Replicate" button that replicates all information from the current record, excluding ID.

As my record set increases in size, I have found that I need to have the "ID" field be automatically generated for users (both in new blank and replicated records) because they don't remember the last ID used.

So, is there a way to auto generate the next ID once the ID category is chosen?

Example:
Current record
ID: TC-ACL001
Category: Access Lists Policy - ACL

Replicated record
ID: TC-ACL002 (this would be the auto generated number)
Category: Access Lists Policy - ACL (replicated by code)

New blank record
ID: TC-FW002 (auto-generated because there was only one other test case under Category "FW&quot
Category: Firewall Policy - FW


Hope this makes sense. I am thoroughly stumped and would appreciate any advice/ideas you may have.

Thank you, in advance.

- Ben

 

[jebry]

Hi Ben!

Try this:

Private Sub Form_Current()

Dim strCategory As String
Dim intIDNumber As Integer
Dim lngLength As Long

If IsNull(TextID) = True Then
strCategory = "TC-" & Mid(txtCategory, InStr(txtCategory, "-&quot + 2)
lngLength = Len(strCategory)
intIDNumber = CInt(Right(DMax("YourIDField", "YourTable", "Left(YourIDField, " & lngLength & &quot = ' & strCategory & '&quot, 3)) + 1
TextID = strCategory & Format(intIDNumber, "000&quot
End If

End Sub

I haven't tested this, but it should at least get you started.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[btj]

Jeff (or anyone else),
I had to create a custom input mask to ensure that the users wouldn't mess up the ID. So I created ">"TC-"LLCCC###;0;_"

It works well, but I am unsure if it causes issues with your code.

Without the mask, I will have two sets of data that make up the ID - a 2 to 5 alpha character (i.e. INST) followed by a 3 digit number (i.e. 001).

I am assuming that I don't need the first two lines of code after the IF statement line.

Let me know when you have a moment.

- Ben

 

[jebry]

Hi!

Can't be sure without testing, but it seems to me that the input mask wouldn't require any changes to the code. Try it out and let me know how it works. Be sure to import the tables and forms you need into a blank db so you are testing on a copy!

Jeff Bridgham
bridgham@purdue.edu

 

[btj]

Jeff,
I have been working on this issue on and off for a few days, but have been unsuccessful.

I needed to go back and change a table, so this is the current applicable information about Test Cases:

Category Table
Field: CategoryID (ex: FW)
Field: Category (ex: Firewall)

Test Table
Field: tcCategory (ex: FW - pulls CategoryID and Category together in a Combo Box but saves CategoryID as entry)
Field: tcID (ex: FW001 - there is a "TC-", but it is an input mask so I don't believe I need to include it)

So...with this, I tried using the code you recommended. As I relatively new at this I cannot figure out the problem. The code runs (no errors) but it doesn't do anything either.

Here is the code:

Private Sub Form_Current()

Dim strCategory As String
Dim intIDNumber As Integer
Dim lngLength As Long

If IsNull(TextID) = True Then
strCategory = "TC-" & Mid(txtCategory, InStr(tcCategory, "-&quot + 2)
lngLength = Len(strCategory)
intIDNumber = CInt(Right(DMax("tcID", "1_tbltcMaster", "Left(tcID, " & lngLength & &quot = ' & strCategory & '&quot, 3)) + 1
tcID = strCategory & Format(intIDNumber, "000&quot
End If

End Sub


I am used to code causing some sort of error if it is not working, so I am stumped about what the issue is. Any advice you can offer would be greatly appreciated!

- Ben

 

[Katerine]

Hi btj,
Don't know if this is THE error, but it's AN error:

intIDNumber = CInt(Right(DMax "tcID", "1_tbltcMaster", "Left(tcID, " & lngLength & &quot = ' & strCategory & '&quot, 3)) + 1

Should be (just including the part with the error):
..& lngLength & &quot = '" & strCategory & "')..

Just a little tiny thing with the quotes. After lngLength & &quot = should be a single, then a double quote. Then, after strCategory & should be a double, then a single quote (it was the other way around before).

Hope that helps! Katie
Hi! I'm currently studying the COM+ programming model. Hopefully YOU'RE having fun, which would make one of us..

 

[btj]

Katie -
Thanks for finding, at least, one of my errors.

I can't seem to get that correction to be accepted. The only thing I found that works is:
intIDNumber = CInt(Right(DMax("tcID", "1_tbltcMaster", Left(tcID, " & IngLength & &quot = "' & strCategory & '&quot, 3)) + 1

As you can see, I had to use double quotes and then a single while using single then double at the end of that line. Even though that doesn't cause an error within VB, it doesn't do anything that I can see within the form.

I just don't know the best solution to this issue. It seems that the best thing would be to look up the Category value (i.e. FW), then find the set of records in ID that
use "FW", then use DMax. Thus, allowing users to fill in one field and have multiple pieces of information automatically assumed/filled in.

Even if this is a better solution, I have no idea how to do it. Regardless, I would love to hear any advice/ideas that people might have about this problem.

Thank you!
Ben

 

[Katerine]

OK, I think I see what the problem with that line is. DMax requires one long string for its criteria.

Create another string variable, and assign it to just the DMax criteria:

Dim strCriteria
...
strCriteria = "Left(tcID, " & lngLength & &quot = '" & _
strCategory & "'"

intID = CInt(Right(DMAX("[tcID]", "1_tbltcMaster", _
strCriteria), 3)) + 1

I'm really only paying attention to that line, so it may be the problem resides in some other line entirely.

Note that the Right function is used with strings - is tcID a string? If not, it needs to first be converted to one.

A couple notes on reading the strCriteria definition: it's lngLength & &quot = then a single, then double quote. Then, it's strCategory & then a double, then single, then double quote Katie
Hi! I'm currently studying the COM+ programming model. Hopefully YOU'RE having fun, which would make one of us..

 

[btj]

Katie,
I had an idea last night and I wanted to run it past you if you have the time.

As mentioned, I have two fields that are critical for this task - Category (consisting of CategoryID and Category) and tcID (formatted as "TC-FW001", but data is just FW001).

So, as a user adds or replicates a record, the cursor goes to Category field. On Category_AfterUpdate, code will be run that will do the following (not exact code):
If Me.tcCategory = "FW" Then
Me.tcID = DMax("tcID", "[table/query]","[search cond.]&quot+1
ElseIf Me.tcCategory = "EMS"... continue code for rest of Category IDs

The basic premise is to use the Category as the springboard to automatically populating the ID field.

Two things I am unsure about -
1) Will this work (i.e. is it a sound concept)?
2) Should I create a query that will be run for each CategoryID to list the numbers associated with it? Example - if Category "FW" has 2 records (FW001 and FW002) a query would list those 2 records and then the DMax function would identify FW002 and then add 1.

Please let me know what you think...

- Ben

 

[Katerine]

Hi btj,
Yes, it should work. You might want to think about the select-case statement if you decide to go that route. However, I've been thinking about this, and I think I know how I would do it, which is somewhat simpler.

I would create a group-by query that just gets the max tcID for each Category. It would go something like this:
SELECT tblTestBTJ.tcCategory, Max(tblTestBTJ.tcID) AS MaxTcID
FROM tblTestBTJ
GROUP BY tblTestBTJ.tcCategory;

You should actually create and save this query as a query, and not stick it in your code - if you stick it in your code, it will slow things down a lot and make your database leap to a huge size. Let's call the query "qryCODE-MaxTcID" for the sake of this example.

Then, in your event handler for cboCategory_AfterUpdate (controls on forms really should be renamed with a prefix giving the control type, hence the "cboCategory&quot:

Private Sub cboCategory_Click()
On Error GoTo Err_cboCategory_Click

If IsNull(cboCategory.Value) Then
'user cleared the category box.
'do nothing.
Exit Sub
Else
txttcID = GetNewID
End If

Exit_cboCategory_Click:
Exit Sub

Err_cboCategory_Click:
MsgBox Err.Description, , _
"cboCategory_Click: " & Err.Number
Resume Exit_cboCategory_Click
End Sub

Then create the following function(I'm assuming this is a bound form) (also assuming this is Access 2000. Code in Access 97 is somewhat different) (also assuming tcID is a string, in the format ("TC-" & value of tcCategory & 4-digit number):

Private Function GetNewID() As String
On Error GoTo Err_GetNewID

'This function takes the value provided
'in cboCategory, and returns a string
'with the next incremental tcID based
'on that category.

Dim lngTCID As Long, strMaxTCID As String
Dim strNewTCID As String
Dim cnn As New ADODB.Connection
Dim rstTC As New ADODB.Recordset
Dim strCriteria As String

'Set the connection for searching
Set cnn = CurrentProject.Connection
rstTC.Open "[qryCODE-MaxTcID]", cnn, _
adOpenDynamic

strCriteria = "[tcCategory] = '" & _
cboCategory.Value & "'"

With rstTC
.Find strCriteria
If .EOF Then
'First record with this category
strMaxTCID = "TC-" & _
cboCategory.Value & "0000"
Else
strMaxTCID = ![tcID]
End If
End With

lngTCID = CLng(Right(strMaxTCID, 4)) + 1

strNewTCID = "TC-" & cboCategory.Value & _
Format(lngTCID, "0000&quot
GetNewID = strNewTCID

Exit_GetNewID:
rstTC.Close
Set rstTC = Nothing
cnn.Close
Set cnn = Nothing
Exit Sub

Err_GetNewID:
MsgBox Err.Description, , _
"GetNewID: " & Err.Number
Resume Exit_GetNewID
End Function

WARNING: the first time you test this, set a breakpoint on this function. That way, if it errors on setting the recordset, you won't have to go through an infinite loop - you can just press the stop button as you're stepping through the code with F8..

Hope that helps! Katie
Hi! I'm currently studying the COM+ programming model. Hopefully YOU'RE having fun, which would make one of us..

 

[btj]

Katie,
Thank you so much! I was just looking for some feedback but this is incredible.

Unfortunately, I have a follow-up question. I put your code (both the Private Sub and the Private Function) in my primary Add form. Although I don't detect any errors, nothing is happening after I select my Category.

I believe it is because of your assumption that tcID is a string composed of "TC-"& Category.value & "0000". Currently, I have an input mask on the ID field which enters "TC-" and allows for up to 5 alpha characters and four digits (Input Mask: >"TC-"LLCCC###).

So, I believe I need to change my format to be like your or vice versa. Is that correct?

I apologize for needing to question you again, but I am just a beginner working on a complex project.

Again, thank you so much...

- Ben

 

[Katerine]

Hi btj,
Yes, you would have to change the code to correspond with how you've got the ID set up.

Also, I just found another error in my code:
Else
strMaxTCID = ![tcID]
End If

When I wrote that code, I'd forgotten that I wasn't looking in the original table, which has a field called [tcID], but was looking at a query, which instead had a field called [MaxTcID]. So replace ![tcID] with ![MaxTcID].

Sorry about that.. but glad to hear this helped

Oh.. just saw this.. probably isn't affecting much, but you should change: txttcID = GetNewID in the cboCategory_Click procedure to txttcID.Value = GetNewID.

Again, this code assumes that your Category combo box is named "cboCategory", and that your textbox which holds the tcID value is a) bound to the tcID value in the table (or written to the table elsewhere), and b) named "txttcID". Katie
Hi! I'm currently studying the COM+ programming model. Hopefully YOU'RE having fun, which would make one of us..

 

[btj]

Katie...
Thanks again for the help. I think I getting better at this because I had already caught both of the issues you mentioned.

I have not yet perfected the code but have a two interrelated questions about a specific portion of the code.

I am gettting an error in strCriteria = "[tcCategory] = '" & tcCategory.value & "'" within the Public Function. I am getting a Run-Time error: Object Required (#424) but see nothing wrong.

I am unsure what is missing, but I cannot check the complete validity of this code because it won't move past this point. I understand the purpose of this line of code, but am unsure how to check for errors.

Again, any thoughts you have would be greatly appreciated...and, as always, thanks for your continued help and patience.

- Ben

 

[Katerine]

Hi btj,
My guess: The problem probably has to do with the fact that the combo box is named the same thing as the field it's bound to. This is why combo boxes and textboxes and other controls really really need to be renamed.

The typical convention is to prefix the names of the controls with the following prefixes:

For combo boxes: cbo
For text boxes: txt
For list boxes: lst
For option buttons: opt
For command buttons: cmd
For frames: fra
For check boxes: chk
.. etc.

Please let me know if renaming the combo box (and updating all references in code..) fixes the problem.

Note: sometimes if you write event procedures for a control, then rename the control, you have to redo the event procedure (even if you rename the event handler), or it will not run. After renaming the control, set "AfterUpdate" to event procedure (again), and click the "..." button (again), and see if it brings you to an empty procedure. If it does, just cut and paste the code from the old procedure to the new one.

HTH! Katie
Hi! I'm currently studying the COM+ programming model. Hopefully YOU'RE having fun, which would make one of us..

 

[btj]

OK...I see the need for using prefixes. I have changed mine and will continue to so in the future.

Unfortunately, that did not seem to resolve the issue. So, after playing with the code for a little while this is what I have come up with two items as possible issues:

1. If Private Function GetNewID() is in the form - I get an error that says "Object Required." This error appears within the Private Sub cboTcCategory_AfterUpdate(). No code line reference...just a MsgBox

2. If Private Function GetNewID() is in a module (tried keeping as Private and changing it to Public) - I get a Compile error stating an "expected variable or procedure, not module". This error appears for the line of code txtTcID.value = GetNewID. This doesn't make sense as it should just be referencing the string created in the GetNewID function. FYI - I have tried changing txtTcID.value to Me.txtTcID but that was unsuccessful.

Let me know if you have any thoughts on this...

- Ben

 

[btj]

Katie...
Disregard previous Reply. I figured out the issues on my own!

Just some little changes, but those are always the ones that seem to be the worst to find.

Although my code is a slight variation of yours, let me know if you want me to post the entire thing.

Thanks so much for all of your help!!!

- Ben

 

[montrose]

btj, Just following your thread, would you please post your final code that works? Thanks, Montrose Learn what you can and share what you know.

 

[btj]

No problem...as noted, I only changed a few things to make the code that Katerine provided work for me and my project.

Private Sub cboTcCategory_Exit(Cancel As Integer)
On Error GoTo Err_cboCategory_Exit

If IsNull(Me.cbotcCategory) Then
'user cleared the category box.
'do nothing.
Exit Sub
Else
Me.txttcID = GetNewID
DoCmd.GoToControl "cboTester"
'added this line to original code
End If

Exit_cboCategory_Exit:
Exit Sub

Err_cboCategory_Exit:
MsgBox Err.Description, , "cboCategory_Exit: " & Err.Number
Resume Exit_cboCategory_Exit
End Sub

Then...put the Public Function within the same form's code:
Public Function GetNewID() As String
On Error GoTo Err_GetNewID

'This function takes the value provided
'in cboCategory, and returns a string
'with the next incremental tcID based
'on that category.

Dim lngTCID As Long, strMaxTCID As String
Dim strNewTCID As String
Dim cnn As New ADODB.Connection
Dim rstTC As New ADODB.Recordset
Dim strCriteria As String

'Set the connection for searching
Set cnn = CurrentProject.Connection
rstTC.Open "[qryMaxID]", cnn, adOpenDynamic

strCriteria = "[tcCategory] = '" & cbotcCategory.value & "'"

With rstTC
.Find strCriteria
If .EOF Then
'First record with this category
strMaxTCID = "TC-" & cbotcCategory.value & "000"
'changed number format from 4 to 3 zeros
Else
strMaxTCID = ![MaxTcID]
End If
End With

lngTCID = CLng(Right(strMaxTCID, 3)) + 1

strNewTCID = "TC-" & cbotcCategory.value & Format(lngTCID, "000&quot
GetNewID = strNewTCID

Exit_GetNewID:
rstTC.Close
Set rstTC = Nothing
cnn.Close
Set cnn = Nothing
Exit Function

Err_GetNewID:
MsgBox Err.Description, , _
"GetNewID: " & Err.Number
Resume Exit_GetNewID
End Function

The original query posted above worked well for my needs so I will not repost that.

 

[montrose]

btj, Katerine
Great problem solving-thanks for the interesting read and suggestions. Montrose Learn what you can and share what you know.

 

[Guest_imported]

Hi,
I have a newbie question, How do I get my textboxes (5 of them) to auto populate with the correct record by using a combo box to select an unique number of that particular record? Its a large database and this would be the most convenient way for us to imediately see a record and make changes to it. I was hoping to use the combo box so we would be able to pick the serial number with just a click. Or if you have a better way I'm all ears...

Thank you!

Todd
Trident Submarine Base
Bangor, Washington