requery

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table - Work Orders - which includes fields for a service person and
hours worked on the order. I have another table which contains the hourly
rate for the service person. I have a query which joins these fields -
service person, hours worked and rate, which also then computes various costs
and allocations. My problem is that I can not get the query to autofill the
rate when the service person is selected. It will update only after the
query is closed and then re-opened. I have tried refresh and requery. I can
only get an instant refresh in a form which displays only one work order at a
time - the user wants to be able to update data for a number of work orders
at a time in a datasheet view. I'm sure this can be done - I just can't do
it. I do not write code. All help will be gratefully accepted.--
TIA
Bibi
 
In the form where your Work Order details are entered, you probably have a
combo for selecting the service person. Use the AfterUpdate event procedure
of this combo to lookup the person's hourly rate, and drop it into the
HourlyRate text box in this form.

Something like this:

Private Sub ServicePersonID_AfterUpdate
Dim strWhere As String
If Not IsNull(Me.ServicePersonID) Then
strWhere = "[ServicePersonID] = " & Me.ServicePersonID
Me.[HourlyRate] = DLookup("HourlyRate", "ServicePersonTable",
strWhere)
End If
End Sub

Note that if the ServicePersonID is a Text field (not a Number field), you
need extra quotes:
strWhere = "[ServicePersonID] = """ & Me.ServicePersonID & """"

For more help, with DLookup(), see:
http://allenbrowne.com/casu-07.html

For a similar example, open the Northwind sample database, and open the
Order Details Subform in design view. The ProductID combo looks up the
current price from the Products table.
 
I am feeling really inadequate - I think understand but I am not getting the
result I need - the form is clearly being updated but has lost the link to
the rate and is coming up blank
This is what I wrote

Private Sub Service_Person_AfterUpdate()
Dim strWhere As String
If Not IsNull(Me.Service_Person) Then
strWhere = "[Service_Person] = '" & Me.Service_Person & "'"
Me.[Rate] = DLookup("Rate", "employee", strWhere)
End If
End Sub
Employee is the name of the table, the id I am using is also called employee
(unfortunately) - it is text
I appreciate all help and realize the problem I am having lies not in the
advice but the ears it is falling upon.
thank you
--
TIA
Bibi


Allen Browne said:
In the form where your Work Order details are entered, you probably have a
combo for selecting the service person. Use the AfterUpdate event procedure
of this combo to lookup the person's hourly rate, and drop it into the
HourlyRate text box in this form.

Something like this:

Private Sub ServicePersonID_AfterUpdate
Dim strWhere As String
If Not IsNull(Me.ServicePersonID) Then
strWhere = "[ServicePersonID] = " & Me.ServicePersonID
Me.[HourlyRate] = DLookup("HourlyRate", "ServicePersonTable",
strWhere)
End If
End Sub

Note that if the ServicePersonID is a Text field (not a Number field), you
need extra quotes:
strWhere = "[ServicePersonID] = """ & Me.ServicePersonID & """"

For more help, with DLookup(), see:
http://allenbrowne.com/casu-07.html

For a similar example, open the Northwind sample database, and open the
Order Details Subform in design view. The ProductID combo looks up the
current price from the Products table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Bibi said:
I have a table - Work Orders - which includes fields for a service person
and
hours worked on the order. I have another table which contains the hourly
rate for the service person. I have a query which joins these fields -
service person, hours worked and rate, which also then computes various
costs
and allocations. My problem is that I can not get the query to autofill
the
rate when the service person is selected. It will update only after the
query is closed and then re-opened. I have tried refresh and requery. I
can
only get an instant refresh in a form which displays only one work order
at a
time - the user wants to be able to update data for a number of work
orders
at a time in a datasheet view. I'm sure this can be done - I just can't
do
it. I do not write code. All help will be gratefully accepted.--
TIA
Bibi
Click to expand...
Click to expand...
 
Bibi, what you have looks right. To debug it, press F9 on the line that
starts:
Me.[Rate] = ...
to make a break point. When you run the code (by using the form to select a
service person), it should pop up the VBA window, and highlight that line.
You can now open the Immediate Window (Ctrl+G) and use it to debug what's
going on, e.g.:
? strWhere
? DLookup("Rate", "employee", strWhere)

Hopefully that will help identify what's going on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Bibi said:
I am feeling really inadequate - I think understand but I am not getting
the
result I need - the form is clearly being updated but has lost the link to
the rate and is coming up blank
This is what I wrote

Private Sub Service_Person_AfterUpdate()
Dim strWhere As String
If Not IsNull(Me.Service_Person) Then
strWhere = "[Service_Person] = '" & Me.Service_Person & "'"
Me.[Rate] = DLookup("Rate", "employee", strWhere)
End If
End Sub
Employee is the name of the table, the id I am using is also called
employee
(unfortunately) - it is text
I appreciate all help and realize the problem I am having lies not in the
advice but the ears it is falling upon.
thank you
--
TIA
Bibi


Allen Browne said:
In the form where your Work Order details are entered, you probably have
a
combo for selecting the service person. Use the AfterUpdate event
procedure
of this combo to lookup the person's hourly rate, and drop it into the
HourlyRate text box in this form.

Something like this:

Private Sub ServicePersonID_AfterUpdate
Dim strWhere As String
If Not IsNull(Me.ServicePersonID) Then
strWhere = "[ServicePersonID] = " & Me.ServicePersonID
Me.[HourlyRate] = DLookup("HourlyRate", "ServicePersonTable",
strWhere)
End If
End Sub

Note that if the ServicePersonID is a Text field (not a Number field),
you
need extra quotes:
strWhere = "[ServicePersonID] = """ & Me.ServicePersonID & """"

For more help, with DLookup(), see:
http://allenbrowne.com/casu-07.html

For a similar example, open the Northwind sample database, and open the
Order Details Subform in design view. The ProductID combo looks up the
current price from the Products table.

Bibi said:
I have a table - Work Orders - which includes fields for a service
person
and
hours worked on the order. I have another table which contains the
hourly
rate for the service person. I have a query which joins these fields -
service person, hours worked and rate, which also then computes various
costs
and allocations. My problem is that I can not get the query to
autofill
the
rate when the service person is selected. It will update only after
the
query is closed and then re-opened. I have tried refresh and requery.
I
can
only get an instant refresh in a form which displays only one work
order
at a
time - the user wants to be able to update data for a number of work
orders
at a time in a datasheet view. I'm sure this can be done - I just
can't
do
it. I do not write code. All help will be gratefully accepted.--
TIA
Bibi
Click to expand...
Click to expand...
Click to expand...
 
thank you for your response -
I am really lost - I can not write code and do not know how to de-bug. I
can however describe what is happening better than I did - an employee
selection changes the rate in the Employee table instead of the work order
table. I figure I should be able to determine why but can't. If you could
look at it one more time I would appreciae it - I grow desperate.
--
TIA
Bibi


Allen Browne said:
Bibi, what you have looks right. To debug it, press F9 on the line that
starts:
Me.[Rate] = ...
to make a break point. When you run the code (by using the form to select a
service person), it should pop up the VBA window, and highlight that line.
You can now open the Immediate Window (Ctrl+G) and use it to debug what's
going on, e.g.:
? strWhere
? DLookup("Rate", "employee", strWhere)

Hopefully that will help identify what's going on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Bibi said:
I am feeling really inadequate - I think understand but I am not getting
the
result I need - the form is clearly being updated but has lost the link to
the rate and is coming up blank
This is what I wrote

Private Sub Service_Person_AfterUpdate()
Dim strWhere As String
If Not IsNull(Me.Service_Person) Then
strWhere = "[Service_Person] = '" & Me.Service_Person & "'"
Me.[Rate] = DLookup("Rate", "employee", strWhere)
End If
End Sub
Employee is the name of the table, the id I am using is also called
employee
(unfortunately) - it is text
I appreciate all help and realize the problem I am having lies not in the
advice but the ears it is falling upon.
thank you
--
TIA
Bibi


Allen Browne said:
In the form where your Work Order details are entered, you probably have
a
combo for selecting the service person. Use the AfterUpdate event
procedure
of this combo to lookup the person's hourly rate, and drop it into the
HourlyRate text box in this form.

Something like this:

Private Sub ServicePersonID_AfterUpdate
Dim strWhere As String
If Not IsNull(Me.ServicePersonID) Then
strWhere = "[ServicePersonID] = " & Me.ServicePersonID
Me.[HourlyRate] = DLookup("HourlyRate", "ServicePersonTable",
strWhere)
End If
End Sub

Note that if the ServicePersonID is a Text field (not a Number field),
you
need extra quotes:
strWhere = "[ServicePersonID] = """ & Me.ServicePersonID & """"

For more help, with DLookup(), see:
http://allenbrowne.com/casu-07.html

For a similar example, open the Northwind sample database, and open the
Order Details Subform in design view. The ProductID combo looks up the
current price from the Products table.

I have a table - Work Orders - which includes fields for a service
person
and
hours worked on the order. I have another table which contains the
hourly
rate for the service person. I have a query which joins these fields -
service person, hours worked and rate, which also then computes various
costs
and allocations. My problem is that I can not get the query to
autofill
the
rate when the service person is selected. It will update only after
the
query is closed and then re-opened. I have tried refresh and requery.
I
can
only get an instant refresh in a form which displays only one work
order
at a
time - the user wants to be able to update data for a number of work
orders
at a time in a datasheet view. I'm sure this can be done - I just
can't
do
it. I do not write code. All help will be gratefully accepted.--
TIA
Bibi
Click to expand...
Click to expand...
Click to expand...
 
Okay, let's back up.

Is this happening in a form?
Is the form bound to a query?
Does the query contain both tables?
Which table does your query's (or form's) Rate field come from?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Bibi said:
thank you for your response -
I am really lost - I can not write code and do not know how to de-bug. I
can however describe what is happening better than I did - an employee
selection changes the rate in the Employee table instead of the work order
table. I figure I should be able to determine why but can't. If you
could
look at it one more time I would appreciae it - I grow desperate.
--
TIA
Bibi


Allen Browne said:
Bibi, what you have looks right. To debug it, press F9 on the line that
starts:
Me.[Rate] = ...
to make a break point. When you run the code (by using the form to select
a
service person), it should pop up the VBA window, and highlight that
line.
You can now open the Immediate Window (Ctrl+G) and use it to debug what's
going on, e.g.:
? strWhere
? DLookup("Rate", "employee", strWhere)

Hopefully that will help identify what's going on.

Bibi said:
I am feeling really inadequate - I think understand but I am not
getting
the
result I need - the form is clearly being updated but has lost the link
to
the rate and is coming up blank
This is what I wrote

Private Sub Service_Person_AfterUpdate()
Dim strWhere As String
If Not IsNull(Me.Service_Person) Then
strWhere = "[Service_Person] = '" & Me.Service_Person & "'"
Me.[Rate] = DLookup("Rate", "employee", strWhere)
End If
End Sub
Employee is the name of the table, the id I am using is also called
employee
(unfortunately) - it is text
I appreciate all help and realize the problem I am having lies not in
the
advice but the ears it is falling upon.
thank you
--
TIA
Bibi


:

In the form where your Work Order details are entered, you probably
have
a
combo for selecting the service person. Use the AfterUpdate event
procedure
of this combo to lookup the person's hourly rate, and drop it into the
HourlyRate text box in this form.

Something like this:

Private Sub ServicePersonID_AfterUpdate
Dim strWhere As String
If Not IsNull(Me.ServicePersonID) Then
strWhere = "[ServicePersonID] = " & Me.ServicePersonID
Me.[HourlyRate] = DLookup("HourlyRate",
"ServicePersonTable",
strWhere)
End If
End Sub

Note that if the ServicePersonID is a Text field (not a Number field),
you
need extra quotes:
strWhere = "[ServicePersonID] = """ & Me.ServicePersonID & """"

For more help, with DLookup(), see:
http://allenbrowne.com/casu-07.html

For a similar example, open the Northwind sample database, and open
the
Order Details Subform in design view. The ProductID combo looks up the
current price from the Products table.

I have a table - Work Orders - which includes fields for a service
person
and
hours worked on the order. I have another table which contains the
hourly
rate for the service person. I have a query which joins these
fields -
service person, hours worked and rate, which also then computes
various
costs
and allocations. My problem is that I can not get the query to
autofill
the
rate when the service person is selected. It will update only after
the
query is closed and then re-opened. I have tried refresh and
requery.
I
can
only get an instant refresh in a form which displays only one work
order
at a
time - the user wants to be able to update data for a number of work
orders
at a time in a datasheet view. I'm sure this can be done - I just
can't
do
it. I do not write code. All help will be gratefully accepted.--
TIA
Bibi
Click to expand...
Click to expand...
Click to expand...
 
Yes. it is in a form. Yes, the form is bound to a query. Yes, the query has
both tables. The rate field comes from the employee table.
??????
thank you for taking so much time with this.
--
TIA
Bibi


Allen Browne said:
Okay, let's back up.

Is this happening in a form?
Is the form bound to a query?
Does the query contain both tables?
Which table does your query's (or form's) Rate field come from?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Bibi said:
thank you for your response -
I am really lost - I can not write code and do not know how to de-bug. I
can however describe what is happening better than I did - an employee
selection changes the rate in the Employee table instead of the work order
table. I figure I should be able to determine why but can't. If you
could
look at it one more time I would appreciae it - I grow desperate.
--
TIA
Bibi


Allen Browne said:
Bibi, what you have looks right. To debug it, press F9 on the line that
starts:
Me.[Rate] = ...
to make a break point. When you run the code (by using the form to select
a
service person), it should pop up the VBA window, and highlight that
line.
You can now open the Immediate Window (Ctrl+G) and use it to debug what's
going on, e.g.:
? strWhere
? DLookup("Rate", "employee", strWhere)

Hopefully that will help identify what's going on.

I am feeling really inadequate - I think understand but I am not
getting
the
result I need - the form is clearly being updated but has lost the link
to
the rate and is coming up blank
This is what I wrote

Private Sub Service_Person_AfterUpdate()
Dim strWhere As String
If Not IsNull(Me.Service_Person) Then
strWhere = "[Service_Person] = '" & Me.Service_Person & "'"
Me.[Rate] = DLookup("Rate", "employee", strWhere)
End If
End Sub
Employee is the name of the table, the id I am using is also called
employee
(unfortunately) - it is text
I appreciate all help and realize the problem I am having lies not in
the
advice but the ears it is falling upon.
thank you
--
TIA
Bibi


:

In the form where your Work Order details are entered, you probably
have
a
combo for selecting the service person. Use the AfterUpdate event
procedure
of this combo to lookup the person's hourly rate, and drop it into the
HourlyRate text box in this form.

Something like this:

Private Sub ServicePersonID_AfterUpdate
Dim strWhere As String
If Not IsNull(Me.ServicePersonID) Then
strWhere = "[ServicePersonID] = " & Me.ServicePersonID
Me.[HourlyRate] = DLookup("HourlyRate",
"ServicePersonTable",
strWhere)
End If
End Sub

Note that if the ServicePersonID is a Text field (not a Number field),
you
need extra quotes:
strWhere = "[ServicePersonID] = """ & Me.ServicePersonID & """"

For more help, with DLookup(), see:
http://allenbrowne.com/casu-07.html

For a similar example, open the Northwind sample database, and open
the
Order Details Subform in design view. The ProductID combo looks up the
current price from the Products table.

I have a table - Work Orders - which includes fields for a service
person
and
hours worked on the order. I have another table which contains the
hourly
rate for the service person. I have a query which joins these
fields -
service person, hours worked and rate, which also then computes
various
costs
and allocations. My problem is that I can not get the query to
autofill
the
rate when the service person is selected. It will update only after
the
query is closed and then re-opened. I have tried refresh and
requery.
I
can
only get an instant refresh in a form which displays only one work
order
at a
time - the user wants to be able to update data for a number of work
orders
at a time in a datasheet view. I'm sure this can be done - I just
can't
do
it. I do not write code. All help will be gratefully accepted.--
TIA
Bibi
Click to expand...
Click to expand...
Click to expand...
 
I think you have a Rate field in both tables.

If so, you need the Rate form from your [Work Orders] table in the query, so
that it is the one that gets updated.

Change the query, so it supplies [Work Orders].Rate to the form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Bibi said:
Yes. it is in a form. Yes, the form is bound to a query. Yes, the query
has
both tables. The rate field comes from the employee table.
??????
thank you for taking so much time with this.
--
TIA
Bibi


Allen Browne said:
Okay, let's back up.

Is this happening in a form?
Is the form bound to a query?
Does the query contain both tables?
Which table does your query's (or form's) Rate field come from?

Bibi said:
thank you for your response -
I am really lost - I can not write code and do not know how to de-bug.
I
can however describe what is happening better than I did - an employee
selection changes the rate in the Employee table instead of the work
order
table. I figure I should be able to determine why but can't. If you
could
look at it one more time I would appreciae it - I grow desperate.
--
TIA
Bibi


:

Bibi, what you have looks right. To debug it, press F9 on the line
that
starts:
Me.[Rate] = ...
to make a break point. When you run the code (by using the form to
select
a
service person), it should pop up the VBA window, and highlight that
line.
You can now open the Immediate Window (Ctrl+G) and use it to debug
what's
going on, e.g.:
? strWhere
? DLookup("Rate", "employee", strWhere)

Hopefully that will help identify what's going on.

I am feeling really inadequate - I think understand but I am not
getting
the
result I need - the form is clearly being updated but has lost the
link
to
the rate and is coming up blank
This is what I wrote

Private Sub Service_Person_AfterUpdate()
Dim strWhere As String
If Not IsNull(Me.Service_Person) Then
strWhere = "[Service_Person] = '" & Me.Service_Person &
"'"
Me.[Rate] = DLookup("Rate", "employee", strWhere)
End If
End Sub
Employee is the name of the table, the id I am using is also called
employee
(unfortunately) - it is text
I appreciate all help and realize the problem I am having lies not
in
the
advice but the ears it is falling upon.
thank you
--
TIA
Bibi


:

In the form where your Work Order details are entered, you probably
have
a
combo for selecting the service person. Use the AfterUpdate event
procedure
of this combo to lookup the person's hourly rate, and drop it into
the
HourlyRate text box in this form.

Something like this:

Private Sub ServicePersonID_AfterUpdate
Dim strWhere As String
If Not IsNull(Me.ServicePersonID) Then
strWhere = "[ServicePersonID] = " & Me.ServicePersonID
Me.[HourlyRate] = DLookup("HourlyRate",
"ServicePersonTable",
strWhere)
End If
End Sub

Note that if the ServicePersonID is a Text field (not a Number
field),
you
need extra quotes:
strWhere = "[ServicePersonID] = """ & Me.ServicePersonID & """"

For more help, with DLookup(), see:
http://allenbrowne.com/casu-07.html

For a similar example, open the Northwind sample database, and open
the
Order Details Subform in design view. The ProductID combo looks up
the
current price from the Products table.

I have a table - Work Orders - which includes fields for a service
person
and
hours worked on the order. I have another table which contains
the
hourly
rate for the service person. I have a query which joins these
fields -
service person, hours worked and rate, which also then computes
various
costs
and allocations. My problem is that I can not get the query to
autofill
the
rate when the service person is selected. It will update only
after
the
query is closed and then re-opened. I have tried refresh and
requery.
I
can
only get an instant refresh in a form which displays only one
work
order
at a
time - the user wants to be able to update data for a number of
work
orders
at a time in a datasheet view. I'm sure this can be done - I
just
can't
do
it. I do not write code. All help will be gratefully accepted.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top