SQL in a form

[indesisiv]

SQL in a form - Problems

I am using an SQL query to update a listbox in my form but as the form loads i get an error that i don't understand.

I have created a query that seems to work but when i use the sql in the form it doesn't (In frmSearch)

I have attached it in 97 format

Can anyone help please
Steve

 

[MaxJam]

where do you set the record source for the search form???? i cant find it!!

 

[indesisiv]

There is no set recordsource for the search form as it is not used to display records as such.

Only the list box is based on records.
Does that make sense?

It seemed to work fine before i put in the calculation on the load sql!

Steve

 

[MaxJam]

im a bit confused but i got rid of your error, im not sure its what you want tho....

go into design mode of frmSearch
rightclick on the listbox and select properties
click on the data tab
set the rowsource property to Query1


does this work?

 

[indesisiv]

It does work but i am not sure why you should have do do this as on load it is set to exactly the same thing through the SQL.

Access is a funny thing!!

Seems to work fine with the full search form. If anyone knows why this is the case could they please explain.

Thanks MaxJam

Steve

 

[MaxJam]

its probably because you told the form ...

(through the list box's data properties in design mode ) ...

that the list box would be recieveing a RowSourceType of "Table/Query" and didnt specify what the RowSource query was (in your case it should have been "Query1")

and while initialising before entering its form_load event, the search form dealt with this as an error in one of the properties of its controls and then went on to the Form_Load event.

You can either set both rowsource = Query1
and
rowsourcetype = Table/Query in design mode and leave them completey out of form_load or you can go into the Data properties again and remove the RowSource entry (blank it) and do the same with RowSourceType,

and then change your form_load event in code to this....

-----------------------------------------------------------------------------
Private Sub Form_Load()

Dim txtSearchStrings As Variant
Dim strSQL As String

'Sets the checkbox to show incomplete records
'Sets the search string to show only incomplete records
txtSearchStrings = Me.txtSearch

' set the rowsource type...
Me.lstResults.RowSourceType = "Table/query"

' set the row source (the name of the query that will fill this listbox)
Me.lstResults.RowSource = "Query1"

'refresh the listbox
Me!lstResults.Requery

End Sub
-----------------------------------------------------------------------------

Either way will work, the only error you had was telling it what type of rowsource you were using but you didnt specify the rowsource (query) itself which caused an error BEFORE you hit form_load at which stage rowsource was re-set by the strsql parameter.

phew....

hope you can kinda understand that??

good luck! any more questions let me know.

now... back to work i go!

 

[indesisiv]

Sounds about right to me thanks

 

[Pat Hartman]

You could move your code to the Open event so that the RowSource is set before it is needed. Access attemps to populate the form in the Load event and THEN it runs your code. So, you either need to follow MAXJAM's suggestion or move your code to the Open event of the form.

 

[indesisiv]

Thanks a lot Pat ... that is exactly what was up.