Assign Result to Form

[CrystalVis]

i'm using Acess 2003 front end and backend is SQL Server 2000. i created a blank database and link it to SQL Server via ODBC. i'm trying to create form so the user can search/update/add record to a table. on the form header, i have a combo box base on the task table, once the user select a task code in the combo box, the code run and query the task table for a record match the task code selected in the combo box. if the query find a match, i want to display that record in the detail section. the combo box is working fine. however, when i click on the search button, i receive the following mesage: "Compile error: Invalid use of property". Below is the code behind the command search. i'm very new to Access and VBA. Any help/suggestion is greatly appreciated.


Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click
Dim strMsg As String
Dim strSQL As String
If Len(Me!CboTaskCode) > 0 Then
strSQL = "SELECT * FROM dbo_tblTasks WHERE TaskCode = '" & Me!CboTaskCode & "'"
'Assign RecordSource and display Detail section
Set Me.RecordSource = strSQL
'Call DisplayDetail(True)
Else
strMsg = "Please select a valid Task Code"
'Hide Detail Section
'Call DisplayDetail(False)
End If

If Len(strMsg) > 0 Then
MsgBox strMsg, , "Can't Display Task"
Me!CboTaskCode.SetFocus
'Call DisplayDetail(False)
End If
'resize form
DoCmd.RunCommand acCmdSizeToFitForm

'Screen.PreviousControl.SetFocus
'DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click

End Sub

 

[PHV]

Try to replace this:
Set Me.RecordSource = strSQL
With this:
Me.RecordSource = strSQL

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

 

[RoyVidar]

One guess, change
[tt] Set Me.RecordSource = strSQL[/tt]

to
[tt] Me.RecordSource = strSQL[/tt]

I think also this
[tt] If Len(Me!CboTaskCode) > 0 Then[/tt]

might be a tad dangerous, perhaps change to either
[tt] If Len(Me!CboTaskCode & vbNullString) > 0 Then[/tt]

or
[tt] If Trim(Me!CboTaskCode & "") <> "" Then[/tt]

Roy-Vidar

 

[CrystalVis]

Thank you all for a quick respond. i removed key word 'Set' as you suggested. Now when i select a value and click search, nothing happen? can you please advise.

 

[CrystalVis]

i forgot to mention that no data display in the detail section and no error/warning message return when i click search. thanks

 

[Remou]

You do not appear to be checking if any records are returned. Perhaps you could check for a RecordCount?

 

[CrystalVis]

like i say, i'm very green to this. what does that mean? i select a task code i know that is existing in the task table. what does RecordCount have to do with the record existing in the task table and when i select it does not show in the form detail section?

 

[PHV]

Are the controls of the detail section bound to the new record source ?

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

 

[RoyVidar]

Does a

[tt]me.requery[/tt]

after assigning the recordsource help?

Testing for a recordcount can help find out where the problem is. If the recordcount = 0, then there's something wrong with either the SQL, the criterion or something like that, if it's > 0, then there's something related to the form, for instance a missing requery, some property of the form is set incorrectly (for instance, check the ones on the data tab).

Roy-Vidar

 

[CrystalVis]

below is my latest code with the form requery as you suggested. still nothing happen


PHV,
Are the controls of the detail section bound to the new record source ? how do i check this?

thank you


Private Sub cmdSearch_Click()
Dim strMsg As String
Dim strSQL As String
'If Len(Me!CboTaskCode) > 0 Then
If Len(Me!CboTaskCode & vbNullString) > 0 Then
'DoCmd.RunSQL strSQL
strSQL = "SELECT * FROM dbo_tblTasks WHERE TaskCode = '" & Me!txtTaskCode & "'"

'Assign RecordSource and display Detail section
Me.RecordSource = strSQL
Me.Requery
'Call DisplayDetail(True)
Else
strMsg = "Please select a valid Task Code"
'Hide Detail Section
'Call DisplayDetail(False)
End If

If Len(strMsg) > 0 Then
MsgBox strMsg, , "Can't Display Task"
Me!CboTaskCode.SetFocus
'Call DisplayDetail(False)
End If
'resize form
DoCmd.RunCommand acCmdSizeToFitForm


Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click

End Sub

 

[PHV]

Are the ControlSource properties set to field names of dbo_tblTasks ?

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

 

[CrystalVis]

PHV,
i just apply your suggestion by going into each control in the detail section and set it to the corresponding field in the dbo_tblTasks. when i run the form, i see #Name? in each control but still nothing display when i select a taskcode in the combo drop down and click search. anything else i should try. thanks