link and sort main and subform

[slowuptake]

slowly feeling my way forward as usual ....

I have a main form that contains 3 unbound combo boxes allowing user to
choose employee, month and year (of monthly timesheets). Event code in a
button on main form then collects the value of the timesheet ID (primary key
of table linked to main form). However at this point the event code simply
stores the value of the timesheet ID in a local variable and doesn't do
anything with it.

I drug a subform into the main form, and manually linked the master and
child fields (using the field name of the main and sub forms respectively).
The primary key of the main form is a field in the table underlying the
subform.

What I want to achieve is:
1) use the local variable from the button event to restrict/filter the data
in the main form to the unique timesheet ID corresponding to the variable, or
simply set the form so that it is at the correct record
2) display all the records in the subform that have the same value of
timesheet ID as in the restricted/filtered main form.

Do I accomplish this by adding to the button event code? Or add code
elsewhere?

Do I sort/filter the table underlying the main form to result in ony one
record? Or do I use something like GoToRecord?

Will filtering or going to correct record in main form allow the subform to
sort by itself, or will it need more instruction?

regards,
slowuptake

 

[Wolfgang Kais]

Hello "slowuptake"

I have a main form that contains 3 unbound combo boxes allowing user
to choose employee, month and year (of monthly timesheets). Event code
in a button on main form then collects the value of the timesheet ID
(primary key of table linked to main form). However at this point the
event code simply stores the value of the timesheet ID in a local
variable and doesn't do anything with it.

What data type has this timesheet ID?

I drug a subform into the main form, and manually linked the maste
and child fields (using the field name of the main and sub forms
respectively). The primary key of the main form is a field in the table
underlying the subform.

You also yould have used the wizard for that.

What I want to achieve is:
1) use the local variable from the button event to restrict/filter the
data in the main form to the unique timesheet ID corresponding to the
variable, or simply set the form so that it is at the correct record
2) display all the records in the subform that have the same value of
timesheet ID as in the restricted/filtered main form.

As you have linked the subform correctly, 2) will be done automatically.

Do I accomplish this by adding to the button event code? Or add
code elsewhere?

It's ok in the button's event code.

Do I sort/filter the table underlying the main form to result in only
one record? Or do I use something like GoToRecord?

You don't have to sort/filter. You can use a Clone of the form's recordset
to find the record and then "goto" the record found using a bookmark.

Will filtering or going to correct record in main form allow the
subform to sort by itself, or will it need more instruction?

Filtering (not sorting) in the subform will be done automatically,
that's what makes them useful.
Finally a little code for the button, assuming the variable is a string:
With Me.RecordsetClone
.FindFirst "[TimesheetID] = '" & strVariable & "'"
If .NoMatch Then
MsgBox "Timeheet not found!", vbExclamation
Else
Me.Bookmark = .Bookmark
End If
End With

 

[slowuptake]

Wolfgang,

Thanks for the code.
Unfortunately you guessed wrong, the variable is a long integer not a string.

I tried to modify the code you sent, but my knowledge of syntax too weak.

Here is the entire bit of button event code as it now stands. When I run
it, it evaluates InvMyVar = 6 (which is correct), but it evaluates
idnTimeCardID = 1 (the first record I presume), and returns "NoMatch".

******
Private Sub cmdFindTimeSheet_Click()
On Error GoTo Err_cmdFindTimeSheet_Click

Dim IngMyVar As Long

'Collect employee, month and year of timecard desired.
'Return the primary key number (idnTimeCardID) in variable IngMyVar.

IngMyVar = Nz(DLookup("idnTimeCardID", "tblTimeCardList", _
"chrEmployee=" & Chr$(34) & cboEmployee & Chr$(34) & " AND " & _
"chrMonth=" & Chr$(34) & cboMonth & Chr$(34) & _
" AND sngYear=" & cboYear), 0)


'Set current record in tblTimeCardList equal to IngMyVar

With Me.RecordsetClone
.FindFirst "[idnTimeCardID] = '" & IngMyVar & "'"
If .NoMatch Then
MsgBox "Timeheet not found!", vbExclamation
Else
Me.Bookmark = .Bookmark
End If
End With

Exit_cmdFindTimeSheet_Click:
Exit Sub

Err_cmdFindTimeSheet_Click:
MsgBox Err.Description
Resume Exit_cmdFindTimeSheet_Click

End Sub

******

Could you please give a lesson on the correct syntax in the FindFirst?

regards,
Slowuptake

Hello "slowuptake"

I have a main form that contains 3 unbound combo boxes allowing user
to choose employee, month and year (of monthly timesheets). Event code
in a button on main form then collects the value of the timesheet ID
(primary key of table linked to main form). However at this point the
event code simply stores the value of the timesheet ID in a local
variable and doesn't do anything with it.

What data type has this timesheet ID?

I drug a subform into the main form, and manually linked the maste
and child fields (using the field name of the main and sub forms
respectively). The primary key of the main form is a field in the table
underlying the subform.

You also yould have used the wizard for that.

What I want to achieve is:
1) use the local variable from the button event to restrict/filter the
data in the main form to the unique timesheet ID corresponding to the
variable, or simply set the form so that it is at the correct record
2) display all the records in the subform that have the same value of
timesheet ID as in the restricted/filtered main form.

As you have linked the subform correctly, 2) will be done automatically.

Do I accomplish this by adding to the button event code? Or add
code elsewhere?

It's ok in the button's event code.

Do I sort/filter the table underlying the main form to result in only
one record? Or do I use something like GoToRecord?

You don't have to sort/filter. You can use a Clone of the form's recordset
to find the record and then "goto" the record found using a bookmark.

Will filtering or going to correct record in main form allow the
subform to sort by itself, or will it need more instruction?

Filtering (not sorting) in the subform will be done automatically,
that's what makes them useful.
Finally a little code for the button, assuming the variable is a string:
With Me.RecordsetClone
.FindFirst "[TimesheetID] = '" & strVariable & "'"
If .NoMatch Then
MsgBox "Timeheet not found!", vbExclamation
Else
Me.Bookmark = .Bookmark
End If
End With

 

[Wolfgang Kais]

Hello "slowuptake".

Thanks for the code.
Unfortunately you guessed wrong, the variable is a long integer not a
string. I tried to modify the code you sent, but my knowledge of syntax
too weak. [snip]
.FindFirst "[idnTimeCardID] = '" & IngMyVar & "'" [snip]
Could you please give a lesson on the correct syntax in the FindFirst?

.FindFirst "[idnTimeCardID] = " & IngMyVar