By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,414 Members | 2,939 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,414 IT Pros & Developers. It's quick & easy.

How to synchronize current record in the form with value selected in a combobox ?

P: n/a
There is a form (single form) and a combobox. I want that current record of
the form is adjusted according to selected value in the combobox. Cuurrent
record should be the same as the value in the combobox.
What is the solution?

Thank you in advance.
Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
This article describes how to use an unbound combo to navigate to the
selected record:
http://allenbrowne.com/ser-03.html

The article suggests setting the combo to Null after navigation, so the user
understands it serves no purpose but navigation. If you wish to keep your
unbound combo synchronized with the current record in the form, you would
need to program these events:

- the Current record of the form, so the unbound combo matches the value
from the current record;

- the Undo event of the form, so the unbound combo is restored to the
OldValue from the curent record;

- the AfterUpdate event of the control(s) in the form that matches the
field(s) you wish to synchronize with.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Zlatko Matif" <zl***********@sb.t-com.hr> wrote in message
news:d1**********@ls219.htnet.hr...
There is a form (single form) and a combobox. I want that current record
of the form is adjusted according to selected value in the combobox.
Cuurrent record should be the same as the value in the combobox.
What is the solution?

Nov 13 '05 #2

P: n/a
Hello, Allen. Thanks for answer.
In fact I have already tried something that matches what you suggest, but
still have a problem...

Let's suppose that txtAAA is textbox and cbxAAA is combobox. My aproach was
the following:

' Events on the form:

' 1. On Load event fills combobox with the first value

Private Sub Form_Load()
cbxAAA = cbxAAA.ItemData(0)
End Sub

' 2. On Current event synchronizes combobox value with current record in the
form (represented by txtAAA)

Private Sub Form_Current()

Me.cbxAAA.Requery

For i = 0 To cbxAAA.ListCount
If cbxAAA.ItemData(i) = Me.txtAAA Then
Me.cbxAAA = cbxAAA.ItemData(i)
Exit For
End If
Next i

End Sub

' On the COMBOBOX istelf I have After Update event which synchronizes
current record (txtAAA) with the value selected from the combobox.

Private Sub cbxAAA_AfterUpdate()

With CodeContextObject
DoCmd.GoToControl "txtAAA"
DoCmd.FindRecord .cbxAAA, acEntire, False, , False, acCurrent, True
End With

End Sub
This works perfectly as long as both textbox and combobox are visible. But,
my intenion was to hide textbox.
Unfortunately, when I put property txtAAA.Visible=False, the above mentioned
After Update event generates an error, because Find method requires an
object focus and hidden object can't have focus...
What is the alternative solution ? What to put in AfterUpdate event of the
combobox to avoid referencing to control object ?

Thanks.

"Allen Browne" <Al*********@SeeSig.Invalid> je napisao u poruci interesnoj
grupi:42***********************@per-qv1-newsreader-01.iinet.net.au...
This article describes how to use an unbound combo to navigate to the
selected record:
http://allenbrowne.com/ser-03.html

The article suggests setting the combo to Null after navigation, so the
user understands it serves no purpose but navigation. If you wish to keep
your unbound combo synchronized with the current record in the form, you
would need to program these events:

- the Current record of the form, so the unbound combo matches the value
from the current record;

- the Undo event of the form, so the unbound combo is restored to the
OldValue from the curent record;

- the AfterUpdate event of the control(s) in the form that matches the
field(s) you wish to synchronize with.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Zlatko Matif" <zl***********@sb.t-com.hr> wrote in message
news:d1**********@ls219.htnet.hr...
There is a form (single form) and a combobox. I want that current record
of the form is adjusted according to selected value in the combobox.
Cuurrent record should be the same as the value in the combobox.
What is the solution?


Nov 13 '05 #3

P: n/a
Take another look at the code in the article.
It uses FindFirst on the RecordsetClone of the form to find the record.
That will work even if the text box is hidden.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Zlatko Matif" <zl***********@sb.t-com.hr> wrote in message
news:d1**********@ls219.htnet.hr...
Hello, Allen. Thanks for answer.
In fact I have already tried something that matches what you suggest, but
still have a problem...

Let's suppose that txtAAA is textbox and cbxAAA is combobox. My aproach
was the following:

' Events on the form:

' 1. On Load event fills combobox with the first value

Private Sub Form_Load()
cbxAAA = cbxAAA.ItemData(0)
End Sub

' 2. On Current event synchronizes combobox value with current record in
the form (represented by txtAAA)

Private Sub Form_Current()

Me.cbxAAA.Requery

For i = 0 To cbxAAA.ListCount
If cbxAAA.ItemData(i) = Me.txtAAA Then
Me.cbxAAA = cbxAAA.ItemData(i)
Exit For
End If
Next i

End Sub

' On the COMBOBOX istelf I have After Update event which synchronizes
current record (txtAAA) with the value selected from the combobox.

Private Sub cbxAAA_AfterUpdate()

With CodeContextObject
DoCmd.GoToControl "txtAAA"
DoCmd.FindRecord .cbxAAA, acEntire, False, , False, acCurrent, True
End With

End Sub
This works perfectly as long as both textbox and combobox are visible.
But, my intenion was to hide textbox.
Unfortunately, when I put property txtAAA.Visible=False, the above
mentioned After Update event generates an error, because Find method
requires an object focus and hidden object can't have focus...
What is the alternative solution ? What to put in AfterUpdate event of the
combobox to avoid referencing to control object ?

Thanks.

"Allen Browne" <Al*********@SeeSig.Invalid> je napisao u poruci interesnoj
grupi:42***********************@per-qv1-newsreader-01.iinet.net.au...
This article describes how to use an unbound combo to navigate to the
selected record:
http://allenbrowne.com/ser-03.html

The article suggests setting the combo to Null after navigation, so the
user understands it serves no purpose but navigation. If you wish to keep
your unbound combo synchronized with the current record in the form, you
would need to program these events:

- the Current record of the form, so the unbound combo matches the value
from the current record;

- the Undo event of the form, so the unbound combo is restored to the
OldValue from the curent record;

- the AfterUpdate event of the control(s) in the form that matches the
field(s) you wish to synchronize with.
"Zlatko Matif" <zl***********@sb.t-com.hr> wrote in message
news:d1**********@ls219.htnet.hr...
There is a form (single form) and a combobox. I want that current record
of the form is adjusted according to selected value in the combobox.
Cuurrent record should be the same as the value in the combobox.
What is the solution?

Nov 13 '05 #4

P: n/a
Hello, Allen!

Thank you for your code:

Now attach this code to the AfterUpdate property of the Combo Box:

Sub CboMoveTo_AfterUpdate ()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[CustomerID] = " & Me.cboMoveTo
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End SubIt seems very nice, but I'm working in .adp and it seems that it does
not support DAO recordset, but only ADO recordset.
Does this clone method works with ADO recordset ? Could you please write ADO
version of the code?

Thanks in advance.

"Allen Browne" <Al*********@SeeSig.Invalid> je napisao u poruci interesnoj
grupi:42***********************@per-qv1-newsreader-01.iinet.net.au...
Take another look at the code in the article.
It uses FindFirst on the RecordsetClone of the form to find the record.
That will work even if the text box is hidden.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Zlatko Matif" <zl***********@sb.t-com.hr> wrote in message
news:d1**********@ls219.htnet.hr...
Hello, Allen. Thanks for answer.
In fact I have already tried something that matches what you suggest, but
still have a problem...

Let's suppose that txtAAA is textbox and cbxAAA is combobox. My aproach
was the following:

' Events on the form:

' 1. On Load event fills combobox with the first value

Private Sub Form_Load()
cbxAAA = cbxAAA.ItemData(0)
End Sub

' 2. On Current event synchronizes combobox value with current record in
the form (represented by txtAAA)

Private Sub Form_Current()

Me.cbxAAA.Requery

For i = 0 To cbxAAA.ListCount
If cbxAAA.ItemData(i) = Me.txtAAA Then
Me.cbxAAA = cbxAAA.ItemData(i)
Exit For
End If
Next i

End Sub

' On the COMBOBOX istelf I have After Update event which synchronizes
current record (txtAAA) with the value selected from the combobox.

Private Sub cbxAAA_AfterUpdate()

With CodeContextObject
DoCmd.GoToControl "txtAAA"
DoCmd.FindRecord .cbxAAA, acEntire, False, , False, acCurrent,
True
End With

End Sub
This works perfectly as long as both textbox and combobox are visible.
But, my intenion was to hide textbox.
Unfortunately, when I put property txtAAA.Visible=False, the above
mentioned After Update event generates an error, because Find method
requires an object focus and hidden object can't have focus...
What is the alternative solution ? What to put in AfterUpdate event of
the combobox to avoid referencing to control object ?

Thanks.

"Allen Browne" <Al*********@SeeSig.Invalid> je napisao u poruci
interesnoj
grupi:42***********************@per-qv1-newsreader-01.iinet.net.au...
This article describes how to use an unbound combo to navigate to the
selected record:
http://allenbrowne.com/ser-03.html

The article suggests setting the combo to Null after navigation, so the
user understands it serves no purpose but navigation. If you wish to
keep your unbound combo synchronized with the current record in the
form, you would need to program these events:

- the Current record of the form, so the unbound combo matches the value
from the current record;

- the Undo event of the form, so the unbound combo is restored to the
OldValue from the curent record;

- the AfterUpdate event of the control(s) in the form that matches the
field(s) you wish to synchronize with.
"Zlatko Matif" <zl***********@sb.t-com.hr> wrote in message
news:d1**********@ls219.htnet.hr...
There is a form (single form) and a combobox. I want that current
record of the form is adjusted according to selected value in the
combobox. Cuurrent record should be the same as the value in the
combobox.
What is the solution?


Nov 13 '05 #5

P: n/a
Dim the rs as ADO.Recordset.
Use Find instead of FindFirst.
Move to the first record first.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Zlatko Matif" <zl***********@sb.t-com.hr> wrote in message
news:d1*********@ls219.htnet.hr...
Hello, Allen!

Thank you for your code:

Now attach this code to the AfterUpdate property of the Combo Box:

Sub CboMoveTo_AfterUpdate ()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[CustomerID] = " & Me.cboMoveTo
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End SubIt seems very nice, but I'm working in .adp and it seems that it
does not support DAO recordset, but only ADO recordset.
Does this clone method works with ADO recordset ? Could you please write
ADO version of the code?

Thanks in advance.

"Allen Browne" <Al*********@SeeSig.Invalid> je napisao u poruci interesnoj
grupi:42***********************@per-qv1-newsreader-01.iinet.net.au...
Take another look at the code in the article.
It uses FindFirst on the RecordsetClone of the form to find the record.
That will work even if the text box is hidden.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Zlatko Matif" <zl***********@sb.t-com.hr> wrote in message
news:d1**********@ls219.htnet.hr...
Hello, Allen. Thanks for answer.
In fact I have already tried something that matches what you suggest,
but
still have a problem...

Let's suppose that txtAAA is textbox and cbxAAA is combobox. My aproach
was the following:

' Events on the form:

' 1. On Load event fills combobox with the first value

Private Sub Form_Load()
cbxAAA = cbxAAA.ItemData(0)
End Sub

' 2. On Current event synchronizes combobox value with current record in
the form (represented by txtAAA)

Private Sub Form_Current()

Me.cbxAAA.Requery

For i = 0 To cbxAAA.ListCount
If cbxAAA.ItemData(i) = Me.txtAAA Then
Me.cbxAAA = cbxAAA.ItemData(i)
Exit For
End If
Next i

End Sub

' On the COMBOBOX istelf I have After Update event which synchronizes
current record (txtAAA) with the value selected from the combobox.

Private Sub cbxAAA_AfterUpdate()

With CodeContextObject
DoCmd.GoToControl "txtAAA"
DoCmd.FindRecord .cbxAAA, acEntire, False, , False, acCurrent,
True
End With

End Sub
This works perfectly as long as both textbox and combobox are visible.
But, my intenion was to hide textbox.
Unfortunately, when I put property txtAAA.Visible=False, the above
mentioned After Update event generates an error, because Find method
requires an object focus and hidden object can't have focus...
What is the alternative solution ? What to put in AfterUpdate event of
the combobox to avoid referencing to control object ?

Thanks.

"Allen Browne" <Al*********@SeeSig.Invalid> je napisao u poruci
interesnoj
grupi:42***********************@per-qv1-newsreader-01.iinet.net.au...
This article describes how to use an unbound combo to navigate to the
selected record:
http://allenbrowne.com/ser-03.html

The article suggests setting the combo to Null after navigation, so the
user understands it serves no purpose but navigation. If you wish to
keep your unbound combo synchronized with the current record in the
form, you would need to program these events:

- the Current record of the form, so the unbound combo matches the
value from the current record;

- the Undo event of the form, so the unbound combo is restored to the
OldValue from the curent record;

- the AfterUpdate event of the control(s) in the form that matches the
field(s) you wish to synchronize with.
"Zlatko Matif" <zl***********@sb.t-com.hr> wrote in message
news:d1**********@ls219.htnet.hr...
> There is a form (single form) and a combobox. I want that current
> record of the form is adjusted according to selected value in the
> combobox. Cuurrent record should be the same as the value in the
> combobox.
> What is the solution?

Nov 13 '05 #6

P: n/a
I did it, but the following error occurs: "Arguments are of the wrong type,
are out of acceptable range or are in conflict with one another."
What's wrong ?
cbxORGJED is the combobox in single form, it's row source is a stored
procedure with parameters. Form's recordsource is also a stored procedure
with parameters.
Private Sub cbxORGJED_AfterUpdate()

Dim rs As ADODB.Recordset
On Error GoTo cbxORGJED_Err

Set rs = New ADODB.Recordset

If Not IsNull(Me.cbxORGJED) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.MoveFirst
rs.Find "[ORGJED] = " & Me.cbxORGJED
'Display the found record in the form.
Me.Bookmark = rs.Bookmark

rs.Close
Set rs = Nothing
End If

cbxORGJED_Exit:
Exit Sub

cbxORGJED_Err:
MsgBox Error$
Resume cbxORGJED_Exit
End Sub

----- Original Message -----
From: "Allen Browne" <Al*********@SeeSig.Invalid>
Newsgroups: comp.databases.ms-access
Sent: Monday, March 21, 2005 3:49 PM
Subject: Re: How to synchronize current record in the form with value
selected in a combobox ?

Dim the rs as ADO.Recordset.
Use Find instead of FindFirst.
Move to the first record first.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Zlatko Matif" <zl***********@sb.t-com.hr> wrote in message
news:d1*********@ls219.htnet.hr...
Hello, Allen!

Thank you for your code:

Now attach this code to the AfterUpdate property of the Combo Box:

Sub CboMoveTo_AfterUpdate ()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[CustomerID] = " & Me.cboMoveTo
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End SubIt seems very nice, but I'm working in .adp and it seems that it
does not support DAO recordset, but only ADO recordset.
Does this clone method works with ADO recordset ? Could you please write
ADO version of the code?

Thanks in advance.

"Allen Browne" <Al*********@SeeSig.Invalid> je napisao u poruci
interesnoj
grupi:42***********************@per-qv1-newsreader-01.iinet.net.au...
Take another look at the code in the article.
It uses FindFirst on the RecordsetClone of the form to find the record.
That will work even if the text box is hidden.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Zlatko Matif" <zl***********@sb.t-com.hr> wrote in message
news:d1**********@ls219.htnet.hr...
Hello, Allen. Thanks for answer.
In fact I have already tried something that matches what you suggest,
but
still have a problem...

Let's suppose that txtAAA is textbox and cbxAAA is combobox. My aproach
was the following:

' Events on the form:

' 1. On Load event fills combobox with the first value

Private Sub Form_Load()
cbxAAA = cbxAAA.ItemData(0)
End Sub

' 2. On Current event synchronizes combobox value with current record
in the form (represented by txtAAA)

Private Sub Form_Current()

Me.cbxAAA.Requery

For i = 0 To cbxAAA.ListCount
If cbxAAA.ItemData(i) = Me.txtAAA Then
Me.cbxAAA = cbxAAA.ItemData(i)
Exit For
End If
Next i

End Sub

' On the COMBOBOX istelf I have After Update event which synchronizes
current record (txtAAA) with the value selected from the combobox.

Private Sub cbxAAA_AfterUpdate()

With CodeContextObject
DoCmd.GoToControl "txtAAA"
DoCmd.FindRecord .cbxAAA, acEntire, False, , False, acCurrent,
True
End With

End Sub
This works perfectly as long as both textbox and combobox are visible.
But, my intenion was to hide textbox.
Unfortunately, when I put property txtAAA.Visible=False, the above
mentioned After Update event generates an error, because Find method
requires an object focus and hidden object can't have focus...
What is the alternative solution ? What to put in AfterUpdate event of
the combobox to avoid referencing to control object ?

Thanks.

"Allen Browne" <Al*********@SeeSig.Invalid> je napisao u poruci
interesnoj
grupi:42***********************@per-qv1-newsreader-01.iinet.net.au...
> This article describes how to use an unbound combo to navigate to the
> selected record:
> http://allenbrowne.com/ser-03.html
>
> The article suggests setting the combo to Null after navigation, so
> the user understands it serves no purpose but navigation. If you wish
> to keep your unbound combo synchronized with the current record in the
> form, you would need to program these events:
>
> - the Current record of the form, so the unbound combo matches the
> value from the current record;
>
> - the Undo event of the form, so the unbound combo is restored to the
> OldValue from the curent record;
>
> - the AfterUpdate event of the control(s) in the form that matches the
> field(s) you wish to synchronize with.
>
>
> "Zlatko Matif" <zl***********@sb.t-com.hr> wrote in message
> news:d1**********@ls219.htnet.hr...
>> There is a form (single form) and a combobox. I want that current
>> record of the form is adjusted according to selected value in the
>> combobox. Cuurrent record should be the same as the value in the
>> combobox.
>> What is the solution?


"Allen Browne" <Al*********@SeeSig.Invalid> je napisao u poruci interesnoj
grupi:42***********************@per-qv1-newsreader-01.iinet.net.au... Dim the rs as ADO.Recordset.
Use Find instead of FindFirst.
Move to the first record first.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Zlatko Matif" <zl***********@sb.t-com.hr> wrote in message
news:d1*********@ls219.htnet.hr...
Hello, Allen!

Thank you for your code:

Now attach this code to the AfterUpdate property of the Combo Box:

Sub CboMoveTo_AfterUpdate ()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[CustomerID] = " & Me.cboMoveTo
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End SubIt seems very nice, but I'm working in .adp and it seems that it
does not support DAO recordset, but only ADO recordset.
Does this clone method works with ADO recordset ? Could you please write
ADO version of the code?

Thanks in advance.

"Allen Browne" <Al*********@SeeSig.Invalid> je napisao u poruci
interesnoj
grupi:42***********************@per-qv1-newsreader-01.iinet.net.au...
Take another look at the code in the article.
It uses FindFirst on the RecordsetClone of the form to find the record.
That will work even if the text box is hidden.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Zlatko Matif" <zl***********@sb.t-com.hr> wrote in message
news:d1**********@ls219.htnet.hr...
Hello, Allen. Thanks for answer.
In fact I have already tried something that matches what you suggest,
but
still have a problem...

Let's suppose that txtAAA is textbox and cbxAAA is combobox. My aproach
was the following:

' Events on the form:

' 1. On Load event fills combobox with the first value

Private Sub Form_Load()
cbxAAA = cbxAAA.ItemData(0)
End Sub

' 2. On Current event synchronizes combobox value with current record
in the form (represented by txtAAA)

Private Sub Form_Current()

Me.cbxAAA.Requery

For i = 0 To cbxAAA.ListCount
If cbxAAA.ItemData(i) = Me.txtAAA Then
Me.cbxAAA = cbxAAA.ItemData(i)
Exit For
End If
Next i

End Sub

' On the COMBOBOX istelf I have After Update event which synchronizes
current record (txtAAA) with the value selected from the combobox.

Private Sub cbxAAA_AfterUpdate()

With CodeContextObject
DoCmd.GoToControl "txtAAA"
DoCmd.FindRecord .cbxAAA, acEntire, False, , False, acCurrent,
True
End With

End Sub
This works perfectly as long as both textbox and combobox are visible.
But, my intenion was to hide textbox.
Unfortunately, when I put property txtAAA.Visible=False, the above
mentioned After Update event generates an error, because Find method
requires an object focus and hidden object can't have focus...
What is the alternative solution ? What to put in AfterUpdate event of
the combobox to avoid referencing to control object ?

Thanks.

"Allen Browne" <Al*********@SeeSig.Invalid> je napisao u poruci
interesnoj
grupi:42***********************@per-qv1-newsreader-01.iinet.net.au...
> This article describes how to use an unbound combo to navigate to the
> selected record:
> http://allenbrowne.com/ser-03.html
>
> The article suggests setting the combo to Null after navigation, so
> the user understands it serves no purpose but navigation. If you wish
> to keep your unbound combo synchronized with the current record in the
> form, you would need to program these events:
>
> - the Current record of the form, so the unbound combo matches the
> value from the current record;
>
> - the Undo event of the form, so the unbound combo is restored to the
> OldValue from the curent record;
>
> - the AfterUpdate event of the control(s) in the form that matches the
> field(s) you wish to synchronize with.
>
>
> "Zlatko Matif" <zl***********@sb.t-com.hr> wrote in message
> news:d1**********@ls219.htnet.hr...
>> There is a form (single form) and a combobox. I want that current
>> record of the form is adjusted according to selected value in the
>> combobox. Cuurrent record should be the same as the value in the
>> combobox.
>> What is the solution?


Nov 13 '05 #7

P: n/a
I still don't know what was wrong with Find method, but, in meantime I
successfully aplied Do While loop on your Clone method, and now it works!
Thank you, Allen !

Private Sub cbxORGJED_AfterUpdate()

Dim rs As ADODB.Recordset

On Error GoTo cbxORGJED_Err

Set rs = New ADODB.Recordset

If Not IsNull(Me.cbxORGJED) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.MoveFirst

Do While Not rs.EOF
rs.MoveNext
If rs("ORGJED") = cbxORGJED Then Exit Do
Loop

Me.Bookmark = rs.Bookmark

rs.Close
Set rs = Nothing
End If

cbxORGJED_Exit:
Exit Sub

cbxORGJED_Err:
MsgBox Error$
Resume cbxORGJED_Exit

End Sub

"Zlatko Matić" <zl***********@sb.t-com.hr> je napisao u poruci interesnoj
grupi:d1**********@ls219.htnet.hr...
I did it, but the following error occurs: "Arguments are of the wrong type,
are out of acceptable range or are in conflict with one another."
What's wrong ?
cbxORGJED is the combobox in single form, it's row source is a stored
procedure with parameters. Form's recordsource is also a stored procedure
with parameters.
Private Sub cbxORGJED_AfterUpdate()

Dim rs As ADODB.Recordset
On Error GoTo cbxORGJED_Err

Set rs = New ADODB.Recordset

If Not IsNull(Me.cbxORGJED) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.MoveFirst
rs.Find "[ORGJED] = " & Me.cbxORGJED
'Display the found record in the form.
Me.Bookmark = rs.Bookmark

rs.Close
Set rs = Nothing
End If

cbxORGJED_Exit:
Exit Sub

cbxORGJED_Err:
MsgBox Error$
Resume cbxORGJED_Exit
End Sub

----- Original Message -----
From: "Allen Browne" <Al*********@SeeSig.Invalid>
Newsgroups: comp.databases.ms-access
Sent: Monday, March 21, 2005 3:49 PM
Subject: Re: How to synchronize current record in the form with value
selected in a combobox ?

Dim the rs as ADO.Recordset.
Use Find instead of FindFirst.
Move to the first record first.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Zlatko Matif" <zl***********@sb.t-com.hr> wrote in message
news:d1*********@ls219.htnet.hr...
Hello, Allen!

Thank you for your code:

Now attach this code to the AfterUpdate property of the Combo Box:

Sub CboMoveTo_AfterUpdate ()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[CustomerID] = " & Me.cboMoveTo
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End SubIt seems very nice, but I'm working in .adp and it seems that it
does not support DAO recordset, but only ADO recordset.
Does this clone method works with ADO recordset ? Could you please write
ADO version of the code?

Thanks in advance.

"Allen Browne" <Al*********@SeeSig.Invalid> je napisao u poruci
interesnoj
grupi:42***********************@per-qv1-newsreader-01.iinet.net.au...
Take another look at the code in the article.
It uses FindFirst on the RecordsetClone of the form to find the record.
That will work even if the text box is hidden.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Zlatko Matif" <zl***********@sb.t-com.hr> wrote in message
news:d1**********@ls219.htnet.hr...
> Hello, Allen. Thanks for answer.
> In fact I have already tried something that matches what you suggest,
> but
> still have a problem...
>
> Let's suppose that txtAAA is textbox and cbxAAA is combobox. My
> aproach was the following:
>
> ' Events on the form:
>
> ' 1. On Load event fills combobox with the first value
>
> Private Sub Form_Load()
> cbxAAA = cbxAAA.ItemData(0)
> End Sub
>
> ' 2. On Current event synchronizes combobox value with current record
> in the form (represented by txtAAA)
>
> Private Sub Form_Current()
>
> Me.cbxAAA.Requery
>
> For i = 0 To cbxAAA.ListCount
> If cbxAAA.ItemData(i) = Me.txtAAA Then
> Me.cbxAAA = cbxAAA.ItemData(i)
> Exit For
> End If
> Next i
>
> End Sub
>
> ' On the COMBOBOX istelf I have After Update event which synchronizes
> current record (txtAAA) with the value selected from the combobox.
>
> Private Sub cbxAAA_AfterUpdate()
>
> With CodeContextObject
> DoCmd.GoToControl "txtAAA"
> DoCmd.FindRecord .cbxAAA, acEntire, False, , False, acCurrent,
> True
> End With
>
> End Sub
>
>
> This works perfectly as long as both textbox and combobox are visible.
> But, my intenion was to hide textbox.
> Unfortunately, when I put property txtAAA.Visible=False, the above
> mentioned After Update event generates an error, because Find method
> requires an object focus and hidden object can't have focus...
> What is the alternative solution ? What to put in AfterUpdate event of
> the combobox to avoid referencing to control object ?
>
> Thanks.
>
> "Allen Browne" <Al*********@SeeSig.Invalid> je napisao u poruci
> interesnoj
> grupi:42***********************@per-qv1-newsreader-01.iinet.net.au...
>> This article describes how to use an unbound combo to navigate to the
>> selected record:
>> http://allenbrowne.com/ser-03.html
>>
>> The article suggests setting the combo to Null after navigation, so
>> the user understands it serves no purpose but navigation. If you wish
>> to keep your unbound combo synchronized with the current record in
>> the form, you would need to program these events:
>>
>> - the Current record of the form, so the unbound combo matches the
>> value from the current record;
>>
>> - the Undo event of the form, so the unbound combo is restored to the
>> OldValue from the curent record;
>>
>> - the AfterUpdate event of the control(s) in the form that matches
>> the field(s) you wish to synchronize with.
>>
>>
>> "Zlatko Matif" <zl***********@sb.t-com.hr> wrote in message
>> news:d1**********@ls219.htnet.hr...
>>> There is a form (single form) and a combobox. I want that current
>>> record of the form is adjusted according to selected value in the
>>> combobox. Cuurrent record should be the same as the value in the
>>> combobox.
>>> What is the solution?



"Allen Browne" <Al*********@SeeSig.Invalid> je napisao u poruci interesnoj
grupi:42***********************@per-qv1-newsreader-01.iinet.net.au...
Dim the rs as ADO.Recordset.
Use Find instead of FindFirst.
Move to the first record first.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Zlatko Matif" <zl***********@sb.t-com.hr> wrote in message
news:d1*********@ls219.htnet.hr...
Hello, Allen!

Thank you for your code:

Now attach this code to the AfterUpdate property of the Combo Box:

Sub CboMoveTo_AfterUpdate ()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[CustomerID] = " & Me.cboMoveTo
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End SubIt seems very nice, but I'm working in .adp and it seems that it
does not support DAO recordset, but only ADO recordset.
Does this clone method works with ADO recordset ? Could you please write
ADO version of the code?

Thanks in advance.

"Allen Browne" <Al*********@SeeSig.Invalid> je napisao u poruci
interesnoj
grupi:42***********************@per-qv1-newsreader-01.iinet.net.au...
Take another look at the code in the article.
It uses FindFirst on the RecordsetClone of the form to find the record.
That will work even if the text box is hidden.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Zlatko Matif" <zl***********@sb.t-com.hr> wrote in message
news:d1**********@ls219.htnet.hr...
> Hello, Allen. Thanks for answer.
> In fact I have already tried something that matches what you suggest,
> but
> still have a problem...
>
> Let's suppose that txtAAA is textbox and cbxAAA is combobox. My
> aproach was the following:
>
> ' Events on the form:
>
> ' 1. On Load event fills combobox with the first value
>
> Private Sub Form_Load()
> cbxAAA = cbxAAA.ItemData(0)
> End Sub
>
> ' 2. On Current event synchronizes combobox value with current record
> in the form (represented by txtAAA)
>
> Private Sub Form_Current()
>
> Me.cbxAAA.Requery
>
> For i = 0 To cbxAAA.ListCount
> If cbxAAA.ItemData(i) = Me.txtAAA Then
> Me.cbxAAA = cbxAAA.ItemData(i)
> Exit For
> End If
> Next i
>
> End Sub
>
> ' On the COMBOBOX istelf I have After Update event which synchronizes
> current record (txtAAA) with the value selected from the combobox.
>
> Private Sub cbxAAA_AfterUpdate()
>
> With CodeContextObject
> DoCmd.GoToControl "txtAAA"
> DoCmd.FindRecord .cbxAAA, acEntire, False, , False, acCurrent,
> True
> End With
>
> End Sub
>
>
> This works perfectly as long as both textbox and combobox are visible.
> But, my intenion was to hide textbox.
> Unfortunately, when I put property txtAAA.Visible=False, the above
> mentioned After Update event generates an error, because Find method
> requires an object focus and hidden object can't have focus...
> What is the alternative solution ? What to put in AfterUpdate event of
> the combobox to avoid referencing to control object ?
>
> Thanks.
>
> "Allen Browne" <Al*********@SeeSig.Invalid> je napisao u poruci
> interesnoj
> grupi:42***********************@per-qv1-newsreader-01.iinet.net.au...
>> This article describes how to use an unbound combo to navigate to the
>> selected record:
>> http://allenbrowne.com/ser-03.html
>>
>> The article suggests setting the combo to Null after navigation, so
>> the user understands it serves no purpose but navigation. If you wish
>> to keep your unbound combo synchronized with the current record in
>> the form, you would need to program these events:
>>
>> - the Current record of the form, so the unbound combo matches the
>> value from the current record;
>>
>> - the Undo event of the form, so the unbound combo is restored to the
>> OldValue from the curent record;
>>
>> - the AfterUpdate event of the control(s) in the form that matches
>> the field(s) you wish to synchronize with.
>>
>>
>> "Zlatko Matif" <zl***********@sb.t-com.hr> wrote in message
>> news:d1**********@ls219.htnet.hr...
>>> There is a form (single form) and a combobox. I want that current
>>> record of the form is adjusted according to selected value in the
>>> combobox. Cuurrent record should be the same as the value in the
>>> combobox.
>>> What is the solution?



Nov 13 '05 #8

P: n/a
I don't use ADO, but try without:
Set rs = New ADODB.Recordset
because you are pointing the variable at an existing object.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Zlatko Matif" <zl***********@sb.t-com.hr> wrote in message
news:d1**********@ls219.htnet.hr...
I did it, but the following error occurs: "Arguments are of the wrong type,
are out of acceptable range or are in conflict with one another."
What's wrong ?
cbxORGJED is the combobox in single form, it's row source is a stored
procedure with parameters. Form's recordsource is also a stored procedure
with parameters.
Private Sub cbxORGJED_AfterUpdate()

Dim rs As ADODB.Recordset
On Error GoTo cbxORGJED_Err

Set rs = New ADODB.Recordset

If Not IsNull(Me.cbxORGJED) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.MoveFirst
rs.Find "[ORGJED] = " & Me.cbxORGJED
'Display the found record in the form.
Me.Bookmark = rs.Bookmark

rs.Close
Set rs = Nothing
End If

cbxORGJED_Exit:
Exit Sub

cbxORGJED_Err:
MsgBox Error$
Resume cbxORGJED_Exit
End Sub

Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.