Solved Two Combo Boxs for Name & Number

[accessonly11]

Dear All,
I have two combo boxes on a formA,
Combo A row source is id, number and name from tlbA,
Combo B row source is id, name and number from same tlbA, but i applied criteria that Combo B shows only related Name which number is selected in Combo A, (Working FIne)

i set the limit to list property Yes, and list items edit form: add_new_form, of Combo A, but when i enter a new number, (let suppose "100") in Combo A, and clicking yes on popup message box "to edit this item in the list, it opens forms "add_new_form" while its textbox of number not show this new number "100"selected, besides it shows "[FormA]![ComboA]=100,
i dont know where is fault, kindly help to configure it out. thanks.

 

[Pat Hartman]

Don't make the combos dependent on each other. When you click one of them, clear the other to avoid confusion and then use OR in your Where clause in the RecordSource query so that the record bound to the form will be from the combo that was used.

You are trying to make the LimitToList procedure affect two combos. It is designed to affect one. You have to manually requery the second.

Personally, I find that if you allow users to add records on the fly using this technique, they just get sloppy and enter their typos so I almost never do it. I prefer them to consciously enter new records rather than "unconsciously".

 

[arnelgp]

you can use the "On Not in List" event of your combobox, to ask whether to add the "new" item to your table:

Private Sub Combo0_NotInList(NewData As String, Response As Integer)
    If MsgBox(NewData & ", is not on the list. Add it?", vbQuestion + vbYesNo) = vbYes Then
        DoCmd.OpenForm FormName:="add_new_form", OpenArgs:=NewData, WindowMode:=acDialog
        Response = acDataErrAdded
    End If
End Sub

set add_new_form, "Data Entry" property to Yes and add code to it's Load event:

Private Sub Form_Load()
    Me!Number = Me.OpenArgs
End Sub

 

[accessonly11]

Don't make the combos dependent on each other. When you click one of them, clear the other to avoid confusion and then use OR in your Where clause in the RecordSource query so that the record bound to the form will be from the combo that was used.

You are trying to make the LimitToList procedure affect two combos. It is designed to affect one. You have to manually requery the second.

Personally, I find that if you allow users to add records on the fly using this technique, they just get sloppy and enter their typos so I almost never do it. I prefer them to consciously enter new records rather than "unconsciously".

thats right, second combo box is un necessary, so i am changing it with textbox

 

[accessonly11]

you can use the "On Not in List" event of your combobox, to ask whether to add the "new" item to your table:

Private Sub Combo0_NotInList(NewData As String, Response As Integer)
    If MsgBox(NewData & ", is not on the list. Add it?", vbQuestion + vbYesNo) = vbYes Then
        DoCmd.OpenForm FormName:="add_new_form", OpenArgs:=NewData, WindowMode:=acDialog
        Response = acDataErrAdded
    End If
End Sub

set add_new_form, "Data Entry" property to Yes and add code to it's Load event:

Private Sub Form_Load()
    Me!Number = Me.OpenArgs
End Sub

thanks, this solved my problem, now working fine