Combo Box and requery

  • Thread starter Thread starter sg
  • Start date Start date
S

sg

Hi,

I have a form that lookup by account# (txtAccount) then get values linked
with this account in shipto combox (comboshp). I like to get the record
after user choosing shipto. how would I do this in after update.
the rest of data of this record in one of my query which includes
account#,shipto#,sales.......
How can I limit this records by using two values in after update.

lI'm a beginner, stuck here and so frustrated.

Please help and appreciate.
Sarah
 
Create a query that will return the desired data. In the criterion box under
the account# field, put this:
Forms!FormName!txtAccount

In the criterion box under the shipto# field, put this:
Forms!FormName!comboshp

Note that, for both expressions above, FormName is to be replaced by the
name of the form that you're using to "filter" the query.

Save the query.

Now base your FormName form on this query (set the form's Recordsource to
this query name). In the AfterUpdate event of the comboshp control, use code
that requeries the form:

Private Sub comboshp_AfterUpdate()
Me.Requery
End Sub
 
Ken,

Thanks for your quick response. I did as you said, in
comboShipto_afterupdate()
is that possible to have two values (account and shipto) in
RecordsetClone.FindFirst?

If not, how can I save current account then use shipto.

thanks,
Sarah

Me.RecordsetClone.FindFirst "[Shipto] =
Me.Bookmark = Me.RecordsetClone.Bookmark

The problem is that I like to use account and shipto together. but the
 
My post did not use RecordsetClone and its FindFirst method.

Nonetheless, if both ship and account are numeric fields:

Me.RecordsetClone.FindFirst "[Shipto] = " & Me.comboshp.Value & _
" And [Account]=" & Me.txtAccount.Value
Me.Bookmark = Me.RecordsetClone.Bookmark

--

Ken Snell
<MS ACCESS MVP>

sg said:
Ken,

Thanks for your quick response. I did as you said, in
comboShipto_afterupdate()
is that possible to have two values (account and shipto) in
RecordsetClone.FindFirst?

If not, how can I save current account then use shipto.

thanks,
Sarah

Me.RecordsetClone.FindFirst "[Shipto] =
Me.Bookmark = Me.RecordsetClone.Bookmark

The problem is that I like to use account and shipto together. but the
Ken Snell said:
Create a query that will return the desired data. In the criterion box
under the account# field, put this:
Forms!FormName!txtAccount

In the criterion box under the shipto# field, put this:
Forms!FormName!comboshp

Note that, for both expressions above, FormName is to be replaced by the
name of the form that you're using to "filter" the query.

Save the query.

Now base your FormName form on this query (set the form's Recordsource to
this query name). In the AfterUpdate event of the comboshp control, use
code that requeries the form:

Private Sub comboshp_AfterUpdate()
Me.Requery
End Sub
Click to expand...
Click to expand...
 
Ken,

Thanks for your response. I used your method first then I tried mine which
uses RecordsetClone, I like to get it work in either way, so I asked you the
question.
Really appreciate your help,
Sarah
Ken Snell said:
My post did not use RecordsetClone and its FindFirst method.

Nonetheless, if both ship and account are numeric fields:

Me.RecordsetClone.FindFirst "[Shipto] = " & Me.comboshp.Value & _
" And [Account]=" & Me.txtAccount.Value
Me.Bookmark = Me.RecordsetClone.Bookmark

--

Ken Snell
<MS ACCESS MVP>

sg said:
Ken,

Thanks for your quick response. I did as you said, in
comboShipto_afterupdate()
is that possible to have two values (account and shipto) in
RecordsetClone.FindFirst?

If not, how can I save current account then use shipto.

thanks,
Sarah

Me.RecordsetClone.FindFirst "[Shipto] =
Me.Bookmark = Me.RecordsetClone.Bookmark

The problem is that I like to use account and shipto together. but the
Ken Snell said:
Create a query that will return the desired data. In the criterion box
under the account# field, put this:
Forms!FormName!txtAccount

In the criterion box under the shipto# field, put this:
Forms!FormName!comboshp

Note that, for both expressions above, FormName is to be replaced by the
name of the form that you're using to "filter" the query.

Save the query.

Now base your FormName form on this query (set the form's Recordsource
to this query name). In the AfterUpdate event of the comboshp control,
use code that requeries the form:

Private Sub comboshp_AfterUpdate()
Me.Requery
End Sub
--

Ken Snell
<MS ACCESS MVP>



Hi,

I have a form that lookup by account# (txtAccount) then get values
linked with this account in shipto combox (comboshp). I like to get the
record after user choosing shipto. how would I do this in after update.
the rest of data of this record in one of my query which includes
account#,shipto#,sales.......
How can I limit this records by using two values in after update.

lI'm a beginner, stuck here and so frustrated.

Please help and appreciate.
Sarah
Click to expand...
Click to expand...
Click to expand...
 
Good luck!

--

Ken Snell
<MS ACCESS MVP>

sg said:
Ken,

Thanks for your response. I used your method first then I tried mine which
uses RecordsetClone, I like to get it work in either way, so I asked you
the question.
Really appreciate your help,
Sarah
Ken Snell said:
My post did not use RecordsetClone and its FindFirst method.

Nonetheless, if both ship and account are numeric fields:

Me.RecordsetClone.FindFirst "[Shipto] = " & Me.comboshp.Value & _
" And [Account]=" & Me.txtAccount.Value
Me.Bookmark = Me.RecordsetClone.Bookmark

--

Ken Snell
<MS ACCESS MVP>

sg said:
Ken,

Thanks for your quick response. I did as you said, in
comboShipto_afterupdate()
is that possible to have two values (account and shipto) in
RecordsetClone.FindFirst?

If not, how can I save current account then use shipto.

thanks,
Sarah

Me.RecordsetClone.FindFirst "[Shipto] =
Me.Bookmark = Me.RecordsetClone.Bookmark

The problem is that I like to use account and shipto together. but the
Create a query that will return the desired data. In the criterion box
under the account# field, put this:
Forms!FormName!txtAccount

In the criterion box under the shipto# field, put this:
Forms!FormName!comboshp

Note that, for both expressions above, FormName is to be replaced by
the name of the form that you're using to "filter" the query.

Save the query.

Now base your FormName form on this query (set the form's Recordsource
to this query name). In the AfterUpdate event of the comboshp control,
use code that requeries the form:

Private Sub comboshp_AfterUpdate()
Me.Requery
End Sub
--

Ken Snell
<MS ACCESS MVP>



Hi,

I have a form that lookup by account# (txtAccount) then get values
linked with this account in shipto combox (comboshp). I like to get
the record after user choosing shipto. how would I do this in after
update.
the rest of data of this record in one of my query which includes
account#,shipto#,sales.......
How can I limit this records by using two values in after update.

lI'm a beginner, stuck here and so frustrated.

Please help and appreciate.
Sarah
Click to expand...
Click to expand...
Click to expand...
 
Back
Top