473,287 Members | 1,880 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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

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
8 12027
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Paolo | last post by:
Friends, I have created a form named FRMNEWCLIENTS whose record source is a table named NEWCLIENTS. This table has a field named FILENUMBER. I have added on the form a combobox using the third...
6
by: Robin S. | last post by:
Originally I wanted a list box which selects which record is the current one within the same form. Easy enough until Access takes a dump when one is deleted and then someone tries to select it in...
6
by: Bernd Smits | last post by:
Hi, I would like to delete a record (with commandbutton) of a table associated to a combobox, when I select a certain value in the combobox (the value I select is associated with the record that I...
3
by: tsteinke | last post by:
What is the syntax to refer to your current row in an SQL statement? I am using the "Lookup Wizard" to build a query in a table. How do you refer to the Current Row For instance I have a Table...
3
by: Christopher Weaver | last post by:
I want to set a value in a specific field in the current row of a DataSet. This seems like the most basic thing to do but I can't find the syntax for identifying the current row. IOW, I can do...
1
by: dbuchanan | last post by:
Hello, I have the controls on my form display the values of the currently selected row of the dataGrid. There are a few fields that I have in my data table where I have the user select a value...
6
by: dbuchanan | last post by:
VS2005 I've been reading all the help I can on the topic (MSDN, other) but I can't make sense of this. Desired behavior; The user is to choose from the displayed list of the databound combobox...
10
by: sara | last post by:
Hi - I am developing a simple app, and just found a problem that I can't fix after 4 hours of trying. I display a list of customers, and the user chooses one and displays "orders". The...
0
by: TD | last post by:
I have a main form with two subforms (both in datasheet view), neither of which are linked to the main form. The main form is based on a query that uses the bound column of a combobox on the main...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.