update using form created filter

[Delindan]

I have a form that I have list boxes and text boxes that allow the user to create a filter then would like to update those records with text that they've input into the text box. My trouble is that I don't know the sql syntax of using the user input in the text boxes. Here is my code that I've tried (and doesn't work by the way)

Option Compare Database
Private Sub cmdupdate___Click()

Dim DB As DAO.Database
Dim RScropdata As DAO.Recordset
Dim strbreeder As String
Dim strcrop As String
Dim strfsspecialist As String
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim strsql As String


strsql = "SELECT * FROM [Crop Data] WHERE 1=1 "
strsql = strsql & BuildIn(Me.lstbreeder, "[Breeder]", "'")
strsql = strsql & BuildIn(Me.lstcrop, "[Crop]", "'")
strsql = strsql & BuildIn(Me.lstfsspecialist, "[FS Specialist]", "'")

Set DB = CurrentDb
Set qdf = DB.CreateQueryDef("", strsql)
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set RScropdata = qdf.OpenRecordset


If chkbreeder Then
strbreeder = Trim(txtbreeder)
DoCmd.RunSQL "UPDATE RScropdata SET RScropdata("breeder") = strbreeder;"

If chkfsspecialist Then
strfsspecialist = Trim(txtfsspecialist)
DoCmd.RunSQL "UPDATE RScropdata SET RScropdata("fs specialist") = strfsspecialist;"


txtCurrProfile = "Update Complete!"
DoEvents

End Sub

Any ideas?

 

[PHV]

What about this ?

Dim strsql As String
strsql = "WHERE 1=1 "
strsql = strsql & BuildIn(Me!lstbreeder, "[Breeder]", "'")
strsql = strsql & BuildIn(Me!lstcrop, "[Crop]", "'")
strsql = strsql & BuildIn(Me!lstfsspecialist, "[FS Specialist]", "'")
If Me!chkbreeder Then
    DoCmd.RunSQL "UPDATE [Crop Data] SET Breeder='" & Trim(Me!txtbreeder) & "' " & strsql
End If
If Me!chkfsspecialist Then
    DoCmd.RunSQL "UPDATE [Crop Data] SET [FS Specialist]='" & Trim(Me!txtfsspecialist) & "' " & strsql
End If
Me!txtCurrProfile = "Update Complete!"
DoEvents

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MazeWorX]

Me!txtCurrProfile = "Update Complete!" and requery the form to display this text

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[Delindan]

Ok...you got me thinking on the right track. Rather than trying to building a query first, use the strsql as the where statement. Let me give it a try. Thanks!

 

[Delindan]

I tried it and it works when I have stuff filled in. If, for example, I leave chkbreeder unchecked and check chkfsspecialist it gives me an error invalid use of null. I'm not sure how to address this! Thanks

 

[MazeWorX]

Nz(Me.chkBreeder,0) You can use the NZ function to handle nulls Nz(objecttocheck,valueifnull)

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[Delindan]

Thanks...worked like a charm. I've used this function before in a different way so it's great to learn another way.