dynamic SQL?

[awake2424]

I am a beginner to access 2003 and would like to be able to pull up a form with say 11 fields that are blank, click on a search button. enter a search field (cboSearchField)and specific text (txtSearchString) and once a match is in the table (Log) have the fields in the form be filled in. I can not seem to accomplish this, can anyone help? Thanks.

 

[KeithG]

Can you post an example of your db?

 

[llkhoutx]

For starters, put your (unbound) filter controls, probably but not necessarily combo boxes, in the form header to logically separate them from any bound controls, then have a button to build the query, the code for same would be

dim sSQL as string
ssql = "SELECT * FROM [TABLE NAME] "
or
ssql = "SELECT A, B, C, etc. FROM"
'cycle through each filter control
'this gets complicated because each filter control can be a different datatype

'numeric datatype
if not isnull(me!ctrl1) then
if right(ssql,5) <> "WHERE" THEN
ssql = ssql & " WHERE FieldName1 = " & me!ctl1
else
ssql = ssql & " AND FieldName1 = " & me!ctl1
end if
end if

'text datatype
if not isnull(me!ctrl2) then
if right(ssql,5) <> "WHERE" THEN
ssql = ssql & " WHERE FieldName2 = " & chr(34) & me!ctl2 & chr(34)
else
ssql = ssql & " AND FieldName2 = " & chr(34) & me!ctl2 & chr(34)
end if
end if
'note that chr(34) is a quotation mark
'I find that that obviates the confusion of 4 adjacent quotation marks.

'date datatype
if not isnull(me!ctrl3) then
if right(ssql,5) <> "WHERE" THEN
ssql = ssql & " WHERE FieldName3 = #" & cdate(me!ctl3) & “#”
else
ssql & ssql & " AND FieldName3 = #" & cdate(me!ctl3) & “#”
end if
end if
'some time an unbound date control get interpreted as a string, so I
'always use the cdate function to convert it. The overhead is insignificant.

'keep in mind that the me!ctlis are values and must be
'concantenated to ssql as values, not strings.

'repeat as required for additional filter controls
'a true of false control can be tested as -1 and 0 (numeric), respectively.

'reset form recordsource
me.recordsource = ssql
'you may have to also requery subforms,
'depending on how they're constructed
me.requery

'ouila your filtered records will populate your form

Use the debugger to step through the the ssql construction code as it's executed, check ssql as it's modified, to make sure its a valid string.

Good luck. Hopefully this get down the path a few more steps.

 

[awake2424]

dynamic SQL

Here is an example that is similar to the database I would like to utilize. Thanks.

 

[Fear Naught]

Have a look at the attached MDB which uses a query by form process which is possibly what you are looking for. The underlying query is a bit complex but it works.

I would like to stress that the honours for producing this are not mine - I found it on one of the many Access forums that are around.

 

[awake2424]

dynamic SQL

Thank you for the code, I appreciate it.

I am getting an error however -

If Not IsNull(Me!ctrl1) Then

Run-time error '2467':

The expression you entered refers to an object that is closed or does not exsist

Do you know what this error is and how to fix it?

 

[Fear Naught]

What is ctrl1 ? If this is a list box or combo box you may have to refer to it differently.

 

[llkhoutx]

Be sure that that is the control name, not the control's recordsource.

The message tells you that ctl1 is not a control on your form.