Populate control using the parameter from a query

[GA]

I'm sure this is straightforward but I just can't get it to work so
I'd appreciate some help.

I have a form which is based on a parameter query to restrict the
displayed records to a single member of staff. It works fine but when
I createa new record with the form, I want to populate the "staff"
control with the value used as a parameter using "if
Me.NewRecord=True".

I thought that I'd be able to simply refer to [Parameter Name] but
that doesn't work. How would I do this?

Thanks GA

 

[Klatuu]

To answer you question, I need to know a couple of things.
Is this done in the form, or are you opening the form to the exiting staff
member? If you are opening to an existing record, please post the code where
you do that.
If you are doing it all within the form, describe how you do that and how
you change to a different staff member, please.

 

[GA]

Thanks for the response.

I have my form based on a parameter query which is -
SELECT tbl_entries.date, tbl_entries.staff, tbl_entries.working
FROM tbl_entries
WHERE (((tbl_entries.staff)=[Service Initials]))
ORDER BY tbl_entries.date;

The form opens to show all existing records which are restricted to a
single member by virtue of coming from the query.

I thought that in the on current event of the form I could do
something like -
if Me.NewRecord=true then Me.staff = [Service Initials]
but it doesn't work.

I don't have the option to change to a different member of staff other
than by closing and re-opening the form.
GA

 

[Klatuu]

That's helpful, but you missed an important part.
Please post the code where you open the form. I need to know how it knows
which staff member to show.
Is [Service Initials] a control on the form?
--
Dave Hargis, Microsoft Access MVP

Thanks for the response.

I have my form based on a parameter query which is -
SELECT tbl_entries.date, tbl_entries.staff, tbl_entries.working
FROM tbl_entries
WHERE (((tbl_entries.staff)=[Service Initials]))
ORDER BY tbl_entries.date;

The form opens to show all existing records which are restricted to a
single member by virtue of coming from the query.

I thought that in the on current event of the form I could do
something like -
if Me.NewRecord=true then Me.staff = [Service Initials]
but it doesn't work.

I don't have the option to change to a different member of staff other
than by closing and re-opening the form.
GA

To answer you question, I need to know a couple of things.
Is this done in the form, or are you opening the form to the exiting staff
member? If you are opening to an existing record, please post the code where
you do that.
If you are doing it all within the form, describe how you do that and how
you change to a different staff member, please.

 

[GA]

The control on the form is "staff".

[Service Initials] is the parameter that the query 'asks' for.

The form is opened with a button on a menu form in which I haven't set
any criteria -
Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_entry_for_one_member"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.Description
Resume Exit_Command4_Click

End Sub

GA

That's helpful, but you missed an important part.
Please post the code where you open the form. I need to know how it knows
which staff member to show.
Is [Service Initials] a control on the form?
--
Dave Hargis, Microsoft Access MVP

Thanks for the response.

I have my form based on a parameter query which is -
SELECT tbl_entries.date, tbl_entries.staff, tbl_entries.working
FROM tbl_entries
WHERE (((tbl_entries.staff)=[Service Initials]))
ORDER BY tbl_entries.date;

The form opens to show all existing records which are restricted to a
single member by virtue of coming from the query.

I thought that in the on current event of the form I could do
something like -
if Me.NewRecord=true then Me.staff = [Service Initials]
but it doesn't work.

 

[Klatuu]

Okay, thanks. That is what we need.
Here is what I would suggest.
Take the Where Clause out of your query.
Instead of opening the form from the menu, create a new form and have the
menu open the new form. It will open your existing form. Put a text box on
the new form for the user to enter the initials. It would be a good idea to
check to make sure the intitials are actually in the table before opening the
form. You can do that in the After Update event of the text box.

If IsNull(DLookup("[staff]","tbl_entries]","[staff] = """ &
Me.txtInitials & """") Then
MsgBox "Select A Valid Staff Memember or " & vbNewLine & _
"Leave Blank to add a new member", vbInformation, _
"Staff Member Not Found"
End If

Also on the form, add a command button to open the form to the selected
member or a blank for to allow a new member to be added and use the Click
event:

If Me.txtInitials = Null & vbNullstring Then
DoCmd.OpenForm "frm_entry_for_one_member", , , , acFormAdd
Else
DoCmd.OpenForm "frm_entry_for_one_member", , , [staff] =
Me.txtInitials, acFormEdit
End If
Docmd.Close acForm, Me.Name, acSaveNo

That way, it will open to the valid staff member if it exist and open a
blank for to enter a new member if not.
--
Dave Hargis, Microsoft Access MVP

The control on the form is "staff".

[Service Initials] is the parameter that the query 'asks' for.

The form is opened with a button on a menu form in which I haven't set
any criteria -
Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_entry_for_one_member"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.Description
Resume Exit_Command4_Click

End Sub

GA

That's helpful, but you missed an important part.
Please post the code where you open the form. I need to know how it knows
which staff member to show.
Is [Service Initials] a control on the form?
--
Dave Hargis, Microsoft Access MVP

Thanks for the response.

I have my form based on a parameter query which is -
SELECT tbl_entries.date, tbl_entries.staff, tbl_entries.working
FROM tbl_entries
WHERE (((tbl_entries.staff)=[Service Initials]))
ORDER BY tbl_entries.date;

The form opens to show all existing records which are restricted to a
single member by virtue of coming from the query.

I thought that in the on current event of the form I could do
something like -
if Me.NewRecord=true then Me.staff = [Service Initials]
but it doesn't work.

 

[GA]

Thanks for that. The reason I tried this is that it's a method I often
use with reports to populate an unbound control.

It's time for bed now so I'll try your solution out tomorrow. Thanks
again.
GA

 

[GA]

Amazing what a few hours sleep does for you. I missed the blindingly
obvious solution which was to use a form/sub form method. I don't
think I made it clear that I have a separate table which contains a
pick list of staff which provided items for a combobox on my first
form. Using that as the source for the 'parent' form made it easy to
display only related items in the sub form which is what I needed to
do.
GA

Thanks for that. The reason I tried this is that it's a method I often
use with reports to populate an unbound control.

It's time for bed now so I'll try your solution out tomorrow. Thanks
again.
GA

Okay, thanks. That is what we need.
Here is what I would suggest.
Take the Where Clause out of your query.
Instead of opening the form from the menu, create a new form and have the
menu open the new form. It will open your existing form. Put a text box on
the new form for the user to enter the initials. It would be a good idea to
check to make sure the intitials are actually in the table before opening the
form. You can do that in the After Update event of the text box.

If IsNull(DLookup("[staff]","tbl_entries]","[staff] = """ &
Me.txtInitials & """") Then
MsgBox "Select A Valid Staff Memember or " & vbNewLine & _
"Leave Blank to add a new member", vbInformation, _
"Staff Member Not Found"
End If

Also on the form, add a command button to open the form to the selected
member or a blank for to allow a new member to be added and use the Click
event:

If Me.txtInitials = Null & vbNullstring Then
DoCmd.OpenForm "frm_entry_for_one_member", , , , acFormAdd
Else
DoCmd.OpenForm "frm_entry_for_one_member", , , [staff] =
Me.txtInitials, acFormEdit
End If
Docmd.Close acForm, Me.Name, acSaveNo

That way, it will open to the valid staff member if it exist and open a
blank for to enter a new member if not.