Prefilling next number in sequence

hunterfan48

Registered User.
Local time
Today, 04:34
Joined
Aug 17, 2008
Messages
436
If I have a field that is just set up as a number field, is there specific code that will generate the next number in sequence?

Each of my customers in my customers have an ID. This field is a number field. On one of my forms, I have all my customers listed in a combo box for entering a sale. When I go to select the customer, I've created a Modal Dialog popup form to enter the information about a new customer that doesn't yet exist. Is there a way to automatically prefill the ID field with the next number in sequence?

Let's say I have 25 customers. I enter a customer in my combo box that hasn't been added yet, so my new form pops up. As it pops up, I want it to prefill the ID field with the number 26....the next number in sequence.

So, is there code to do this?

Thanks,
Brady
 
I'm sorry...I don't know whether I am or not. How would I know?

I think it is just because when I created my pop-up form, I used the 'add existing fields' button and added all of the fields from my customer table.

Would that make it bound?
 
I know I know...and I'm sorry upfront for my lack of basic knowledge. I think it is bound from what I said in my earlier post so the answer to your question is yes...as far as I know.

Now, where do we go from here?
 
Let's just confirm. What is in the Record Source property of your pop-up form?
 
And to clarify, here's a scenario:

1. You enter a customer that doesn't exist in main form
2. The pop-up form opens up because the customer doesn't exist
3. You complete this form and close it
4. You want the combo box to now display the new customer created in step 3?
 
And to clarify, here's a scenario:

1. You enter a customer that doesn't exist in main form
Correct
2. The pop-up form opens up because the customer doesn't exist
Correct
But here's the deal, I want the pop-up form to automatically fill in the next number in sequence from the ID field. Again, the field type is NUMBER, not AUTONUMBER. Is there code to have this field automatically enter the next number in sequence? From there, I would then enter the new customers' information and complete step 3.

3. You complete this form and close it
Correct
4. You want the combo box to now display the new customer created in step 3?
Correct...as well display the customer in my table customers, which it should do.
 
So here are the steps you need to take after the new record is saved:

* Requery the combo box
* Set Focus to the combo box
* Set the Text property of the combo box to the Name of the customer, not the ID. Name being the type of value that's normally display after selecting a value in the combo. Notice I said Text property, not Value.
 
Ok I will give that a try...thanks.

What about this though?
But here's the deal, I want the pop-up form to automatically fill in the next number in sequence from the ID field. Again, the field type is NUMBER, not AUTONUMBER. Is there code to have this field automatically enter the next number in sequence? From there, I would then enter the new customers' information and complete step 3.
 
Remember the DMax() function? And also remember the Default Value property of a control? You can put the DMax in there. So just open the form and acCmdRecordsGoToNew and DMax will take care of the rest.
 
Ok...so here's what I put into the Default Value property for the control (ID) on my popup form. Instead of giving me the next one in order, which would be 55, it gave me 100.

DMax("ID","tblCustomers")

I just closed everything out and opened my pop-up form all by itself. It prefilled the number 56. Why?? I'm not sure. It just keeps doing that.
 
I don't know because I can't see your db.

If you want the next number you should be using:

DMax("ID","tblCustomers") + 1
 
I'll post my database here in the future just for clarification.

Thanks...I'll double check my work.
 
1) Your new formula that you had ( + 1) works great...every time the pop-up form pops up, it prefills the ID field with the next number in sequence. THANK YOU!

2) When I type the name, "Stu Black" into my combo box and press enter or tab, the 'not in list' event runs and pops up the newly created pop-up form. I have the following code listed that makes this happen.

Code:
Private Sub CustomerID_NotInList(NewData As String, Response As Integer)
 
DoCmd.OpenForm "entercustomer", , , , , acDialog, NewData
CustomerID.Undo
CustomerID.Requery
CustomerID = DLookup("ID", "tblCustomers", "ID='" & NewData & "'")
 
End Sub

How would I get the name Stu Black to split into two fields. That being instead of 'Stu Black' being prefilled into the 'FirstName' field, I would like (IF POSSIBLE) to have the first name, Stu, be prefilled into the 'FirstName' field and have the last name, Black, be prefilled into the 'LastName' field.

3) When I finish filling in the customer information on the pop-up form, I click the 'OK' button. I then get an error...I've posted two screenshots to clarify my problem. The 1st one shows the message I get, and the 2nd one is a screenshot of what I see after I click the 'Debug' button in the shown in the 1st screenshot.
untitled-2.jpg

untitled2.jpg

Not sure where my error is coming in though??

Thanks for all the help!
 
btw...here's what my RowSource Property is for the combo box.

SELECT tblCustomers.ID, [FirstName] & " " & [LastName] AS Name FROM tblCustomers ORDER BY [FirstName] & " " & [LastName];

Could that be the problem with the error I keep getting in this line of code shown below?
CustomerID = DLookup("ID", "tblCustomers", "ID='" & NewData & "'")
 
Since you stated in post #1 that ID is a number you must remove the quotes around newdata.

CustomerID = DLookup("ID", "tblCustomers", "ID=" & NewData)

JR
 
Thanks for the response...now it gives me this error.

Run-time error '3075':
Syntax error (missing operator) in query expression 'ID=Joe Customer'.
Code:
Private Sub CustomerID_NotInList(NewData As String, Response As Integer)
DoCmd.OpenForm "entercustomer", , , , , acDialog, NewData
CustomerID.Undo
CustomerID.Requery
CustomerID = DLookup("ID", "tblCustomers", "ID=" & NewData)
 

Users who are viewing this thread

Back
Top Bottom