Prefilling next number in sequence

[vbaInet]

Joe Customer is not an ID. Plus you're not following the steps I explained in post #10.

 

[hunterfan48]

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.

So back to post #10...

I have no idea how to make this happen. Again, here's my 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

Could you inform me how to write code to requery the combo box, set focus to the combo box, and set the TEXT property. I honestly have no idea where to even go from here...

I can see what you're saying, but I'm not knowledgable enough to take that n figure out what code will make that happen.

 

[hunterfan48]

Thanks everyone for all your help so far. I've posted my database so you guys can better see what I'm missing.

The form name is 'copytest'

Please take a look to see if you can help me find where I went wrong.

Here's the steps...

1) Open form (copytest)
2) In the top right corner, type your name. (LOL hopefully it won't be in the list)
3) When finished, press tab or enter to active the not in list event.
4) Enter your info in the pop-up form and click 'enter' or 'cancel'
5) You will be able to see the error message I'm getting and see my code.

Thanks for all your help!

 

[boblarson]

Okay, here you go - a working copy. I used the ListEditItems property and also changed the way the form was populated when the not in list event happens.

 

[hunterfan48]

Bob...

YOU ARE THE MAN! LOL thanks for your help...just like that it's fixed. Just awesome...

1) Why was I getting an error code in the 1st place?
2) What code did you change? Where was I going wrong?
3) Very cool on getting the 1st name to prefill in to the 1st name field and the last name to prefill into the last name field.

Again...thanks so much!

 

[hunterfan48]

Btw...when I look at my code compared to the code you used in the event procedure for 'not in list,' the only difference I see is that you put an apostrophe in front of each line of code including the line in between the lines of code.

It made the text green, which means it's a comment. So, doesn't that mean there's actually no code being run there??

 

[boblarson]

Btw...when I look at my code compared to the code you used in the event procedure for 'not in list,' the only difference I see is that you put an apostrophe in front of each line of code including the line in between the lines of code.

It made the text green, which means it's a comment. So, doesn't that mean there's actually no code being run there??

I disabled your code by commenting it out. I didn't delete it but it isn't needed if you use what I put in.

 

[boblarson]

Bob...

YOU ARE THE MAN! LOL thanks for your help...just like that it's fixed. Just awesome...

1) Why was I getting an error code in the 1st place?

Because you weren't using the NotInList event properly.

2) What code did you change? Where was I going wrong?

First off, with Access 2007/2010 you don't need NotInList code at all. You just use the List Items Edit Form property of the combo box to specify a form to use to add the new data.

3) Very cool on getting the 1st name to prefill in to the 1st name field and the last name to prefill into the last name field.

If you look at the On Load event of the form to add the information I used the Split function to do it.

 

[hunterfan48]

Because you weren't using the NotInList event properly.

First off, with Access 2007/2010 you don't need NotInList code at all. You just use the List Items Edit Form property of the combo box to specify a form to use to add the new data.

If you look at the On Load event of the form to add the information I used the Split function to do it.

1) Right, but I was hoping to find out what exactly was wrong with my code and where the error was coming in. Just simply trying to learn as much as I can when you take YOUR time to help me, which I really appreciate.

That's funny you mention about Access 2007 and the need not to use the NotInList code. I actually had a form setup like that, but it just wasn't a popup form. I tried to implement the code from a sample database from the book, Access 2007: The Missing Manual. Within the VBA chapter, they had this exact example to use the NotInList code so that's why I thought I should follow how they do it, since I'm the noob to this all.

Any reason why they would recommend us to use that property as opposed to not using it like you had mentioned?

Bob,
Again thanks for taking the time to help me out. For the future, is there a site within this forum or another site which you would recommend which would help me understand more of this vba coding? For example, I seen the code for the Split function, but I have no idea how to even write something like that for my own use. Any suggestions for breaking the complexity down and being apply the appropriate function to each line of code to help accomplish my goals?

Thanks as always,
Brady

 

[hunterfan48]

If you get a minute, I'd like to see what I had wrong with my original code so I can know for the future.

Thanks again,
Brady

 

[hunterfan48]

I tried copying this form into my ACTUAL database and everything worked out ok, except it will not prefill the next number in the ID field. My code is the exact same as what you used in your example...do you know what would be causing it not to bring up the next number for my ID field?

Here's the code...

Private Sub Form_Load()
If CurrentProject.AllForms("Form10").IsLoaded Then
Dim varSplit As Variant

varSplit = Split(Forms!Form10!CustomerID.Text, " ")
Me!FirstName = varSplit(0)
Me!LastName = varSplit(1)
End If
End Sub

Thanks!
Brady

 

[hunterfan48]

Hahah I finally found the difference...it was a DMAX formula in my default value.

Now, here's what I'd like to figure out. Since my 1st three fields are prefilled with the default value and the split of the name into a first name and last field name, is there a way to have the cursor automatically move to the 4th box and be ready to start typing once the form opens?

Thanks
Brady