Using a Combo Box to Find Records

[Scott_Brasted]

Greetings,

I have been reading Allen Browne's Access website and I just tried the trick
to use a combo box to find records on a form. It works great except that the
cbo is empty when the form opens. The form and subform have the right records,
but I have to check the correct entry in the cbo to have it display the entry
data. After that it works until I close and reopen the form. Does anyone know
what I can do to correct this?

Thanks,
Scott

 

[Jeanette Cunningham]

Hi Scott,
Here is a way to do it.
In the form's load or open event put a line of code like this-->

Me.[NameOfCombo] = Me.[NameOfCombo].ItemData(0)

The code sets the combo to show the first row from the list.
If the first row of the combo is not the one that matches what the form
shows, you can add an extra line-->

Call Me.[NameOfCombo]_AfterUpdate

Replace NameOfCombo of your combo.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jeanette Cunningham]

Oops,
that extra line is wrong.
It should read

Call [NameOfCombo]_AfterUpdate


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi Scott,
Here is a way to do it.
In the form's load or open event put a line of code like this-->

Me.[NameOfCombo] = Me.[NameOfCombo].ItemData(0)

The code sets the combo to show the first row from the list.
If the first row of the combo is not the one that matches what the form
shows, you can add an extra line-->

Call Me.[NameOfCombo]_AfterUpdate

Replace NameOfCombo of your combo.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Greetings,

I have been reading Allen Browne's Access website and I just tried the
trick
to use a combo box to find records on a form. It works great except that
the
cbo is empty when the form opens. The form and subform have the right
records,
but I have to check the correct entry in the cbo to have it display the
entry
data. After that it works until I close and reopen the form. Does anyone
know
what I can do to correct this?

Thanks,
Scott

 

[Scott_Brasted via AccessMonster.com]

Good morning Jeanette,

Thanks for the reply. I woke up this morning thinking that I needed a
form_current or on_open event, I just did not know wat to write. I am very
new to vb in Access.

When I put your code in the open or current event, the cbo puts the field
name for the id key (setupID) in and then gives me an error that says "The
value you entered isn't valid for this field . . ." When I add the 2nd line
of code, it is red in the code window and throw an even more ominous error
messsage. Any thoughts?

Best,
Scott

Oops,
that extra line is wrong.
It should read

Call [NameOfCombo]_AfterUpdate

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi Scott,
Here is a way to do it.

[quoted text clipped - 28 lines]

 

[BruceM via AccessMonster.com]

The Current event will run every time you navigate to a record. That may not
be what you need. To set a value initially I would use the Load event. Open
may work, too, but I tend to regard the Load event as the first opportunity
to work with data. If you are checking the value of a field, Open is too
soon.

I'm not sure why Jeanette suggested calling the combo box After Update code.
Perhaps there is something in the code at Allen Browne's web site of which
she has specific knowledge I am lacking, but in any case you need to compile
the code if you have not done so (Debug >> Compile).

You speak of adding a second line of code, but I don't see where Jeanette
suggested that, so it is not clear to me what you did. It would help if you
post the actual code, as well as the text of any error messages. There is no
way to know what you would regard as ominous.

Good morning Jeanette,

Thanks for the reply. I woke up this morning thinking that I needed a
form_current or on_open event, I just did not know wat to write. I am very
new to vb in Access.

When I put your code in the open or current event, the cbo puts the field
name for the id key (setupID) in and then gives me an error that says "The
value you entered isn't valid for this field . . ." When I add the 2nd line
of code, it is red in the code window and throw an even more ominous error
messsage. Any thoughts?

Best,
Scott

Oops,
that extra line is wrong.

[quoted text clipped - 9 lines]

 

[Scott_Brasted via AccessMonster.com]

Buce, thanks for the reply.

Here is the code for the cbo:

Private 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 "[SetupID] = " & 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 Sub

As I said it works, as is but show a blank box when the form opens.

Jeanette's code: Me.[NameOfCombo] = Me.[NameOfCombo].ItemData(0)

Jeanette's second respone had the following comment:
Oops,
that extra line is wrong.
It should read
Call [NameOfCombo]_AfterUpdate

None of this does anything to the cbo.

When I try to compile, I get errow I cannot understand. The code the compiler
stosa at is MS code. It came with the db and is part of the switchboard. Here
is the code that contans the place wher the compiler stops:
Private Sub cmdExit_GotFocus()
Dim intOption As Integer

'If the Exit Button has received the focus, turn off the focus on all the
menu options
For intOption = 1 To conNumButtons
Me("Option" & intOption).Visible = False
Me("OptionLabel" & intOption).FontWeight = conFontWeightNormal
Next intOption

ExitLabel.FontUnderline = True
End Sub

ExitLable is highlighted by the compiler.

Best,
Scott

The Current event will run every time you navigate to a record. That may not
be what you need. To set a value initially I would use the Load event. Open
may work, too, but I tend to regard the Load event as the first opportunity
to work with data. If you are checking the value of a field, Open is too
soon.

I'm not sure why Jeanette suggested calling the combo box After Update code.
Perhaps there is something in the code at Allen Browne's web site of which
she has specific knowledge I am lacking, but in any case you need to compile
the code if you have not done so (Debug >> Compile).

You speak of adding a second line of code, but I don't see where Jeanette
suggested that, so it is not clear to me what you did. It would help if you
post the actual code, as well as the text of any error messages. There is no
way to know what you would regard as ominous.

Good morning Jeanette,

[quoted text clipped - 16 lines]

 

[BruceM via AccessMonster.com]

Jeanette did not know the name of your combo box, so she used a generic name
NameOfCombo, and the instruction "Replace NameOfCombo of your combo". I will
admit this is a bit cryptic. The idea is that you would substitute the
actual name of your combo box:

Me.[cboMoveTo] = Me.[cboMoveTo].ItemData(0)

This should show you the first item on the list. Try placing the code in the
form's Load event. If this is not what you want, please specify what you do
want.

As for the compile error, I understand you to be saying this is the
troublesome line of code:

ExitLabel.FontUnderline = True

This underlines the font in ExitLabel, which is presumably a label, probably
next to the Exit button. If there is no such label (as I suspect), delete
the code or rename the label (if it is now named something else). It seems
the code in the Exit button's Got Focus event is looping through controls
named Option1, Option2, etc., and OptionLabel1, OptionLabel2, etc., making
Option1 etc. invisible, and changing the label font to normal weight. It
does this for the number of times represented by the variable conNumButtons,
which presumably is a constant somewhere in the code. You could delete the
entire Got Focus code and not do any harm I can see. All it does is some
formatting and stuff like that. Or comment it out by placing an apostrophe
in front of each line of code.

A compile error contains some sort of description. Like any error about
which you are seeking help, post the full message you receive. In this case
I think I can guess what is going on, but the less guessing responders have
to do the sooner you will receive a targeted response.

Buce, thanks for the reply.

Here is the code for the cbo:

Private 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 "[SetupID] = " & 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 Sub

As I said it works, as is but show a blank box when the form opens.

Jeanette's code: Me.[NameOfCombo] = Me.[NameOfCombo].ItemData(0)

Jeanette's second respone had the following comment:
Oops,
that extra line is wrong.
It should read
Call [NameOfCombo]_AfterUpdate

None of this does anything to the cbo.

When I try to compile, I get errow I cannot understand. The code the compiler
stosa at is MS code. It came with the db and is part of the switchboard. Here
is the code that contans the place wher the compiler stops:
Private Sub cmdExit_GotFocus()
Dim intOption As Integer

'If the Exit Button has received the focus, turn off the focus on all the
menu options
For intOption = 1 To conNumButtons
Me("Option" & intOption).Visible = False
Me("OptionLabel" & intOption).FontWeight = conFontWeightNormal
Next intOption

ExitLabel.FontUnderline = True
End Sub

ExitLable is highlighted by the compiler.

Best,
Scott

The Current event will run every time you navigate to a record. That may not
be what you need. To set a value initially I would use the Load event. Open

[quoted text clipped - 17 lines]

 

[Scott_Brasted via AccessMonster.com]

Bruce,

Without getting frustrated or upset, I know what Jeanette wanted me to do
with the code. I changed the cbo to the name I have in my form when I got her
answer. The code she suggested is not doing anything and I am tryng to figure
out why. I have it in the on_load event and I have an empty cbo when I open
the form. As I mentioned, the data is correct and I can choose an entry to
click on and that will be correct also. Everything seems fine except for the
empty cbo.

Here is the entire code for that form:

Option Compare Database
Option Explicit

Private 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 "[SetupID] = " & 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 Sub

Private Sub cmdCloseCampaignPledgeSummaryForm_Click()
On Error GoTo Err_cmdCloseCampaignPledgeSummaryForm_Click
DoCmd.Close
Exit_cmdCloseCampaignPledgeSummaryForm_C:
Exit Sub
Err_cmdCloseCampaignPledgeSummaryForm_Click:
MsgBox Err.Description
Resume Exit_cmdCloseCampaignPledgeSummaryForm_C
End Sub

Private Sub cboMoveTnLoad()
Me.[cboMoveTo] = Me.[cboMoveTo].ItemData(0)
End Sub

By the way,I just did as you suggested and commented out the code for the
label the switchboard wtill works fine and the code compiled in a few seconds.
Can you explain what happens when I compile the code? Does the db work
differently or better?

Thanks,
Scott

Jeanette did not know the name of your combo box, so she used a generic name
NameOfCombo, and the instruction "Replace NameOfCombo of your combo". I will
admit this is a bit cryptic. The idea is that you would substitute the
actual name of your combo box:

Me.[cboMoveTo] = Me.[cboMoveTo].ItemData(0)

This should show you the first item on the list. Try placing the code in the
form's Load event. If this is not what you want, please specify what you do
want.

As for the compile error, I understand you to be saying this is the
troublesome line of code:

ExitLabel.FontUnderline = True

This underlines the font in ExitLabel, which is presumably a label, probably
next to the Exit button. If there is no such label (as I suspect), delete
the code or rename the label (if it is now named something else). It seems
the code in the Exit button's Got Focus event is looping through controls
named Option1, Option2, etc., and OptionLabel1, OptionLabel2, etc., making
Option1 etc. invisible, and changing the label font to normal weight. It
does this for the number of times represented by the variable conNumButtons,
which presumably is a constant somewhere in the code. You could delete the
entire Got Focus code and not do any harm I can see. All it does is some
formatting and stuff like that. Or comment it out by placing an apostrophe
in front of each line of code.

A compile error contains some sort of description. Like any error about
which you are seeking help, post the full message you receive. In this case
I think I can guess what is going on, but the less guessing responders have
to do the sooner you will receive a targeted response.

Buce, thanks for the reply.

[quoted text clipped - 58 lines]

 

[Jeanette Cunningham]

Scott,
I am just getting back to this thread - it is 6am here in Australia.

This code-->
Private Sub cboMoveTnLoad()
Me.[cboMoveTo] = Me.[cboMoveTo].ItemData(0)
End Sub

is meant to make the combo show the first item that you would see if you
clicked on the combo. By the way, the line -->
needs to be put in the Form_Load event.

The idea is that when the form load event runs, it puts that first item in
the combo.
The load event always runs when a form is opened.

Let's check out why this doesn't work for you.

In the code you have on the Form_Load event,
put this line
Debug.Print "First item on combo list is: " & Me.cboMoveTo.ItemData(0)


Now open the form.
Press Alt + G to open the Immediate window.
Look what access has put for the first item in the combo.

Let us know what access put in the immediate window.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Bruce,

Without getting frustrated or upset, I know what Jeanette wanted me to do
with the code. I changed the cbo to the name I have in my form when I got
her
answer. The code she suggested is not doing anything and I am tryng to
figure
out why. I have it in the on_load event and I have an empty cbo when I
open
the form. As I mentioned, the data is correct and I can choose an entry to
click on and that will be correct also. Everything seems fine except for
the
empty cbo.

Here is the entire code for that form:

Option Compare Database
Option Explicit

Private 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 "[SetupID] = " & 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 Sub

Private Sub cmdCloseCampaignPledgeSummaryForm_Click()
On Error GoTo Err_cmdCloseCampaignPledgeSummaryForm_Click
DoCmd.Close
Exit_cmdCloseCampaignPledgeSummaryForm_C:
Exit Sub
Err_cmdCloseCampaignPledgeSummaryForm_Click:
MsgBox Err.Description
Resume Exit_cmdCloseCampaignPledgeSummaryForm_C
End Sub

Private Sub cboMoveTnLoad()
Me.[cboMoveTo] = Me.[cboMoveTo].ItemData(0)
End Sub

By the way,I just did as you suggested and commented out the code for the
label the switchboard wtill works fine and the code compiled in a few
seconds.
Can you explain what happens when I compile the code? Does the db work
differently or better?

Thanks,
Scott

Jeanette did not know the name of your combo box, so she used a generic
name
NameOfCombo, and the instruction "Replace NameOfCombo of your combo". I
will
admit this is a bit cryptic. The idea is that you would substitute the
actual name of your combo box:

Me.[cboMoveTo] = Me.[cboMoveTo].ItemData(0)

This should show you the first item on the list. Try placing the code in
the
form's Load event. If this is not what you want, please specify what you
do
want.

As for the compile error, I understand you to be saying this is the
troublesome line of code:

ExitLabel.FontUnderline = True

This underlines the font in ExitLabel, which is presumably a label,
probably
next to the Exit button. If there is no such label (as I suspect), delete
the code or rename the label (if it is now named something else). It
seems
the code in the Exit button's Got Focus event is looping through controls
named Option1, Option2, etc., and OptionLabel1, OptionLabel2, etc., making
Option1 etc. invisible, and changing the label font to normal weight. It
does this for the number of times represented by the variable
conNumButtons,
which presumably is a constant somewhere in the code. You could delete
the
entire Got Focus code and not do any harm I can see. All it does is some
formatting and stuff like that. Or comment it out by placing an
apostrophe
in front of each line of code.

A compile error contains some sort of description. Like any error about
which you are seeking help, post the full message you receive. In this
case
I think I can guess what is going on, but the less guessing responders
have
to do the sooner you will receive a targeted response.

Buce, thanks for the reply.

[quoted text clipped - 58 lines]

Thanks,
Scott

 

[BruceM via AccessMonster.com]

I didn't know you understood Jeanette's suggestion based on what you wrote.
You wouldn't be the first person attempting to use a placeholder field or
control name in actual code.

The ItemData code needs to go into the *form's* Load event. A combo box
doesn't have a Load or OnLoad event (at least not in Access 2003 and earlier).
Did that code compile?

When closing a form I like to be specific:
DoCmd.Close acForm, "frmCampaignSummary"

If you are closing the form on which the command button lives you can do:
DoCmd.Close acForm, Me.Name

I can't say exactly what compiling code does, except that it will identify
syntax errors, references to non-existent fields and controls (perhaps due to
spelling errors), functions with the wrong number of arguments (for instance,
Left("Test text") would generate a compile error because you need to specify
the number of characters), functions it doesn't recognize (again, spelling
errors perhaps). A database with uncompiled code could harbor an error. In
some cases this would be code that runs only when certain conditions are met,
so it could be a while before the error happens. I believe compiling
improves performance, but most of the time probably not enough to make much
of a difference.

As an aside, Option Explict is a good thing to have at the top. It means you
have to declare all variables. If you do not, Access tends to assume
anything it doesn't recognize is a variant, which can lead to very
frustrating troubleshooting just because you typed something incorrectly.
The difference between lngID and IngID may not be readily apparent by eye
(one uses a lower case "l" and the other an upper case "I", in case you are
viewing this with sans serif font).

Bruce,

Without getting frustrated or upset, I know what Jeanette wanted me to do
with the code. I changed the cbo to the name I have in my form when I got her
answer. The code she suggested is not doing anything and I am tryng to figure
out why. I have it in the on_load event and I have an empty cbo when I open
the form. As I mentioned, the data is correct and I can choose an entry to
click on and that will be correct also. Everything seems fine except for the
empty cbo.

Here is the entire code for that form:

Option Compare Database
Option Explicit

Private 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 "[SetupID] = " & 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 Sub

Private Sub cmdCloseCampaignPledgeSummaryForm_Click()
On Error GoTo Err_cmdCloseCampaignPledgeSummaryForm_Click
DoCmd.Close
Exit_cmdCloseCampaignPledgeSummaryForm_C:
Exit Sub
Err_cmdCloseCampaignPledgeSummaryForm_Click:
MsgBox Err.Description
Resume Exit_cmdCloseCampaignPledgeSummaryForm_C
End Sub

Private Sub cboMoveTnLoad()
Me.[cboMoveTo] = Me.[cboMoveTo].ItemData(0)
End Sub

By the way,I just did as you suggested and commented out the code for the
label the switchboard wtill works fine and the code compiled in a few seconds.
Can you explain what happens when I compile the code? Does the db work
differently or better?

Thanks,
Scott

Jeanette did not know the name of your combo box, so she used a generic name
NameOfCombo, and the instruction "Replace NameOfCombo of your combo". I will

[quoted text clipped - 34 lines]

 

[Scott_Brasted via AccessMonster.com]

Jeanette,

Here is what is in imm. win:
First item on combo list is: Setup ID
But if I perservere and get past the error message, the correct data is in
the cbo.

SetupID is the autonumber primary key for the table for the main form. The
table is the list of fund raising campaigns and the field in the combo box is
the campaign name. I get the error: The value you entered isn't valid for
this field. Does it matter that there is a subform?

Thanks,
Scott

Scott,
I am just getting back to this thread - it is 6am here in Australia.

This code-->
Private Sub cboMoveTnLoad()
Me.[cboMoveTo] = Me.[cboMoveTo].ItemData(0)
End Sub

is meant to make the combo show the first item that you would see if you
clicked on the combo. By the way, the line -->
needs to be put in the Form_Load event.

The idea is that when the form load event runs, it puts that first item in
the combo.
The load event always runs when a form is opened.

Let's check out why this doesn't work for you.

In the code you have on the Form_Load event,
put this line
Debug.Print "First item on combo list is: " & Me.cboMoveTo.ItemData(0)

Now open the form.
Press Alt + G to open the Immediate window.
Look what access has put for the first item in the combo.

Let us know what access put in the immediate window.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Bruce,

[quoted text clipped - 107 lines]

 

[Jeanette Cunningham]

Hi Scott,
I assume that the combo now correctly shows the first item on its list after
the form loads?
The error message comes when the next line of code runs
Is this what happens?
If the answer is yes, just comment out the line of code
Call cboMoveTo_AfterUpdate

and see how that goes.

That line of code was put in to simulate the user clicking the combo to
finding the records that match the SetupID for the first item on the list.
You may not need to do this.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette,

Here is what is in imm. win:
First item on combo list is: Setup ID
But if I perservere and get past the error message, the correct data is in
the cbo.

SetupID is the autonumber primary key for the table for the main form. The
table is the list of fund raising campaigns and the field in the combo box
is
the campaign name. I get the error: The value you entered isn't valid
for
this field. Does it matter that there is a subform?

Thanks,
Scott

Scott,
I am just getting back to this thread - it is 6am here in Australia.

This code-->
Private Sub cboMoveTnLoad()
Me.[cboMoveTo] = Me.[cboMoveTo].ItemData(0)
End Sub

is meant to make the combo show the first item that you would see if you
clicked on the combo. By the way, the line -->
needs to be put in the Form_Load event.

The idea is that when the form load event runs, it puts that first item in
the combo.
The load event always runs when a form is opened.

Let's check out why this doesn't work for you.

In the code you have on the Form_Load event,
put this line
Debug.Print "First item on combo list is: " & Me.cboMoveTo.ItemData(0)

Now open the form.
Press Alt + G to open the Immediate window.
Look what access has put for the first item in the combo.

Let us know what access put in the immediate window.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Bruce,

[quoted text clipped - 107 lines]

Thanks,
Scott

 

[Scott_Brasted via AccessMonster.com]

Bruce

My appoligies for being too sensitive. It has been a particularly bad couple
of weeks. I appreciate the thoughts on compiling. It makes good sense. It
amazes me that MS will write db's with code that does not compile. Also
thankyou for the idea on the close button. As I understand what you said, It
pays to be more specific in my code. What does the ac in acForm represent?

Thank you for all your help and patience.

Best,
Scott

I didn't know you understood Jeanette's suggestion based on what you wrote.
You wouldn't be the first person attempting to use a placeholder field or
control name in actual code.

The ItemData code needs to go into the *form's* Load event. A combo box
doesn't have a Load or OnLoad event (at least not in Access 2003 and earlier).
Did that code compile?

When closing a form I like to be specific:
DoCmd.Close acForm, "frmCampaignSummary"

If you are closing the form on which the command button lives you can do:
DoCmd.Close acForm, Me.Name

I can't say exactly what compiling code does, except that it will identify
syntax errors, references to non-existent fields and controls (perhaps due to
spelling errors), functions with the wrong number of arguments (for instance,
Left("Test text") would generate a compile error because you need to specify
the number of characters), functions it doesn't recognize (again, spelling
errors perhaps). A database with uncompiled code could harbor an error. In
some cases this would be code that runs only when certain conditions are met,
so it could be a while before the error happens. I believe compiling
improves performance, but most of the time probably not enough to make much
of a difference.

As an aside, Option Explict is a good thing to have at the top. It means you
have to declare all variables. If you do not, Access tends to assume
anything it doesn't recognize is a variant, which can lead to very
frustrating troubleshooting just because you typed something incorrectly.
The difference between lngID and IngID may not be readily apparent by eye
(one uses a lower case "l" and the other an upper case "I", in case you are
viewing this with sans serif font).

Bruce,

[quoted text clipped - 58 lines]

 

[Scott_Brasted via AccessMonster.com]

Jeanette,

I took out the line: Call cboMoveTo_AfterUpdate a while ago because it turned
red in the code window and caused an error. The cbo still shows blank and I
get the error mentioned above with the code I posted above with the error
message posted above.

Best,
Scott

Hi Scott,
I assume that the combo now correctly shows the first item on its list after
the form loads?
The error message comes when the next line of code runs
Is this what happens?
If the answer is yes, just comment out the line of code
Call cboMoveTo_AfterUpdate

and see how that goes.

That line of code was put in to simulate the user clicking the combo to
finding the records that match the SetupID for the first item on the list.
You may not need to do this.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette,

[quoted text clipped - 48 lines]

 

[Jeanette Cunningham]

That combo searches using SetupID.
From reading through all your previous posts, I assume that the primary key
of the form is something other than SetupID.
For the code line with ItemData(0) to work, the combo must search on the
primary key field of the form.

However, if the form has only value for SetupID when it opens,
you could try code like this-->
Me.cboMoveTo = Me.SetupID

Put that one line of code in the Form_Load event.

If the form has more than one value for SetupID when it opens, the code
above won't work. If it doesn't work, leave the combo blank when the form
opens.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette,

I took out the line: Call cboMoveTo_AfterUpdate a while ago because it
turned
red in the code window and caused an error. The cbo still shows blank and
I
get the error mentioned above with the code I posted above with the error
message posted above.

Best,
Scott

Hi Scott,
I assume that the combo now correctly shows the first item on its list
after
the form loads?
The error message comes when the next line of code runs

Call cboMoveTo_AfterUpdate

Is this what happens?
If the answer is yes, just comment out the line of code
Call cboMoveTo_AfterUpdate

and see how that goes.

That line of code was put in to simulate the user clicking the combo to
finding the records that match the SetupID for the first item on the list.
You may not need to do this.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette,

[quoted text clipped - 48 lines]

Thanks,
Scott

 

[Scott_Brasted via AccessMonster.com]

Hi, setupID IS the primary key.

That combo searches using SetupID.
From reading through all your previous posts, I assume that the primary key
of the form is something other than SetupID.
For the code line with ItemData(0) to work, the combo must search on the
primary key field of the form.

However, if the form has only value for SetupID when it opens,
you could try code like this-->
Me.cboMoveTo = Me.SetupID

Put that one line of code in the Form_Load event.

If the form has more than one value for SetupID when it opens, the code
above won't work. If it doesn't work, leave the combo blank when the form
opens.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette,

[quoted text clipped - 32 lines]

 

[BruceM via AccessMonster.com]

I wrote in my previous posting that the code needs to be in the *form's* Load
event. You have posted code for the combo box OnLoad event. Maybe there is
such a thing in Access 2007 (there is not in Access 2003), but in any case
the code needs to be in the form's Load event. You have not mentioned
whether you moved the code there, but the most recent posting of the code
indicates it is still in the combo box OnLoad event, whatever that is.

Does the code compile?

Hi, setupID IS the primary key.

That combo searches using SetupID.
From reading through all your previous posts, I assume that the primary key

[quoted text clipped - 19 lines]

 

[BruceM via AccessMonster.com]

By the way, Jeanette also (and originally) specified the form's Load event.
I have just been trying to stress that point.

Hi, setupID IS the primary key.

That combo searches using SetupID.
From reading through all your previous posts, I assume that the primary key

[quoted text clipped - 19 lines]

 

[Scott_Brasted via AccessMonster.com]

OK, I woke up this mornng withyet another idea. This
time it was a good one. I took Jeanette's code and replaced the Me.[cboMoveTo]
= Me.[cboMoveTo].ItemData(0) with Me.[cboMoveTo] = Me.[cboMoveTo].ItemData(1)
ans voila, magic and all that. Simple is great.

Mant thanks for for the persistance.

Best,
scott

That combo searches using SetupID.
From reading through all your previous posts, I assume that the primary key
of the form is something other than SetupID.
For the code line with ItemData(0) to work, the combo must search on the
primary key field of the form.

However, if the form has only value for SetupID when it opens,
you could try code like this-->
Me.cboMoveTo = Me.SetupID

Put that one line of code in the Form_Load event.

If the form has more than one value for SetupID when it opens, the code
above won't work. If it doesn't work, leave the combo blank when the form
opens.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette,

[quoted text clipped - 32 lines]

 

[BruceM via AccessMonster.com]

Are you saying the code came directly from a Microsoft template, with no
alteration to either the code or the forms? If so, I am a little surprised
too, but a subtle and seemingly insignificant change can lead to code that no
longer works. I suspect this is what occurred, but it doesn't really matter.
Any code should be compiled. One reason has to do with references. In the
VBA editor, click Tools >> References. Various libraries are checked.
However, different Access applications may use different libraries, or ones
beyond the basic libraries that are almost always in use. If code references
something in one of these libraries, but the library is not loaded, you will
have problems with the code.

This brings me to intrinsic constants such as acForm and vbYesNo. The prefix
ac means it is a constant defined by the Access library; the vb prefix means
the Visual Basic library. They are used in place of numbers. For instance,
in Access 2003 (or my version at least), acForm is the number 2. The
following should be equivalent:

DoCmd.Close acForm, Me.Name
DoCmd.Close 2, Me.Name

The number 2 is the same as acForm only after DoCmd.Close. In the following
code, acToolbarNo is the number 2 also:
DoCmd.ShowToolbar "ToolBarName", acToolbarNo

The number represented by the constant varies depending on what comes before
it, sort of as a street number depends on the street name to define a
specific location.

It is possible that some numbers will change in the future, or are different
in past versions, so the intrinsic constant should be used. Access will
translate it to the correct number behind the scenes. Also, it is easier to
understand a constant than a number when reading the code.

That's about as much as I can say, and some of that is deduced from some
experiments I did. Help has more information about constants in general, and
intrinsic constants in particular.

Bruce

My appoligies for being too sensitive. It has been a particularly bad couple
of weeks. I appreciate the thoughts on compiling. It makes good sense. It
amazes me that MS will write db's with code that does not compile. Also
thankyou for the idea on the close button. As I understand what you said, It
pays to be more specific in my code. What does the ac in acForm represent?

Thank you for all your help and patience.

Best,
Scott

I didn't know you understood Jeanette's suggestion based on what you wrote.
You wouldn't be the first person attempting to use a placeholder field or

[quoted text clipped - 34 lines]