NotInList or something else? (A2k3)

Topic

New Reply

I’ve been using an unbound combo box’s NotInList event to add new records via a second data entry form. I’d like to skip using the second form but I still need to check to see if the name is already in the database. What’s a good way to do that?

E

Reply | Quote

Replies

I don’t quite understand. The NotInList event occurs if the text entered by the user is not present in the Row Source of the combo box. Why would you need to check if the name is already in the database? If it were, the NotInList event wouldn’t have occurred.

Reply | Quote

Couldn’t you set it as some sort of key that doesn’t allow duplicates?

Reply | Quote

Couldn’t you set it as some sort of key that doesn’t allow duplicates?

Reply | Quote

My objective is use a single form to both verify that the data I’m entering is not already in the database and, if it isn’t, add a new record using this same form. If there a relatively simple way to do this?

E

Reply | Quote

Are you asking something like if the info already exists that it automatically pulls up all the info on that record? And if it doesn’t exist you just continue to enter data?

Reply | Quote

Are you asking something like if the info already exists that it automatically pulls up all the info on that record? And if it doesn’t exist you just continue to enter data?

Reply | Quote

I have to echo Hans’s question: if the item is in the list, it must be in the database, assuming the list is populated from the database. Perhaps if you clarify the problem we can answer your question. If you are using an unbound combobox to navigate to a particular record, then you can use the NotInList event to undo the combobox entry and move the form to a new record. Is that what you’re trying to accomplish?

Reply | Quote

“you can use the NotInList event to undo the combobox entry and move the form to a new record”

That’s exactly what I’m after! Thanks a million.
E

Reply | Quote

OK, do you need help with that? If you post your NotInList code, we can help you tweak it if necessary.

Reply | Quote

(Edited by HansV to make URL clickable – see Help 19)

Charlotte,

Thought I was going to need help with it then ran across the following — http://www.pacificdb.com.au/MVP/Code/NIL.htm%5B/url%5D — and I was demystified. Here’s the code I’m using:

Private Sub cboFind_NotInList(NewData As String, Response As Integer)

MsgBox “Record doesn’t exist. Creating new record.”
Me.cboFind.Undo
DoCmd.GoToRecord , , acNewRec
Response = acDataErrContinue

End Sub

Thanks so much for pointing me in the right direction. I knew there has to be a way to add a new record to the existing form but I had been spinning my wheels. My stumbling block was that last line of code. It didn’t make sense to me that it came after the DoCmd, in much the same way it didn’t make sense to me that you defined your OrderBy and only then have OrderByOn. I’m learning, tho some days it feels more like knocking my head against the wall. You can teach an old dog new tricks…but sometimes we’re just incredible sllloooowwwww.

E

Reply | Quote

I think the most confusing thing about NotInList code is that you MUST supply a response, regardless of whatever else you do. Since your combobox is unbound, the move to a new record doesn’t affect it, but trying to exit the routine means that whatever response you have provided will then be processed. Once you get that firmly in mind, it starts to make sense. You could have populated the response argument earlier in the code, but there wasn’t any need to do so.

Reply | Quote

I think the most confusing thing about NotInList code is that you MUST supply a response, regardless of whatever else you do. Since your combobox is unbound, the move to a new record doesn’t affect it, but trying to exit the routine means that whatever response you have provided will then be processed. Once you get that firmly in mind, it starts to make sense. You could have populated the response argument earlier in the code, but there wasn’t any need to do so.

Reply | Quote

(Edited by HansV to make URL clickable – see Help 19)

Charlotte,

Thought I was going to need help with it then ran across the following — http://www.pacificdb.com.au/MVP/Code/NIL.htm%5B/url%5D — and I was demystified. Here’s the code I’m using:

Private Sub cboFind_NotInList(NewData As String, Response As Integer)

MsgBox “Record doesn’t exist. Creating new record.”
Me.cboFind.Undo
DoCmd.GoToRecord , , acNewRec
Response = acDataErrContinue

End Sub

Thanks so much for pointing me in the right direction. I knew there has to be a way to add a new record to the existing form but I had been spinning my wheels. My stumbling block was that last line of code. It didn’t make sense to me that it came after the DoCmd, in much the same way it didn’t make sense to me that you defined your OrderBy and only then have OrderByOn. I’m learning, tho some days it feels more like knocking my head against the wall. You can teach an old dog new tricks…but sometimes we’re just incredible sllloooowwwww.

E

Reply | Quote

OK, do you need help with that? If you post your NotInList code, we can help you tweak it if necessary.

Reply | Quote

“you can use the NotInList event to undo the combobox entry and move the form to a new record”

That’s exactly what I’m after! Thanks a million.
E

Reply | Quote

I have to echo Hans’s question: if the item is in the list, it must be in the database, assuming the list is populated from the database. Perhaps if you clarify the problem we can answer your question. If you are using an unbound combobox to navigate to a particular record, then you can use the NotInList event to undo the combobox entry and move the form to a new record. Is that what you’re trying to accomplish?

Reply | Quote

My objective is use a single form to both verify that the data I’m entering is not already in the database and, if it isn’t, add a new record using this same form. If there a relatively simple way to do this?

E

Reply | Quote

I don’t quite understand. The NotInList event occurs if the text entered by the user is not present in the Row Source of the combo box. Why would you need to check if the name is already in the database? If it were, the NotInList event wouldn’t have occurred.

Reply | Quote