Linking a combo box to a query after update 1

[Triacona]

Hi everyone,

I am struggling with running a query using a subform query linked to a combo box...
Basically I have a combo box and a subform on a page and the user clicks on a field in the combo box and presto the subform updates with the relevant info...
Now on the combo box I have added the code

DoCmd.OpenQuery stdocname, stLinkCriteria = rs.EOF

This opens a query with all the same fields as the subform.
The query, askes for date from and date to criteria and you fill these in and it generates the data sheet with all records pertaining to these dates...only problem is that the selected officer code (selected with the combo box) is not pulled through...even though the code is on the combo box and referencing variables it has within it.

Please could anyone help, it would greatly be appreciated
The code for the combo box is below:


Private Sub Combo0_AfterUpdate()
' Find the record that matches the control.

Dim rs As Object
Dim stdocname As String

stdocname = "DateTo&From No of Apps Per Officer DateQ"

Set rs = Me.Recordset.Clone

rs.FindFirst "[OFFCODE] = '" & Me![Combo0] & "'"

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

DoCmd.OpenQuery stdocname, stLinkCriteria = rs.EOF



End Sub

 

[GKChesterton]

Three observations:

First, the code seems to be a chopped-up wiz-generated routine that lacks sensibleness.

Second, I don't see [tt]stLinkCriteria[/tt] declared. Do you have "Option Explicit" at the top of your module? This is very important. Perhaps this variable is declared outside the procedure.

Third, a query with the name
[tt]DateTo&From No of Apps Per Officer DateQ[/tt]
is kind of asking for trouble, due to the use of spaces and an ampersand (&)

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[Triacona]

Thanks very much

I have set Option Explicit at the top of the module I.E:

Option Compare Database
Option Explicit

Private Sub Combo0_AfterUpdate()
' Find the record that matches the control.

Dim rs As Object
Dim stdocname As String
Dim stLinkCriteria As String


stdocname = "DateTo&From No of Apps Per Officer DateQ"

Set rs = Me.Recordset.Clone

rs.FindFirst "[OFFCODE] = '" & Me![Combo0] & "'"

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

DoCmd.OpenQuery stdocname, stLinkCriteria = rs.EOF



End Sub

I set the stlinkciteria as a string, is this incorrect, is it using the Officer code as a string to input into the query??
I run it now and the query returns all the officers in the specified time period...so it seems it is not taking the selected officer code in the combo box and using it in the query...

Any more help would be greatly appreciated
Kind regards
Triacona

 

[SeeThru]

This code is to find the first record, not to limit the list...

You need to:

edit the query to refer to the form i.e. in the query, under the offcode field, put criteria to Forms!formname!Combo0

or

Generate the full SQL for the for the query on the fly, create the query and view it.

SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[Triacona]

Yahoo! that is brilliant, thank you so very much!!

it works!