HELP! Subform record select, display,requery

[sea]

I have two problems I'm hoping someone can help with.

I have a form for Employees, and when one employee is
selected, a subform displays all that employee's charges.
Works fine.

I need to be able to have the user select ONE charge from
the list (could be hundreds) and be able to edit it.

Problem 1:
I have "Change Record" button, but when the user clicks
the button, I can only get the FIRST record (most recent
date) to appear on the form for editing.
I've tried variations of this code, but I get either the
first record or no records.
I would love to have the user double-click on the record
in the subform for editing, but I just can't figure out
how.

Private Sub cmdChangeOSRecord_Click()
Dim stLinkCriteria As String
Dim Cashier As Long
Dim SALEDATE As Date
Dim STNum As Long
Dim VarType As String

Cashier = Me.SF_CashierOS.Form!sfCashier
SALEDATE = Me.SF_CashierOS.Form!sfSaleDate
STNum = Me.SF_CashierOS.Form!sfStNum
VarType = Me.SF_CashierOS.Form!sfVarType

' Trying to get only ONE record selected - not working get
none or error w/this
' (Type mismatch when Vartype added to end - 1 byte char
field)
' No matter what I do, when it works (Cashier only) I get
the first record in the sub form - even when I've had
focus on another on the subform
' stLinkCriteria = "[Cashier]=" & Str(Cashier) & " AND
[SaleDate]=" & Str(SALEDATE) _
' & " AND [StNum]=" & Str(STNum)

stLinkCriteria = "[Cashier]=" & Str(Cashier)

DoCmd.OpenForm "F_UpdateOSRecord", , , stLinkCriteria

End Sub

Problem 2:
Once the user edits a record (the first record or scroll
through the edit form for another) I have the SAVE button
close the editing form and return to the original
form/subform. I think I should do a REQUERY to refresh
the subform, to show the changed record(s), but I can't
get that to work either.

Private Sub cmdSaveOSRecord_Click()
On Error GoTo Err_cmdSaveOSRecord_Click

' Save the record when the user presses "SAVE" and Close
the form - with Requery? (not working, but needs it)
DoCmd.DoMenuItem acFormBar, acRecordsMenu,
acSaveRecord, , acMenuVer70
DoCmd.Close

' If F_Cashier form is loaded,
' select it, requery cboFindbyNum combo box.

Dim ctl As Control

If IsLoaded("F_Cashier") Then
Set ctl = Forms![F_Cashier]![cboFindbyNum]
DoCmd.SelectObject acForm, "F_Cashier"
ctl.Requery

End If

Exit_cmdSaveOSRecord_Click:
Exit Sub

Err_cmdSaveOSRecord_Click:
MsgBox Err.Description
Resume Exit_cmdSaveOSRecord_Click

End Sub

Very stuck - weeks on hold now and desperate!

Many thanks. sea

 

[Marshall Barton]

I have two problems I'm hoping someone can help with.

I have a form for Employees, and when one employee is
selected, a subform displays all that employee's charges.
Works fine.

I need to be able to have the user select ONE charge from
the list (could be hundreds) and be able to edit it.

Problem 1:
I have "Change Record" button, but when the user clicks
the button, I can only get the FIRST record (most recent
date) to appear on the form for editing.
I've tried variations of this code, but I get either the
first record or no records.
I would love to have the user double-click on the record
in the subform for editing, but I just can't figure out
how.

Private Sub cmdChangeOSRecord_Click()
Dim stLinkCriteria As String
Dim Cashier As Long
Dim SALEDATE As Date
Dim STNum As Long
Dim VarType As String

Cashier = Me.SF_CashierOS.Form!sfCashier
SALEDATE = Me.SF_CashierOS.Form!sfSaleDate
STNum = Me.SF_CashierOS.Form!sfStNum
VarType = Me.SF_CashierOS.Form!sfVarType

' Trying to get only ONE record selected - not working get
none or error w/this
' (Type mismatch when Vartype added to end - 1 byte char
field)
' No matter what I do, when it works (Cashier only) I get
the first record in the sub form - even when I've had
focus on another on the subform
' stLinkCriteria = "[Cashier]=" & Str(Cashier) & " AND
[SaleDate]=" & Str(SALEDATE) _
' & " AND [StNum]=" & Str(STNum)

The string that results from the above expression must have
the appropriate delimiters around the values. Text fields
require quotes (either single or double) and date fields
need to be enclosed in # signs and must be in an unambiguous
US format. I'm just guessing at your field types, but
here's an example:

stLinkCriteria = "[Cashier]=" & Cashier & _
" AND [SaleDate]=" & Format(SALEDATE, "\#m\/d\/yyyy\#") _
& " AND [StNum]=" & Str(STNum) _
& " AND [VarType] = """ VarType & """"

To make it easy to see the result of a comlicated expression
like this, temporarily add a line of code to display it:

MsgBox stLinkCriteria


Since the record has many controls on it and you don't know
where a users might double click, you'll have to use the
double click event of the form section and each control to
catch all the possibilities.

Problem 2:
Once the user edits a record (the first record or scroll
through the edit form for another) I have the SAVE button
close the editing form and return to the original
form/subform. I think I should do a REQUERY to refresh
the subform, to show the changed record(s), but I can't
get that to work either.

Private Sub cmdSaveOSRecord_Click()
On Error GoTo Err_cmdSaveOSRecord_Click

' Save the record when the user presses "SAVE" and Close
the form - with Requery? (not working, but needs it)
DoCmd.DoMenuItem acFormBar, acRecordsMenu,
acSaveRecord, , acMenuVer70
DoCmd.Close

' If F_Cashier form is loaded,
' select it, requery cboFindbyNum combo box.

Dim ctl As Control

If IsLoaded("F_Cashier") Then
Set ctl = Forms![F_Cashier]![cboFindbyNum]
DoCmd.SelectObject acForm, "F_Cashier"
ctl.Requery

End If

Instead of using the archaric DoMenuItem, use something more
specific or, at least, use the RunCommand method.

'Save the record
If Me.Dirty Then Me.Dirty = False

'Requery a combo box on the Cashier form
Forms![F_Cashier]![cboFindbyNum].Requery

'Requery a subform on the Cashier form
Forms![F_Cashier]!subformcontrol.Form.Requery

'Close this form
DoCmd.Close acForm, Me.Name, acSaveNo