Simulated Not-In-List

[cheuschober]

Heya folks.

This is a pickle I've been working off an on for two weeks but now seems to be the time to stop it.

I have two tables, tbl1 and lkup1 in an obvious many-1 relationship. The key for lkup1 is comprised of two fields key1 and key2 and we'll call the related fields in tbl1 fk1 and fk2. The form for tbl1 has fk1 and fk2 as a pair of cascading comboboxes where fk2 cascades off of fk1.

I want to allow my user, in the very rare chance he will need it, to be able to add a record to lkup1 from the same form data entry occurs for tbl1 if a record doesn't exist in the lookup table. There are a few additional required fields in lkup1 so the standard Not-in-list recordset event doesn't work.

I have my additional fields to lkup1 as a hidden subform on the main form and know how to set focus and all that. What I can't figure out how to do is simulate a not-in-list event or supress the response so that I can actually set focus to my subform, enter the data, and add a record to my lkup.

Any thoughts? Regards,
Chad

 

[Treason]

Set the Limit to list property to No... Then add code like this to AfterUpdate of the Combo Box

Private Sub Combo0_AfterUpdate()
Dim i As Integer
Dim blnFound As Boolean

'Loops through the combo box to see if the item is in the list already
For i = 1 To Me.Combo0.ListCount - 1
    If Me.Combo0 = Me.Combo0.Column(0, i) Then
        blnFound = True
    End If
Next i

'If it wasnt in the list then prompt the user
If blnFound = False Then
    If MsgBox("This item not in the list, would you like to add it now?", vbYesNo) = vbYes Then
        'Code to insert the record
        DoCmd.SetWarnings False
        DoCmd.RunSQL "INSERT INTO test ( test ) SELECT '" & Me.Combo0 & "' AS Expr1;"
        Me.Combo0.Requery
    End If
End If
End Sub

This should basically prompt a user to save when an item is not in the list. This is also assuming your combobox has 1 column

 

[cheuschober]

Treason --

Thanks for the assist but it's particularly complicated because my second combobox (the one in question) is a text field.

Specifically, the two comboboxes we're dealing with are an ISO Country Code and the Postal Code ... postal codes from different countries have different types of formatting etc so there's a regular expression in place for validation before a record is added etc, but it entirely prevents normal operations...

If AfterUpdate is the 'weapon of choice' then theoretically I could use DAO method on a query that counts the number of Postal Codes that match the one in the combobox for that specific countryID... if <1 then we can go through the process whereby I unhide the subform, setfocus and fill in the additional fields.

I would use DCount but I don't think I can have two qualifiers in DCount... that is to say I've not found a way to essentially make this work:

 Dcount("[PostalCode]","lkupPostalCode","[PostalCode] = Me.cboPostalCode AND [CountryID] = Me.cboCounryID")

Any additional thoughts?

Regards,
~Chad

 

[Treason]

Chad, you can use mulitiple qualifiers just like how you just did...

I would only change...

Dcount("[PostalCode]","lkupPostalCode","[PostalCode] = Me.cboPostalCode AND [CountryID] = Me.cboCounryID")

to...

Dcount("[PostalCode]","lkupPostalCode","[PostalCode] = '" & Me.cboPostalCode & "' AND [CountryID] = '" & Me.cboCounryID &"'")

Dcount is not the fastest method to use... If you already have the Zip Codes displayed in a combobox, I would check against that rather then querying the table again.

Hope this helps...

Your fellow New Yorker

 

[cheuschober]

Damn those domain functions and their quirky syntaxes.

Excellent Treason (doubly excellent fellow New Yorker). You've piqued my interest, I must say. Are you suggesting there is a method whereby I can check my non-numeric postal code against the already queried recordsource?

This combobox.list stuff interests me as I haven't come across it before.

Cheers mate,
~Chad

 

[Treason]

Yea u can... It would be slighty optimized considering the combobox already has the information. So instead of doing the >1 the way you were going to do.. try something like this

Dim i As Integer
Dim blnFound As Boolean

'Loops through the combo box to see if the item is in the list already
For i = 1 To Me.Combo0.ListCount - 1
    If Me.Combo0 = Me.Combo0.Column(0, i) Then
        blnFound = True
    End If
Next i

'If it wasnt in the list then prompt the user
If blnFound = True Then
     'Equivelent to >1
     'Insert code to unhide the subform, setfocus and fill in the additional fields.
End If