Prevent Control Enable if Query/SQL Has Data 2

[Hakala]

Hi, Tek-Tips!

I'm a SQL newbie and I get stuck on the oddest things.

I have a form on which the user enters a date and a type of work. When both of these are filled in, I want SQL to run comparing the values on the form with the values in a lookup table. If the values match, I want to disable the next control on the form. If there is no match, the user should be able to continue.

How do I write my SQL code?

My form (frmSRENExcelImport) fields are:
txtEffective - user-entered short date
txtTOWType - user-chosen from a list of three possibilities

My lookup table (tlkpRenewalImport) fields are:
Effective - short date
TOW - text with a field size of four (all TOW codes are four letters)

I made a query that compares the two:
SELECT tlkpRenewalImport.Effective, tlkpRenewalImport.TOW
FROM tlkpRenewalImport
WHERE (((tlkpRenewalImport.Effective)=[Forms]![frmSRENExcelImport]![txtEffective]) AND ((tlkpRenewalImport.TOW)=[Forms]![frmSRENExcelImport]![txtTOWType]));
but I don't know how to put it into the form module, or how to disable a control based on a result.

Thank you for any help you can give me!

 

[Melagan]

I'm almost tempted to suggest using DLOOKUP here instead of SQL, but since you're comparing multiple fields, SQL might be better.

You'll want this code behind your "second" controls afterupdate event.

Dim rstLookup As Recordset, SQLText

SQLText = "SELECT tlkpRenewalImport.Effective, tlkpRenewalImport.TOW " _
            & "FROM tlkpRenewalImport " _
            & "WHERE (((tlkpRenewalImport.Effective)=[Forms]![frmSRENExcelImport]![txtEffective]) " _
            & "AND ((tlkpRenewalImport.TOW)=[Forms]![frmSRENExcelImport]![txtTOWType]));"

Set rstLookup = CurrentDb.OpenRecordset(SQLText)

    If rstLookup.EOF And rstLookup.BOF Then
'       Values didn't match
        Exit Sub
    Else
'Assuming the name of the control you want to "disable" is "txtNextControl"
        Me.txtNextControl.Enabled = False
    End If
    
End Sub

typed - not tested.


~Melagan
______
"It's never too late to become what you might have been.

 

[Hakala]

Thank you, Melagan!

I get a run-time error (3061) "Too few parameters. Expected 2." I get this error whether there are or are not records that match.


I modified the code a bit to allow for what I want it to do:

Private Sub txtTOWType_AfterUpdate()

Dim rstLookup As Recordset, SQLText

SQLText = "SELECT tlkpRenewalImport.Effective, tlkpRenewalImport.TOW " _
& "FROM tlkpRenewalImport " _
& "WHERE (((tlkpRenewalImport.Effective)=[Forms]![frmSRENExcelImport]![txtEffective]) " _
& "AND ((tlkpRenewalImport.TOW)=[Forms]![frmSRENExcelImport]![txtTOWType]));"

Set rstLookup = CurrentDb.OpenRecordset(SQLText)

If rstLookup.EOF And rstLookup.BOF Then
' Values didn't match, so record doesn't exist: user can continue
cmdBrowse.Enabled = True
txtPath.Enabled = True
Else
' Values match, record exists. Don't allow user to continue
Exit Sub
End If

End Sub

 

[PHV]

[tt]SQLText = "SELECT Effective, TOW " _
& "FROM tlkpRenewalImport " _
& "WHERE Effective=#" & Forms!frmSRENExcelImport!txtEffective & "#" _
& "AND TOW='" & Forms!frmSRENExcelImport!txtTOWType & "'"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Melagan]

That error usually comes up when the SQL syntax is incorrect. Try this:

SQLText = "SELECT t.Effective, t.TOW " _
            & "FROM tlkpRenewalImport as t " _
            & "WHERE t.Effective=Forms!frmSRENExcelImport!txtEffective " _
            & "AND t.TOW=Forms!frmSRENExcelImport!txtTOWType"

~Melagan
______
"It's never too late to become what you might have been.

 

[Hakala]

It works! Thank you both so much!