Using DMAX to create an incremental ID based on a combo box

[flying linus]

I have a form the the user selects a domain via a combo box. I want to create an incremental ID so that the actual ID generated is a concatenation of the Domain selected + a three digit incremented number as text. How can this be done? I am currently inputting the IDs in by hand but want to automate the form.

 

[chappy]

Are you familiar with visual basic programming? That's probably what would work best for this situation.

A small vb routine could do the following:
1. Count all the of records in your table that have the chosen domain
2. increment the count by 1
3. convert the count to a three digit string
4. append the 3-digit string to the chosen domain name

If you're familiar with visual basic, I could send you some code which you could modify to work with your forms and tables.

Kind Regards,

-Chappy

 

[flying linus]

I'm getting more familiar everyday. Send it my way and I'll try it out.

 

[Pat Hartman]

You'll get in trouble using the DCount() solution if you ever delete a record. Always use DMax() to return the highest value key. It is far safer.

 

[chappy]

Put this code in the afterupdate event for your domain combobox. I realized you can perform a query to do everything you need and then just assign the result of the query to your id field on your form, so this is simpler than my previous idea.

dim rs as recordset,str_id as string,sqlstr as string

if (combobox value) <> "" then
sqlstr = "SELECT """ & (combobox value )& """ & format(count(*)+1,'00#') as new_id FROM [your table] WHERE domain = " & (combobox value)

set rs = dbengine(0)(0).openrecordset(sqlstr)

rs.movelast
rs.movefirst

str_id = rs!new_id

rs.close
set rs = nothing

(id field) = str_id

end if

Comments:

1. The variable sqlstr as it appears above assumes you're storing domains as numbers, if you're storing them as strings then the sqlstr variable will be

sqlstr = "SELECT """ & (combobox value )& """ & format(count(*)+1,'00#') as new_id FROM [your table] WHERE domain = """ & (combobox value) & """"

2. (combobox value) is a reference to the column in your combobox that's storing the domain data as it's stored in your table.

3. In order for this code to work properly, you may have to change the vba references. To change the references, open the visual basic editor and choose "References..." from the Tools menu. Uncheck the "Microsoft ActiveX Data Objects 2.1 Library" reference and check the "Microsoft DAO 3.6 Object Library" (you'll probably have to scroll through the list to find it). You won't need to change any other references.

Let me know if you have any questions.

-Chappy

 

[Pat Hartman]

chappy,
Count() and DCount() will NOT work if any records are ever deleted. Why take the chance when all you need to do is substitute Max() or DMax()????

 

[flying linus]

Here is what I have...

Private Sub Domain_AfterUpdate()
  
    Me.System.Requery
    
    
    Dim rs As Recordset, str_id As String, sqlstr As String

    If (Domain) <> "" Then
    sqlstr = "SELECT """ & (Domain) & """ & format(Max(*)+1,'00#') as new_id FROM [tblFailures] WHERE Domain = """ & (Domain) & """"
    Set rs = DBEngine(0)(0).openrecordset(sqlstr)

    rs.MoveLast
    rs.MoveFirst

    str_id = rs!new_id

    rs.Close
    Set rs = Nothing

    (Fail_ID) = str_id

    End If

    
End Sub

It is getting hung up on

 (Fail_ID) = str_id

Can you guys take a look and see any obvious mistakes?

 

[Pat Hartman]

Max(*) needs to be Max(some field name)

If (Fail_ID) is numeric then str_id needs to be defined as the same data type. It can't be defined as a string.