Blank Combo Box when form opens

[OnTheJobLearning]

I have a combo box for transaction #'s (cboTx#) in a form. I got the #'s to
show in descending order through a query, but when I open the form the combo
box is empty. The form shows the last record entered, but the combo box
doesn't.

How can I get the combo box to populate with the last transaction # that
corresponds to the last record when I open the form?

Thanks in advance!
lj

 

[mscertified]

bind the combo box to the table column where the value is stored (Data tab of
Properties).

-Dorian

 

[Klatuu]

Using # in a name is not a good idea. Names should consist of letters,
numbers, and the underscore character. There should be no spaces or other
special characters.

Forms open to the first record in the recordset unless you have some code
that positions the form differently. You could use the form's load event to
set the value of the combo box to the correct value.

 

[OnTheJobLearning]

I had tried this before, but when I click on the combo box to select another
record, I get the error:
The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
(Error 3022)
You tried to duplicate a value in a field that is the underlying table's
primary key or an index that does not allow duplicates.

Then, if I try to close the form, I get this message:
Update or CancelUpdate without AddNew or Edit. (Error 3020)
On a Microsoft Jet database, you called the Update or CancelUpdate method
but did not use the AddNew or Edit method before writing data to a record.

On an ODBCDirect database, this error occurs when you attempt to write data
to a record without first calling AddNew or Edit.

Since this is my key field, can I change it to "Duplicates OK" to solve
this? Or, would this create a problem in the database functioning somewhere
else?
lj

 

[Klatuu]

That would be expected. A search Combo should almost always be an unbound
control, because it does change data in the table.
If you have the field bound to a text box hidden or not on your form, you
can get the value there in the form load event.

Me.MyCombo = Me.MyText Box

You may also want to do that in the Current event so it stays in sync.

 

[OnTheJobLearning]

I changed the combo box back to unbound. I don't want to change any data in
the table when I use it!
I guess I'll just open the form, and click on the transaction # in the combo
box to show it. After that, any choice I make with the combo box works fine
and the corresponding record is displayed.
Without further knowlege on my part, I don't want to create a problem within
the database. I wanted to combo box because there are starting to be so many
records that it is not efficient to click the "previous record" button to go
back 20 or so records!

thanks for your prompt help!

 

[Klatuu]

Oaky, it is not required that you change any data with an unbound combo, but
if you are using it to select a record, you will need something like this in
the After Update event of the combo to make it go to the selected record:

With Me.RecordsetClone
.FindFirst "[Some Field] = '" & Me.MyCombo & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Change [Some Field] to the name of the recordset field that you want to
search on.
Change MyCombo to the name of your combo
Note the FindFirst line syntax assumes [Some Field] is a text field. If it
is numeric, use this:
.FindFirst "[Some Field] = " & Me.MyCombo
And for a Date/Time field:
.FindFirst "[Some Field] = #" & Me.MyCombo & "#"

 

[OnTheJobLearning]

That put me back to where I began. The combo box works, but when I open the
form, the box is empty. This is what was in the After Update event:

Set rs = Me.Recordset.Clone
rs.FindFirst "[Transaction #] = " & Str(Nz(Me![Combo242], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

lj

Oaky, it is not required that you change any data with an unbound combo, but
if you are using it to select a record, you will need something like this in
the After Update event of the combo to make it go to the selected record:

With Me.RecordsetClone
.FindFirst "[Some Field] = '" & Me.MyCombo & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Change [Some Field] to the name of the recordset field that you want to
search on.
Change MyCombo to the name of your combo
Note the FindFirst line syntax assumes [Some Field] is a text field. If it
is numeric, use this:
.FindFirst "[Some Field] = " & Me.MyCombo
And for a Date/Time field:
.FindFirst "[Some Field] = #" & Me.MyCombo & "#"


--
Dave Hargis, Microsoft Access MVP

I changed the combo box back to unbound. I don't want to change any data in
the table when I use it!
I guess I'll just open the form, and click on the transaction # in the combo
box to show it. After that, any choice I make with the combo box works fine
and the corresponding record is displayed.
Without further knowlege on my part, I don't want to create a problem within
the database. I wanted to combo box because there are starting to be so many
records that it is not efficient to click the "previous record" button to go
back 20 or so records!

thanks for your prompt help!

 

[Klatuu]

You didn't put any code in the form Load event, did you?

Well, no matter, you can do it by adding code to the form Current event,
because you are going to want it to stay in sync. I assume you have a text
box on the form that is bound to Transaction #

Me.Combo242 = Me.[txtTransaction #]

You really need to work on your naming.
Nobody will know what Combo242 means and again, # should not be used in a
name.
--
Dave Hargis, Microsoft Access MVP

That put me back to where I began. The combo box works, but when I open the
form, the box is empty. This is what was in the After Update event:

Set rs = Me.Recordset.Clone
rs.FindFirst "[Transaction #] = " & Str(Nz(Me![Combo242], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

lj

Oaky, it is not required that you change any data with an unbound combo, but
if you are using it to select a record, you will need something like this in
the After Update event of the combo to make it go to the selected record:

With Me.RecordsetClone
.FindFirst "[Some Field] = '" & Me.MyCombo & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Change [Some Field] to the name of the recordset field that you want to
search on.
Change MyCombo to the name of your combo
Note the FindFirst line syntax assumes [Some Field] is a text field. If it
is numeric, use this:
.FindFirst "[Some Field] = " & Me.MyCombo
And for a Date/Time field:
.FindFirst "[Some Field] = #" & Me.MyCombo & "#"


--
Dave Hargis, Microsoft Access MVP

I changed the combo box back to unbound. I don't want to change any data in
the table when I use it!
I guess I'll just open the form, and click on the transaction # in the combo
box to show it. After that, any choice I make with the combo box works fine
and the corresponding record is displayed.
Without further knowlege on my part, I don't want to create a problem within
the database. I wanted to combo box because there are starting to be so many
records that it is not efficient to click the "previous record" button to go
back 20 or so records!

thanks for your prompt help!

:

That would be expected. A search Combo should almost always be an unbound
control, because it does change data in the table.
If you have the field bound to a text box hidden or not on your form, you
can get the value there in the form load event.

Me.MyCombo = Me.MyText Box

You may also want to do that in the Current event so it stays in sync.
--
Dave Hargis, Microsoft Access MVP


:

I had tried this before, but when I click on the combo box to select another
record, I get the error:
The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
(Error 3022)
You tried to duplicate a value in a field that is the underlying table's
primary key or an index that does not allow duplicates.

Then, if I try to close the form, I get this message:
Update or CancelUpdate without AddNew or Edit. (Error 3020)
On a Microsoft Jet database, you called the Update or CancelUpdate method
but did not use the AddNew or Edit method before writing data to a record.

On an ODBCDirect database, this error occurs when you attempt to write data
to a record without first calling AddNew or Edit.

Since this is my key field, can I change it to "Duplicates OK" to solve
this? Or, would this create a problem in the database functioning somewhere
else?
lj



:

bind the combo box to the table column where the value is stored (Data tab of
Properties).

-Dorian

:

I have a combo box for transaction #'s (cboTx#) in a form. I got the #'s to
show in descending order through a query, but when I open the form the combo
box is empty. The form shows the last record entered, but the combo box
doesn't.

How can I get the combo box to populate with the last transaction # that
corresponds to the last record when I open the form?

Thanks in advance!
lj

 

[OnTheJobLearning]

Yes, I know. Next time I start a database, I'll follow the naming rules!
Once I got into it though, it is hard to go back and rename.

I appreciate all the help.

You didn't put any code in the form Load event, did you?

Well, no matter, you can do it by adding code to the form Current event,
because you are going to want it to stay in sync. I assume you have a text
box on the form that is bound to Transaction #

Me.Combo242 = Me.[txtTransaction #]

You really need to work on your naming.
Nobody will know what Combo242 means and again, # should not be used in a
name.
--
Dave Hargis, Microsoft Access MVP

That put me back to where I began. The combo box works, but when I open the
form, the box is empty. This is what was in the After Update event:

Set rs = Me.Recordset.Clone
rs.FindFirst "[Transaction #] = " & Str(Nz(Me![Combo242], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

lj

Oaky, it is not required that you change any data with an unbound combo, but
if you are using it to select a record, you will need something like this in
the After Update event of the combo to make it go to the selected record:

With Me.RecordsetClone
.FindFirst "[Some Field] = '" & Me.MyCombo & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Change [Some Field] to the name of the recordset field that you want to
search on.
Change MyCombo to the name of your combo
Note the FindFirst line syntax assumes [Some Field] is a text field. If it
is numeric, use this:
.FindFirst "[Some Field] = " & Me.MyCombo
And for a Date/Time field:
.FindFirst "[Some Field] = #" & Me.MyCombo & "#"


--
Dave Hargis, Microsoft Access MVP


:

I changed the combo box back to unbound. I don't want to change any data in
the table when I use it!
I guess I'll just open the form, and click on the transaction # in the combo
box to show it. After that, any choice I make with the combo box works fine
and the corresponding record is displayed.
Without further knowlege on my part, I don't want to create a problem within
the database. I wanted to combo box because there are starting to be so many
records that it is not efficient to click the "previous record" button to go
back 20 or so records!

thanks for your prompt help!

:

That would be expected. A search Combo should almost always be an unbound
control, because it does change data in the table.
If you have the field bound to a text box hidden or not on your form, you
can get the value there in the form load event.

Me.MyCombo = Me.MyText Box

You may also want to do that in the Current event so it stays in sync.
--
Dave Hargis, Microsoft Access MVP


:

I had tried this before, but when I click on the combo box to select another
record, I get the error:
The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
(Error 3022)
You tried to duplicate a value in a field that is the underlying table's
primary key or an index that does not allow duplicates.

Then, if I try to close the form, I get this message:
Update or CancelUpdate without AddNew or Edit. (Error 3020)
On a Microsoft Jet database, you called the Update or CancelUpdate method
but did not use the AddNew or Edit method before writing data to a record.

On an ODBCDirect database, this error occurs when you attempt to write data
to a record without first calling AddNew or Edit.

Since this is my key field, can I change it to "Duplicates OK" to solve
this? Or, would this create a problem in the database functioning somewhere
else?
lj



:

bind the combo box to the table column where the value is stored (Data tab of
Properties).

-Dorian

:

I have a combo box for transaction #'s (cboTx#) in a form. I got the #'s to
show in descending order through a query, but when I open the form the combo
box is empty. The form shows the last record entered, but the combo box
doesn't.

How can I get the combo box to populate with the last transaction # that
corresponds to the last record when I open the form?

Thanks in advance!
lj