I have an ID field which can not be autonumbered, and serves as the primary key for a table. When we put a new record in we need the next record to be numbered in one of two sequences that are pre-existing and can not be changed.
I was trying to do this:
=IIf([lst_DeviceType]="Cell Phone",DMax("DeviceNum","tbl_Cell_Tab","DeviceNum < 70000")+1,DMax("DeviceNum","tbl_Cell_Tab")+1)
Inside the field that will be used to create a device number.
What we have are cell phones and tablets, for cellphones the devices start at 10000, and for tablets they start at 70000. What I need to have happen is for a selection from a listbox, "Cell Phone" or "Tablet" to cause the next proper sequence number to be generated. ie. 10125 for "Cell Phone" but 70725 for "Tablet"
The above code returned a #Error
My question is thus two fold : One, is something wrong with my code? I am thinking that I am not properly referencing the listbox, but maybe that isn't it. Or two, can I not do this in the text box itself?
This is the code being used in the text box, I could try an OnUpdate on the form, Which broke when I tried that using this code it was placed in the OnUpdate for the listbox, because that's the main selector:
If Me.lst_DeviceType = "Cell Phone" Then
Me.txt_DN.Value = DoCmd.RunSQL("SELECT MAX(DeviceNum)+1 FROM tbl_Cell_Tab
WHERE DeviceNum < 70000")
Else
Me.txt_DN.Value = DoCmd.RunSQL("SELECT MAX(DeviceNum)+1 FROM
tbl_Cell_Tab")
End If
Me.txt_DN.Locked = True
but I wonder if that is both the best and easiest way to do it?
Listboxes and comboboxes behave differently.
For a listbox, must include Column index when pulling value from selection: [lst_DeviceType].Column(0)
Then you will need code to save the calculated value. Instead of expression in textbox, bind textbox to ID field and use listbox AfterUpdate event:
If IsNull(Me!ID) Then
Me!ID = IIf(...)
End If
AFAIK, cannot use DoCmd.RunSQL to return a value to a variable. That's what domain aggregate functions are for.