OnNotInList - tweaking pasted code

[AccessIdiot]

I know I know, I'm the worst kind of coder. I am working on my first big Access project and with no formal training I am sort of stumbling my way through. I haven't worked with VB since college about 5 years ago.

I am trying to add a new item to a combo box by opening a new form. Here is my setup:
Specimen form has combo box Species_Code. Type something into Species_Code and if it's not there you get the pop up "Want to add?" if Yes then open Species form, type in a new species including other fields in that table (common name, scientific name, genus, etc). Then close the form and go on your merry way with the Specimen form.

Anyway, I copied this from a Microsoft suggested code page:

On the Species_Code combo box on the Specimen form:

Private Sub Species_Code_NotInList(NewData As String, Response As Integer)
Dim Result
Dim Msg As String
Dim CR As String
 
    CR = Chr$(13)
 
    If NewData = "" Then Exit Sub
 
    Msg = "'" & NewData & "' is not in the list." & CR & CR
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
       DoCmd.OpenForm "Species", , , , acAdd, acDialog, NewData
    End If
 
    Result = DLookup("[Species_Code]", "Species", _
             "[Species_Code]='" & NewData & "'")
    If IsNull(Result) Then
       Response = acDataErrContinue
       MsgBox "Please try again!"
    Else
       Response = acDataErrAdded
    End If
End Sub
 

And on the Species form

Private Sub Form_Load()
   If Not IsNull(Me.OpenArgs) Then
      Me![Species_Code] = Me.OpenArgs
   End If
End Sub
 

Here's what happens: everything seems to work fine except that when the Species form opens it doesn't go to the last/new record in the table (there are already 100 species in the table, hence the drop down that you can add to). Instead it shows record 1 or 1 and the autonumber primary key is something like 1304579 instead of 101.

When I open the Species table the record was added but again the autonumber primary key is that weird number and not the logical next increment.

Any ideas?

And if I may be so bold - is there any way to prevent someone from continuing to work on the Specimen form if the add new species function/form is firing?

thanks, i love communities like this one.

melissa (the Access Idiot)

[Rabbit]

I know I know, I'm the worst kind of coder. I am working on my first big Access project and with no formal training I am sort of stumbling my way through. I haven't worked with VB since college about 5 years ago.

I am trying to add a new item to a combo box by opening a new form. Here is my setup:
Specimen form has combo box Species_Code. Type something into Species_Code and if it's not there you get the pop up "Want to add?" if Yes then open Species form, type in a new species including other fields in that table (common name, scientific name, genus, etc). Then close the form and go on your merry way with the Specimen form.

Anyway, I copied this from a Microsoft suggested code page:

On the Species_Code combo box on the Specimen form:

Private Sub Species_Code_NotInList(NewData As String, Response As Integer)
Dim Result
Dim Msg As String
Dim CR As String
 
    CR = Chr$(13)
 
    If NewData = "" Then Exit Sub
 
    Msg = "'" & NewData & "' is not in the list." & CR & CR
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
       DoCmd.OpenForm "Species", , , , acAdd, acDialog, NewData
    End If
 
    Result = DLookup("[Species_Code]", "Species", _
             "[Species_Code]='" & NewData & "'")
    If IsNull(Result) Then
       Response = acDataErrContinue
       MsgBox "Please try again!"
    Else
       Response = acDataErrAdded
    End If
End Sub
 

And on the Species form

Private Sub Form_Load()
   If Not IsNull(Me.OpenArgs) Then
      Me![Species_Code] = Me.OpenArgs
   End If
End Sub
 

Here's what happens: everything seems to work fine except that when the Species form opens it doesn't go to the last/new record in the table (there are already 100 species in the table, hence the drop down that you can add to). Instead it shows record 1 or 1 and the autonumber primary key is something like 1304579 instead of 101.

When I open the Species table the record was added but again the autonumber primary key is that weird number and not the logical next increment.

Any ideas?

And if I may be so bold - is there any way to prevent someone from continuing to work on the Specimen form if the add new species function/form is firing?

thanks, i love communities like this one.

melissa (the Access Idiot)

It's acFormAdd, not acAdd.

The thing about autonumbers is that they don't renumber themselves. If you had a 100 records, then deleted all the records, the next record you add will be 101 and not 1. It's just the way autonumbers work in Access.

[AccessIdiot]

Thanks for that, but still the same problem.

So there is no way to launch the Species form to add a new species and have it go to the next new record (101)?

[ADezii]

I know I know, I'm the worst kind of coder. I am working on my first big Access project and with no formal training I am sort of stumbling my way through. I haven't worked with VB since college about 5 years ago.

I am trying to add a new item to a combo box by opening a new form. Here is my setup:
Specimen form has combo box Species_Code. Type something into Species_Code and if it's not there you get the pop up "Want to add?" if Yes then open Species form, type in a new species including other fields in that table (common name, scientific name, genus, etc). Then close the form and go on your merry way with the Specimen form.

Anyway, I copied this from a Microsoft suggested code page:

On the Species_Code combo box on the Specimen form:

Private Sub Species_Code_NotInList(NewData As String, Response As Integer)
Dim Result
Dim Msg As String
Dim CR As String
 
    CR = Chr$(13)
 
    If NewData = "" Then Exit Sub
 
    Msg = "'" & NewData & "' is not in the list." & CR & CR
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
       DoCmd.OpenForm "Species", , , , acAdd, acDialog, NewData
    End If
 
    Result = DLookup("[Species_Code]", "Species", _
             "[Species_Code]='" & NewData & "'")
    If IsNull(Result) Then
       Response = acDataErrContinue
       MsgBox "Please try again!"
    Else
       Response = acDataErrAdded
    End If
End Sub
 

And on the Species form

Private Sub Form_Load()
   If Not IsNull(Me.OpenArgs) Then
      Me![Species_Code] = Me.OpenArgs
   End If
End Sub
 

Here's what happens: everything seems to work fine except that when the Species form opens it doesn't go to the last/new record in the table (there are already 100 species in the table, hence the drop down that you can add to). Instead it shows record 1 or 1 and the autonumber primary key is something like 1304579 instead of 101.

When I open the Species table the record was added but again the autonumber primary key is that weird number and not the logical next increment.

Any ideas?

And if I may be so bold - is there any way to prevent someone from continuing to work on the Specimen form if the add new species function/form is firing?

thanks, i love communities like this one.

melissa (the Access Idiot)

If you are getting 'wierd' numbers that are also 'not in sequence' in an AutoNumber Field, then check the New Values property for the Field in the Grid below. Instead of being set to Increment, you may have it set to Random.

[Rabbit]

I'm stumped, no idea why it won't go to a new record. As far as I can tell, it should. Maybe someone else will know why.

[MMcCarthy]

If you run compact and Repair on the database the numbers should resequence and give you 101.

Mary

[AccessIdiot]

If you are getting 'wierd' numbers that are also 'not in sequence' in an AutoNumber Field, then check the New Values property for the Field in the Grid below. Instead of being set to Increment, you may have it set to Random.

Hi, I did check and it is set to increment.

mmccarthy - is that just a temporary solution? or will that fix it so that it returns a correct number any time a new record is added?

thanks everyone!

[MMcCarthy]

Hi, I did check and it is set to increment.

mmccarthy - is that just a temporary solution? or will that fix it so that it returns a correct number any time a new record is added?

thanks everyone!

As long as you don't start adding and deleting records it should be fine.

Mary

[Rabbit]

As long as you don't start adding and deleting records it should be fine.

Mary

Didn't know Compact and Repair did that. Good to know.

I suppose you could set up the database to compact on close. Tools > Startup.

[AccessIdiot]

Okay I figured out what I was doing wrong but not how to fix it. See, there is good reason why I am called Access Idiot! :-)

This is so basic I know that I am embarrassed to be asking but hey, I guess we all gotta start somewhere.

So what I am doing wrong has to do with the control and row source for the combo box. I have this form for "Specimen". On the Specimen form I want to have a drop down for Gender. I want the values for the dropdown to come from the Gender table but I want the gender chosen for that particular specimen to be held in the Specimen table. Pretty basic right? So I have the primary key of the Gender table as a foreign key in the Specimen table with a one-to-many relationship (many specimens can be male).

What I'm getting caught up in is how to set this in the properties of the stupid form. How do I set the Record Source for the form? What is the control source for the combo box? What is the row source? I'm getting very confused on how to set this all up.

But I think that solving this problem will solve my weird autonumber problem. Previously I had the control source as the gender field from the gender table, so anytime I chose "male" it would add a row for male to the gender table when I already had one in there.

I tried setting the record source for the form to * of the Specimen table. Then I set the control source for the combo box to be gender_id (since that is what the specimen table holds) and the row source to gender (so you'd actually see male, female, etc in the dropdown) but even though you see the text in the drop down it is looking for a number because the gender_id is an autonumber field. Argh!

I hope this makes sense and I hope you don't think I'm so ignorant that I don't deserve an answer!

[MMcCarthy]

Okay I figured out what I was doing wrong but not how to fix it. See, there is good reason why I am called Access Idiot! :-)

This is so basic I know that I am embarrassed to be asking but hey, I guess we all gotta start somewhere.

So what I am doing wrong has to do with the control and row source for the combo box. I have this form for "Specimen". On the Specimen form I want to have a drop down for Gender. I want the values for the dropdown to come from the Gender table but I want the gender chosen for that particular specimen to be held in the Specimen table. Pretty basic right? So I have the primary key of the Gender table as a foreign key in the Specimen table with a one-to-many relationship (many specimens can be male).

What I'm getting caught up in is how to set this in the properties of the stupid form. How do I set the Record Source for the form? What is the control source for the combo box? What is the row source? I'm getting very confused on how to set this all up.

But I think that solving this problem will solve my weird autonumber problem. Previously I had the control source as the gender field from the gender table, so anytime I chose "male" it would add a row for male to the gender table when I already had one in there.

I tried setting the record source for the form to * of the Specimen table. Then I set the control source for the combo box to be gender_id (since that is what the specimen table holds) and the row source to gender (so you'd actually see male, female, etc in the dropdown) but even though you see the text in the drop down it is looking for a number because the gender_id is an autonumber field. Argh!

I hope this makes sense and I hope you don't think I'm so ignorant that I don't deserve an answer!

Speciman form based on Speciman table with GenderID as a foreign key from speciman table. You don't need the Gender table included by query (unless you need if for something else).

Set up combo box with control source property set to GenderID, which (since Gender table isn't included in the query) will store the value in the Speciman table.

Set the following Properties of the combo box.

Under Format tab
Column Count = 2
Column Widths = 0cm; 2cm

Under Data tab
Row Source = SELECT GenderID, GenderName FROM Gender
Bound Column = 1

Mary

[AccessIdiot]

Okay this is something that really ticks me off. See, I DID all that and it wouldn't work. When I erased the combo box and used the toolbox to draw one in and go through the wizard it DID work. Why? That is incredibly frustrating.

I should mention that when I created the form I went through the wizard and added all the fields from the Specimen table, then went into each field, changed it to a combo box, and then went into the properties to set the row source, bound column, etc. Mary when I change the settings to what you suggested the combo box displays the gender id, not the gender text which is in column 2. Any ideas why?

Seems like it should work if the properties are exactly the same no matter how you get there.

thanks all for your help, i'm glad i finally fixed the problem but I'm not happy with the method.

[MMcCarthy]

Okay this is something that really ticks me off. See, I DID all that and it wouldn't work. When I erased the combo box and used the toolbox to draw one in and go through the wizard it DID work. Why? That is incredibly frustrating.

If you haven't set the lookup list for the genderID in the table it will only come in as a textbox.

Mary when I change the settings to what you suggested the combo box displays the gender id, not the gender text which is in column 2. Any ideas why?

Did you set the column widths I gave you.

[AccessIdiot]

If you haven't set the lookup list for the genderID in the table it will only come in as a textbox.

Did you set the column widths I gave you.

I did set the column widths, thank you. That's another thing I don't get. Why do you have to set the column count to 2 and hide one? Why not just set it to the one you want to display? Kind of a weird quirk isn't it?

I did not set the lookup list. Question about that - if I set it in the table properties does that mean I can't add to it using the NotInList property?

again, thanks for all the patience and help - i am slowly getting it down!

[AccessIdiot]

Okay I am making progress. I can open a form and have it go to a blank new record and the autonumber increments just fine. I have a combo box on this form. I type something into my combo box and it opens up another form to add the data to the underlying table. However, on THAT form the auto number is huge again and not incremental. Also, when I try to add data to a combo box on THAT form I get the dreaded standard Access pop up that my data wasn't chosen from the list.

So in other words:

Specimen Form - autonumber works great; has combo box called "species" - type in and if not in list launch Species Form.

Species Form - autonumber does not work but can fill in fields; has combo box called "genus" - type in and if not in list I get standard Access error message.

Any help?

Code on Specimen Form:

Private Sub Species_NotInList(NewData As String, Response As Integer)
Dim Result
Dim Msg As String
Dim CR As String
 
    CR = Chr$(13)
 
    ' Exit this subroutine if the combo box was cleared.
    If NewData = "" Then Exit Sub
 
    ' Ask the user if he or she wishes to add the new customer.
    Msg = "'" & NewData & "' is not in the list." & CR & CR
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
       ' If the user chose Yes, start the Species form in data entry
       ' mode as a dialog form, passing the new species code name in
       ' NewData to the OpenForm method's OpenArgs argument. The
       ' OpenArgs argument is used in Species form's Form_Load event
       ' procedure.
       DoCmd.OpenForm "Species", , , , acFormAdd, acDialog, NewData
    End If
 
    ' Look for the species code name the user created in the Specimen form.
    Result = DLookup("[Species_Code]", "Species", _
             "[Species_Code]='" & NewData & "'")
    If IsNull(Result) Then
       ' If the species code was not created, set the Response argument
       ' to suppress an error message and undo changes.
       Response = acDataErrContinue
       ' Display a customized message.
       MsgBox "Please try again!"
    Else
       ' If the species code was created, set the Response argument to
       ' indicate that new data is being added.
       Response = acDataErrAdded
    End If
 
End Sub

Code on Species Form

Private Sub Form_Load()
   If Not IsNull(Me.OpenArgs) Then
      Me![Species_Code] = Me.OpenArgs
   End If
End Sub
 
Private Sub Genus_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String
 
    strMsg = "'" & NewData & "' is not an available AE Name " & vbCrLf & vbCrLf
    strMsg = strMsg & "Do you want to associate the new Name to the current DLSAF?"
    strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type it."
 
    If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
        Response = acDataErrContinue
    Else
        Set db = CurrentDb
        Set rs = db.OpenRecordset("Genus", dbOpenDynaset)
        On Error Resume Next
        rs.AddNew
            rs!AEName = NewData
        rs.Update
 
        If Err Then
            MsgBox "An error occurred. Please try again."
            Response = acDataErrContinue
        Else
            Response = acDataErrAdded
        End If
 
    End If
 
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

[NeoPa]

I did set the column widths, thank you. That's another thing I don't get. Why do you have to set the column count to 2 and hide one? Why not just set it to the one you want to display? Kind of a weird quirk isn't it?

I did not set the lookup list. Question about that - if I set it in the table properties does that mean I can't add to it using the NotInList property?

again, thanks for all the patience and help - i am slowly getting it down!

Melissa (I can't call you AccessIdiot - it doesn't work for me ;)),
If you have a ComboBox where you want to return 1 value and show another, then it needs to have both the shown and the returned fields as columns. You can show both and return one, but you can't either show or return a column unless it's included in the data.
I'm afraid I didn't really follow the other question - sorry.

[AccessIdiot]

Thank you NeoPa, that makes perfect sense now. :-)

Mary check your email and thank you!

cheers,
melissa

[NeoPa]

No Problem.
We'll make an expert of you yet. I can just see the introductory thread :
'Welcome to our newest Access Expert AccessIdiot.' :D

[MMcCarthy]

Private Sub Genus_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String
 
    strMsg = "'" & NewData & "' is not an available AE Name " & vbCrLf & vbCrLf
    strMsg = strMsg & "Do you want to associate the new Name to the current DLSAF?"
    strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type it."
 
    If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
        Response = acDataErrContinue
    Else
        Set db = CurrentDb
        Set rs = db.OpenRecordset("Genus", dbOpenDynaset)
        On Error Resume Next
        rs.AddNew
            rs!Genus = NewData
        rs.Update
 
        If Err Then
            MsgBox "An error occurred. Please try again."
            Response = acDataErrContinue
        Else
            Response = acDataErrAdded
        End If
 
    End If
 
   rs.Close
   Set rs = Nothing
   Set db = Nothing
   Me.Genus.Requery
 
End Sub
 

[MMcCarthy]

The main problem with the NotInList code was you were trying to update a non existant field.

Your code had

 
rs!AEName = NewData

instead of

 
rs!Genus = NewData

[MMcCarthy]

Problem regarding checkbox on the form controlling the subform was that it was unbound. Therefore when you set it to true on one form record it was remaining true for all records. I've gotten around this by creating a field in the speciman table to store the value of this checkbox. It will now change with the forms on current event. The other problem was that the subforms data entry property was set to yes so you couldn't view existing records but only add new records. I've changed this to no.

Mary

[AccessIdiot]

The main problem with the NotInList code was you were trying to update a non existant field.

Your code had

 
rs!AEName = NewData

instead of

 
rs!Genus = NewData

I never would have seen that in a million years - thank you!

Problem regarding checkbox on the form controlling the subform was that it was unbound. Therefore when you set it to true on one form record it was remaining true for all records. I've gotten around this by creating a field in the speciman table to store the value of this checkbox. It will now change with the forms on current event. The other problem was that the subforms data entry property was set to yes so you couldn't view existing records but only add new records. I've changed this to no.

Mary

I knew it was unbound but didn't know how to bind it. I never would have thought to add a field to the table. I also didn't realize that the data entry property set to yes meant you couldn't view existing records? That doesn't seem intuitive. Thanks for catching that and fixing my checkbox problem as well!

I'm sure I'll be back with more problems as our forms grow. This has been a great learning experience for me though - thanks to everyone for the help and especially to Mary!

[MMcCarthy]

No problem!

Glad to help.

Mary

[AccessIdiot]

Okay I'm back. :-) That didn't last long did it?

I've been playing with the code a bit, trying to break it (because I know the end user eventually will and they'll be stuck on a boat in another state and calling me for help on how to fix it) and of course I did. I was getting a compile error on the code on the 2nd combo box, the one for Genus on the Species form. So I got rid of it and found simpler code that I am using instead:

Private Sub Genus_NotInList(NewData As String, Response As Integer)
    Dim strSQL As String
    Dim i As Integer
    Dim Msg As String
 
    'Exit this sub if the combo box is cleared
    If NewData = "" Then Exit Sub
 
    Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"
 
    i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Genus Category...")
    If i = vbYes Then
        strSQL = "Insert Into Genus ([Genus]) " & _
                 "values ('" & NewData & "');"
        CurrentDb.Execute strSQL, dbFailOnError
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
End Sub
 

This works great! Except I am still having problem with the autonumber not incrementing and giving me huge weird numbers, both for the new species that was added to the combo box on Specimen and to the new genus that was added to the combo box on Species.

Any ideas why this is so?

Also, when I close the form that adds a new Species the Specimen form advances to a new record. But I would like it to remain on the current record in case the user hasn't filled out all the information on the form. I want them to have to advance to a new record when they are ready. Is there any way to set this up?

Note that I do have some code on the Specimen form that causes it to open to a new/blank record when it is launched (the onload event) but I was hoping that wouldn't fire when the form is already opened. Could it be interfering?

[MMcCarthy]

Okay I'm back. :-) That didn't last long did it?

I've been playing with the code a bit, trying to break it (because I know the end user eventually will and they'll be stuck on a boat in another state and calling me for help on how to fix it) and of course I did. I was getting a compile error on the code on the 2nd combo box, the one for Genus on the Species form. So I got rid of it and found simpler code that I am using instead:

Private Sub Genus_NotInList(NewData As String, Response As Integer)
    Dim strSQL As String
    Dim i As Integer
    Dim Msg As String
 
    'Exit this sub if the combo box is cleared
    If NewData = "" Then Exit Sub
 
    Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"
 
    i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Genus Category...")
    If i = vbYes Then
        strSQL = "Insert Into Genus ([Genus]) " & _
                 "values ('" & NewData & "');"
        CurrentDb.Execute strSQL, dbFailOnError
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
End Sub
 

This works great! Except I am still having problem with the autonumber not incrementing and giving me huge weird numbers, both for the new species that was added to the combo box on Specimen and to the new genus that was added to the combo box on Species.

Any ideas why this is so?

Also, when I close the form that adds a new Species the Specimen form advances to a new record. But I would like it to remain on the current record in case the user hasn't filled out all the information on the form. I want them to have to advance to a new record when they are ready. Is there any way to set this up?

Note that I do have some code on the Specimen form that causes it to open to a new/blank record when it is launched (the onload event) but I was hoping that wouldn't fire when the form is already opened. Could it be interfering?

Leave this with me for a while. I'll play around a bit and get back to you tomorrow.

Mary

[AccessIdiot]

Thanks Mary!

[NeoPa]

I go away for five minutes and come back to find 490 (odd) new posts :(
I presume from the back-and-forth that the two of you are working on an e-mailed version of the actual database rather than intuitively from what's been posted?
Good work anyway Mary - not been idle again I see :)

[AccessIdiot]

Yes I emailed a copy to Mary but nothing much has been changed from what has been posted. I am happy to send you a copy if you have any ideas! :-D

[NeoPa]

Thanks for the offer Melissa, but I'll take a pass.
I just wanted to make it plain to any other readers of the thread that this had happened and I'm not remotely 'jealous' of the attention Mary has deservedly got from her work.
One of her answers was posted which referenced information which wasn't publicly available, so I guessed, and thought it a good idea to make it clear.
I'll help where I can of course, but if you have Mary on your thread you don't often need anyone else ;)