using combobox for value input

[Knut Ole]

hi,
i have a combobox that pulls a list from a table, and i've added a "New" button to create a new blank record in the combobox (see below). when i have a new record (ID) in the combobox however, and want to fill that new recordset in, if the value i enter happens to be one that already exists in another record, it will CHOOSE that old record and make it "active," instead of filling in the new record with the new value...

so how do i make the combo box differentiate, ie. enter values into a new recordset even when those values happen to be similiar to already existing values?

Private Sub NewC(curComboText As String)
 
    Dim strMsg As String
    Dim rstNewContact As DAO.Recordset
 
 
    strMsg = "'" & curComboText & "' is not in the list.  "
    strMsg = strMsg & "Would you like to make a new Contact with " & curComboText & " as last name?"
 
    If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion + vbApplicationModal, "New Customer") Then
      Response = acDataErrDisplay 'Access displays its standard Error Message
      'nuContact = False
    Else
      'Decided to Add the New Customer
      EditMode (True)
      MsgBox "after editmode, curComboText=" & curComboText
      Set rstNewContact = CurrentDb.OpenRecordset("Contacts", dbOpenTable)
        With rstNewContact
            .Index = "PrimaryKey"
'            .Seek "=", comboID
'            If .NoMatch = False Then
               ' MsgBox "seek ok, " & comboID
                'strIndex = !LastName
               ' MsgBox "last name is " & strIndex
                .MoveFirst
                .MoveLast
                .AddNew
                !LastName = curComboText
                .Update
                .MoveLast
                comboID = !ID
                .Seek "=", comboID
                MsgBox "nu comboid=" & comboID
 
                Form_Query18.Combo336.Value = comboID
                Form_Query18.Combo336.Requery
                MsgBox "new contact added, " & !LastName & " + " & !FirstName
 
'            Else
'            MsgBox "id not found?! "
 '           End If
        End With
 
        curID = rstNewContact("ID")
        curLN = rstNewContact("LastName")
        curMob = rstNewContact("Mobile")
        'MsgBox curID & ", " & curLN & ", " & curMob & ", " & NewData & "+ " & varBookmark
        Response = acDataErrAdded   'Item added to underlying Recordset and the Combo
                                    'Box is Requeried, Item added to List
        rstNewContact.Close
        Set rstNewContact = Nothing
        Form_Query18.Combo336.SetFocus
        Form_Query18.Combo340.Requery
        bTextCombo336 = ""
    End If
 
 
 
End Sub
 

new button create new record and adds perhaps the last name if available... (barely working code...)


thanks for any help..

[Knut Ole]

i should mention i have three combo boxes (last name, first name, mobile number), and i'm having trouble with all three - if a add a new value to the first box, and the new ID is the same in all three, it will still move to a different recordset instead of adding new value if new value happens to be already existing. so the cb is selecting from its list, instead of adding new value?

i guess im expecting there to be some simple "setting" of the combobox i need to adjust, but perhaps i need to catch it with tab or enter button press, and edit current recordset manually?

thank

[NeoPa]

This appears to be unconnected with the ComboBoxes per se, but all to do with the code you're associating with them.

If you want different behaviour from your code you will need to change the code (This seems too obvious to be what you're asking for but unless you want someone else to develop your work for you I see no other interpretation).

[TheSmileyCoder]

When you are posting this amount of code, It would REALLY be preferable if you could omit the lines of code you have commented out.

Set rstNewContact = CurrentDb.OpenRecordset("Contacts", dbOpenTable)
        With rstNewContact
            .Index = "PrimaryKey"
                .MoveFirst
                .MoveLast
                .AddNew
                !LastName = curComboText
                .Update
                .MoveLast
                comboID = !ID
                .Seek "=", comboID
                MsgBox "nu comboid=" & comboID
 
                Form_Query18.Combo336.Value = comboID
                Form_Query18.Combo336.Requery
                MsgBox "new contact added, " & !LastName & " + " & !FirstName
 

You seem to have alot of code that has no real purpose.
You want to add a new record, so there would be no need to use .MoveFirst and .MoveLast.
Simply:

.AddNew
!LastName=curComboText
.Update

Why have the .MoveLast again after the update? If your ID field is autonumber I would THINK that you simply use:

comboID = !ID

There also seems no need to use a .Seek at that time.

There is also little point in setting the value of your forms combobox, before you have requery' it.

Form_Query18.Combo33.Undo
Form_Query18.Combo33.Requery
Form_Query18.Combo33.Value=comboID

And pretty please with sugar on top, give your querys and comboboxes a meaningfull name.

EDIT:
I also fail to see what
Response=AcDataErrContinue has of purpose in a procedure where Response has not been declared and is not part of the function. I strongly recommend that you add Option Explicit to the top line of your module!

[Knut Ole]

thanks for reply,
im sorry for posting messy code. as for superfluous code, thanks smileycoder, i'll look it up. the .movefirst/last is recommended by microsoft, however, to "populate the recordset." but i know but what i pick up here and there.

as for my question, i suppose your answer neopa is that there is no setting/function inherent in comboboxes that will make it differentiate between entering new values and looking up old ones. ok, thanks for the answer. i guess i was looking for a tips as to how to go about it, but i'll figure it out myself instead, as my question here is somehow deemed inappropriate.

thanks again.

[TheSmileyCoder]

Part of your trouble comes from copying code thats intended to be run from within the NotInList event. Thats for example where you got your Response variable idea from.

In your example you create a new record with the curComboText as the Lastname, but you never specify the Firstname. If you want to add a more complicated "new" record, the best approach (In my oppinion, mind you :)) is to use a form.

I have made a small example db, of how it can be performed using a couple of events and forms. Use frm_Example and start typing some name into the combobox.

If you have questions ask.

Regarding the .MoveLast vs. MoveFirst, as far as I know, the only reason to do that, is if you wish to make use of the .RecordCount property. Now Im self-taught in access and VBA myself, so don't always take my words as the truth. Being an expert just means I've made more mistakes then you!

[NeoPa]

Knut Ole:
i guess i was looking for a tips as to how to go about it, but i'll figure it out myself instead, as my question here is somehow deemed inappropriate.

To be able to offer tips I would need a better understanding of what you're after. The question isn't so much inappropriate, as unclear. Actually, my tip was possibly more helpful than you appreciate. It tells you at least where to focus your effort, which you didn't seem too clear about in your question.

If you follow the advice in the sticky threads about how to post your questions - what to include; what not to; keeping the question to a single question; etc - this will give us something more to work with technically.

I expect Smiley's post will have helped you more on the technical side, but feel free to indicate where you've got to if you need more assistance.

PS. .MoveLast/.MoveFirst is a technique that can be required, but only when it's important that the recordset be pre-populated (such as for when the record count is required). If that describes your logic then it's helpful. Often it's not required, but you would need to decide that for yourself knowing the exact logic you require.

[Knut Ole]

Thanks for your answers, I've been away for a couple of weeks.

I've indeed heeded the advice and made separate forms for entering the info. Thanks again for your excellent responses, they are highly appreciated! I deem you naught but angels of open-source-utopia, and I solemnly swear to improve my posts and code! Thank you again neopa and smiley!

[NeoPa]

Good to hear Knut :-)

I hope (and expect) you find your questions get more and better answers as a result.