dropdown list cbo to find record

[Corrine]

I have a form with a dropdown list from which I want to be able to select an
item and have the rest of the form populate additional fields with that
record's information. Preferrably the form will start out blank. Currently it
is automatically populating with the first record and is only letting me use
the navigation buttons (at the bottom of my form) to move from record to
record. The dropdown list will open, but I cannot select anything from the
list. This form needs to be only for running searches, not for editing in any
way. Suggestions? I'm working in Access '07.

 

[strive4peace]

Hi Corrine,

"The dropdown list will open, but I cannot select anything from the >
list. This form needs to be only for running searches, not for editing
in any way."

My guess is that you have the form AllowEdits property set to False

If you want the user to be able to edit the search combo, instead do this:

for each control, set:
Locked = True
and. optionally,
Enabled = False

and set AllowEdits = True for the form

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Tori]

Good afternoon.

I am using Access 2003. I have a combo box in a form I want to be able to
use for navigation just like Corrine (it sounds like she and I are working on
very similar forms). I set my Properties control for this data field to
Locked = Yes and Enabled = No. My Form Properties control is set to Allow
Edits = Yes.

My drop down arrow for the combo box does not open to allow me to select a
record. I can only navigate using the navigational buttons on the bottom.

What else could I try?

 

[Corrine]

Now the dropdown won't open at all.

Hi Corrine,

"The dropdown list will open, but I cannot select anything from the >
list. This form needs to be only for running searches, not for editing
in any way."

My guess is that you have the form AllowEdits property set to False

If you want the user to be able to edit the search combo, instead do this:

for each control, set:
Locked = True
and. optionally,
Enabled = False

and set AllowEdits = True for the form

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[strive4peace]

Hi Corrine,

make sure that, for the combo:

Locked --> No (False)
Enabled --> Yes (True)

this is opposite of what it should be for your data controls

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[strive4peace]

Hi Tori,

If you can click in the combo and drop the list, then Enabled is Yes
(True) -- if you can't, then the control is probably not Enabled

If you can drop the list but not change the data, then Locked is Yes (True)

The ControlSource (Data tab of Property sheet) for a search combo should
be blank


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[strive4peace]

Corrine and Tori,

oh, hey, I totally forgot about this...

for a form where you do not want to allow changes to the data, make the
form RecordsetType --> Snapshot

that way, you do not have to deal with Locked or Enabled for the
controls -- just leave them set to the defaults

you will be able to change anything that is not bound to the underlying
RecordSource, such as a 'find combo'

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Tori]

Good afternoon.

Okay, I think I am on the right track now - thankyouverymuch. However,
because my customer name is now an unbounded data field, when I scroll
through the database using the navigational buttons, it is not updating the
customer name but it is updating the other fields.

For instance, I select "Atlas" and all the fields populate properly. If I
want to see the record after Atlas and use the navigational buttons, "Atlas"
remains the name in the customer field while all of the other fields continue
to populate properly.

If I use the drop down for my record selection and do not use the
navigational buttons, all the fields are populating properly.

I have a filter for "Open Date." The filter seems to be working properly,
but when I use the navigational buttons, the name is not updating. You
cannot use the drop down for this filter because you do not know what the
next customer record is, which is why you had to use the filter. Did I lose
you?

Any other suggestions?

 

[Corrine]

Tried it both ways. Dropdown still won't work.

Also, or in the meantime, how do I get the form to open by default as blank
and not populate until I choose something from the dropdown list?

Thanks for your help!

 

[strive4peace]

Hi Tori,

"However, because my customer name is now an unbounded data field"

when you make a control to be used as a lookup or a filter, it is not
bound -- and I normally put these in the form header so they are not
confused with data controls.

If Customer Name is a field in your RecordSource, you should have a
bound control to display and edit it.

~~~

for finding records:

Make one or more unbound (no ControlSource) combos on your form (like in
the header). Let the first column be invisible and be the primary key
ID of the RecordSource of your form and then, on its AfterUpdate event...

=FindRecord()

this code goes behind the form:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecord()

'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

'declare a variable to hold the primary key value to look up
' NOTE: this assumes that the primary key is an autonumber
' or a long integer data type -- if this is not the case,
' modify accordingly
Dim mRecordID As Long

'set value to look up by what is selected
mRecordID = Me.ActiveControl

'clear the choice to find
Me.ActiveControl = Null

'find the first value that matches
Me.RecordsetClone.FindFirst "SomeID = " & mRecordID

'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Function

'~~~~~~~~~~~~~~~~~~~~
where
- SomeID is the Name of the primary key field, which is in the
RecordSource of the form -- assuming your primary key is a Long Integer
data type (autonumbers are long integers)

Remember that the Rowsource for a combo can come from anywhere -- it can
pull from multiple tables or only use one ... just make sure that the
first column is the primary key ID of the table you want to search (and
that field is part of the RecordSource for the form you are searching).


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[strive4peace]

Hi Corrine,

if the combo won't drop, then my guess is that the control is not
enabled or you have AllowEdits = False for the form

~~~
to go a new record when a form is opened, put this code in the [Event
Procedure] for the form LOAD event:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Form_Load()
If Not Me.NewRecord Then
DoCmd.RunCommand acCmdRecordsGoToNew
End If
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If the user doesn't type anything, the new record will not be saved



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Corrine]

AllowEdits was already True, but I had Enable = No. I turned Enable back to
Yes, but now I'm back to my original problem.

It's giving me a "syntax error" for that coding.

Hi Corrine,

if the combo won't drop, then my guess is that the control is not
enabled or you have AllowEdits = False for the form

~~~
to go a new record when a form is opened, put this code in the [Event
Procedure] for the form LOAD event:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Form_Load()
If Not Me.NewRecord Then
DoCmd.RunCommand acCmdRecordsGoToNew
End If
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If the user doesn't type anything, the new record will not be saved



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Tried it both ways. Dropdown still won't work.

Also, or in the meantime, how do I get the form to open by default as blank
and not populate until I choose something from the dropdown list?

Thanks for your help!

 

[strive4peace]

Hi Corrine,

"now I'm back to my original problem"
not sure which original problem you mean ... not being able to pick
something?
is Locked = False
for the control?

"It's giving me a "syntax error" for that coding."

please post the line that the compiler is stopping on

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

AllowEdits was already True, but I had Enable = No. I turned Enable back to
Yes, but now I'm back to my original problem.

It's giving me a "syntax error" for that coding.

Hi Corrine,

if the combo won't drop, then my guess is that the control is not
enabled or you have AllowEdits = False for the form

~~~
to go a new record when a form is opened, put this code in the [Event
Procedure] for the form LOAD event:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Form_Load()
If Not Me.NewRecord Then
DoCmd.RunCommand acCmdRecordsGoToNew
End If
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If the user doesn't type anything, the new record will not be saved



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Tried it both ways. Dropdown still won't work.

Also, or in the meantime, how do I get the form to open by default as blank
and not populate until I choose something from the dropdown list?

Thanks for your help!

:

Corrine and Tori,

oh, hey, I totally forgot about this...

for a form where you do not want to allow changes to the data, make the
form RecordsetType --> Snapshot

that way, you do not have to deal with Locked or Enabled for the
controls -- just leave them set to the defaults

you will be able to change anything that is not bound to the underlying
RecordSource, such as a 'find combo'

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




strive4peace wrote:
Hi Corrine,

make sure that, for the combo:

Locked --> No (False)
Enabled --> Yes (True)

this is opposite of what it should be for your data controls

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Corrine wrote:
Now the dropdown won't open at all.

:

Hi Corrine,

"The dropdown list will open, but I cannot select anything from the >
list. This form needs to be only for running searches, not for
editing in any way."

My guess is that you have the form AllowEdits property set to False

If you want the user to be able to edit the search combo, instead do
this:

for each control, set:
Locked = True
and. optionally,
Enabled = False

and set AllowEdits = True for the form

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Corrine wrote:
I have a form with a dropdown list from which I want to be able to
select an item and have the rest of the form populate additional
fields with that record's information. Preferrably the form will
start out blank. Currently it is automatically populating with the
first record and is only letting me use the navigation buttons (at
the bottom of my form) to move from record to record. The dropdown
list will open, but I cannot select anything from the list. This
form needs to be only for running searches, not for editing in any
way. Suggestions? I'm working in Access '07.

 

[Corrine]

Crystal,

Yes, Locked=False.

I just thought of something. The info for the combo box is being pulled from
a different table than for the rest of the form. Could that be causing any
trouble?

It seems to be having trouble with this part of the code:
DoCmd.RunCommand acCmdRecordsGoToNew
End If

Thank you for your help! This database is making me feel stupid!

Hi Corrine,

"now I'm back to my original problem"
not sure which original problem you mean ... not being able to pick
something?
is Locked = False
for the control?

"It's giving me a "syntax error" for that coding."

please post the line that the compiler is stopping on

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

AllowEdits was already True, but I had Enable = No. I turned Enable back to
Yes, but now I'm back to my original problem.

It's giving me a "syntax error" for that coding.

Hi Corrine,

if the combo won't drop, then my guess is that the control is not
enabled or you have AllowEdits = False for the form

~~~
to go a new record when a form is opened, put this code in the [Event
Procedure] for the form LOAD event:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Form_Load()
If Not Me.NewRecord Then
DoCmd.RunCommand acCmdRecordsGoToNew
End If
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If the user doesn't type anything, the new record will not be saved



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Corrine wrote:
Tried it both ways. Dropdown still won't work.

Also, or in the meantime, how do I get the form to open by default as blank
and not populate until I choose something from the dropdown list?

Thanks for your help!

:

Corrine and Tori,

oh, hey, I totally forgot about this...

for a form where you do not want to allow changes to the data, make the
form RecordsetType --> Snapshot

that way, you do not have to deal with Locked or Enabled for the
controls -- just leave them set to the defaults

you will be able to change anything that is not bound to the underlying
RecordSource, such as a 'find combo'

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




strive4peace wrote:
Hi Corrine,

make sure that, for the combo:

Locked --> No (False)
Enabled --> Yes (True)

this is opposite of what it should be for your data controls

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Corrine wrote:
Now the dropdown won't open at all.

:

Hi Corrine,

"The dropdown list will open, but I cannot select anything from the >
list. This form needs to be only for running searches, not for
editing in any way."

My guess is that you have the form AllowEdits property set to False

If you want the user to be able to edit the search combo, instead do
this:

for each control, set:
Locked = True
and. optionally,
Enabled = False

and set AllowEdits = True for the form

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Corrine wrote:
I have a form with a dropdown list from which I want to be able to
select an item and have the rest of the form populate additional
fields with that record's information. Preferrably the form will
start out blank. Currently it is automatically populating with the
first record and is only letting me use the navigation buttons (at
the bottom of my form) to move from record to record. The dropdown
list will open, but I cannot select anything from the list. This
form needs to be only for running searches, not for editing in any
way. Suggestions? I'm working in Access '07.

 

[Corrine]

Crystal,

Yes, Locked = False.

I thought of something. The info for the combo box is being pulled from a
different table than for the rest of the form. Could that be causing any
trouble?

This is code where it's getting hung up:

DoCmd.RunCommand acCmdRecordsGoToNew
End If

Thank you for helping! This database is making me feel stupid!

Hi Corrine,

"now I'm back to my original problem"
not sure which original problem you mean ... not being able to pick
something?
is Locked = False
for the control?

"It's giving me a "syntax error" for that coding."

please post the line that the compiler is stopping on

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

AllowEdits was already True, but I had Enable = No. I turned Enable back to
Yes, but now I'm back to my original problem.

It's giving me a "syntax error" for that coding.

Hi Corrine,

if the combo won't drop, then my guess is that the control is not
enabled or you have AllowEdits = False for the form

~~~
to go a new record when a form is opened, put this code in the [Event
Procedure] for the form LOAD event:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Form_Load()
If Not Me.NewRecord Then
DoCmd.RunCommand acCmdRecordsGoToNew
End If
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If the user doesn't type anything, the new record will not be saved



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Corrine wrote:
Tried it both ways. Dropdown still won't work.

Also, or in the meantime, how do I get the form to open by default as blank
and not populate until I choose something from the dropdown list?

Thanks for your help!

:

Corrine and Tori,

oh, hey, I totally forgot about this...

for a form where you do not want to allow changes to the data, make the
form RecordsetType --> Snapshot

that way, you do not have to deal with Locked or Enabled for the
controls -- just leave them set to the defaults

you will be able to change anything that is not bound to the underlying
RecordSource, such as a 'find combo'

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




strive4peace wrote:
Hi Corrine,

make sure that, for the combo:

Locked --> No (False)
Enabled --> Yes (True)

this is opposite of what it should be for your data controls

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Corrine wrote:
Now the dropdown won't open at all.

:

Hi Corrine,

"The dropdown list will open, but I cannot select anything from the >
list. This form needs to be only for running searches, not for
editing in any way."

My guess is that you have the form AllowEdits property set to False

If you want the user to be able to edit the search combo, instead do
this:

for each control, set:
Locked = True
and. optionally,
Enabled = False

and set AllowEdits = True for the form

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Corrine wrote:
I have a form with a dropdown list from which I want to be able to
select an item and have the rest of the form populate additional
fields with that record's information. Preferrably the form will
start out blank. Currently it is automatically populating with the
first record and is only letting me use the navigation buttons (at
the bottom of my form) to move from record to record. The dropdown
list will open, but I cannot select anything from the list. This
form needs to be only for running searches, not for editing in any
way. Suggestions? I'm working in Access '07.

 

[strive4peace]

Hi Corrine,

it is perfectly fine for the RowSource of a combo to list values from
the same table

is AllowEdits = yes for the form?

Is the form based on just ONE table? If the form RecordSource is based
on multiple tables, you may not be able to edit or add information. It
is, therefore, best to base each form and subform on just one table.

"This database is making me feel stupid!"

read Access Basics -- it is just 100 pages, has lots of screen shots,
and should give you more confidence

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal
remote programming and training

*
have an awesome day
*

Crystal,

Yes, Locked=False.

I just thought of something. The info for the combo box is being pulled from
a different table than for the rest of the form. Could that be causing any
trouble?

It seems to be having trouble with this part of the code:
DoCmd.RunCommand acCmdRecordsGoToNew
End If

Thank you for your help! This database is making me feel stupid!

Hi Corrine,

"now I'm back to my original problem"
not sure which original problem you mean ... not being able to pick
something?
is Locked = False
for the control?

"It's giving me a "syntax error" for that coding."

please post the line that the compiler is stopping on

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

AllowEdits was already True, but I had Enable = No. I turned Enable back to
Yes, but now I'm back to my original problem.

It's giving me a "syntax error" for that coding.

:

Hi Corrine,

if the combo won't drop, then my guess is that the control is not
enabled or you have AllowEdits = False for the form

~~~
to go a new record when a form is opened, put this code in the [Event
Procedure] for the form LOAD event:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Form_Load()
If Not Me.NewRecord Then
DoCmd.RunCommand acCmdRecordsGoToNew
End If
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If the user doesn't type anything, the new record will not be saved



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Corrine wrote:
Tried it both ways. Dropdown still won't work.

Also, or in the meantime, how do I get the form to open by default as blank
and not populate until I choose something from the dropdown list?

Thanks for your help!

:

Corrine and Tori,

oh, hey, I totally forgot about this...

for a form where you do not want to allow changes to the data, make the
form RecordsetType --> Snapshot

that way, you do not have to deal with Locked or Enabled for the
controls -- just leave them set to the defaults

you will be able to change anything that is not bound to the underlying
RecordSource, such as a 'find combo'

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




strive4peace wrote:
Hi Corrine,

make sure that, for the combo:

Locked --> No (False)
Enabled --> Yes (True)

this is opposite of what it should be for your data controls

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Corrine wrote:
Now the dropdown won't open at all.

:

Hi Corrine,

"The dropdown list will open, but I cannot select anything from the >
list. This form needs to be only for running searches, not for
editing in any way."

My guess is that you have the form AllowEdits property set to False

If you want the user to be able to edit the search combo, instead do
this:

for each control, set:
Locked = True
and. optionally,
Enabled = False

and set AllowEdits = True for the form

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Corrine wrote:
I have a form with a dropdown list from which I want to be able to
select an item and have the rest of the form populate additional
fields with that record's information. Preferrably the form will
start out blank. Currently it is automatically populating with the
first record and is only letting me use the navigation buttons (at
the bottom of my form) to move from record to record. The dropdown
list will open, but I cannot select anything from the list. This
form needs to be only for running searches, not for editing in any
way. Suggestions? I'm working in Access '07.

 

[Corrine]

(That was wierd. It posted my last reply twice!)

AllowEdit = yes for the form.

The combo box is from my tbl "Company" and the rest of the form's
information is coming from my tbl "Agreement". The idea was that picking the
company name from the combo box the agreement info will appear. The tables
are linked through the company ID, though, not the name, now that I think of
it.

Thanks for the suggestion.

Hi Corrine,

it is perfectly fine for the RowSource of a combo to list values from
the same table

is AllowEdits = yes for the form?

Is the form based on just ONE table? If the form RecordSource is based
on multiple tables, you may not be able to edit or add information. It
is, therefore, best to base each form and subform on just one table.

"This database is making me feel stupid!"

read Access Basics -- it is just 100 pages, has lots of screen shots,
and should give you more confidence

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal
remote programming and training

*
have an awesome day
*

Crystal,

Yes, Locked=False.

I just thought of something. The info for the combo box is being pulled from
a different table than for the rest of the form. Could that be causing any
trouble?

It seems to be having trouble with this part of the code:
DoCmd.RunCommand acCmdRecordsGoToNew
End If

Thank you for your help! This database is making me feel stupid!

Hi Corrine,

"now I'm back to my original problem"
not sure which original problem you mean ... not being able to pick
something?
is Locked = False
for the control?

"It's giving me a "syntax error" for that coding."

please post the line that the compiler is stopping on

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Corrine wrote:
AllowEdits was already True, but I had Enable = No. I turned Enable back to
Yes, but now I'm back to my original problem.

It's giving me a "syntax error" for that coding.

:

Hi Corrine,

if the combo won't drop, then my guess is that the control is not
enabled or you have AllowEdits = False for the form

~~~
to go a new record when a form is opened, put this code in the [Event
Procedure] for the form LOAD event:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Form_Load()
If Not Me.NewRecord Then
DoCmd.RunCommand acCmdRecordsGoToNew
End If
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If the user doesn't type anything, the new record will not be saved



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Corrine wrote:
Tried it both ways. Dropdown still won't work.

Also, or in the meantime, how do I get the form to open by default as blank
and not populate until I choose something from the dropdown list?

Thanks for your help!

:

Corrine and Tori,

oh, hey, I totally forgot about this...

for a form where you do not want to allow changes to the data, make the
form RecordsetType --> Snapshot

that way, you do not have to deal with Locked or Enabled for the
controls -- just leave them set to the defaults

you will be able to change anything that is not bound to the underlying
RecordSource, such as a 'find combo'

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




strive4peace wrote:
Hi Corrine,

make sure that, for the combo:

Locked --> No (False)
Enabled --> Yes (True)

this is opposite of what it should be for your data controls

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Corrine wrote:
Now the dropdown won't open at all.

:

Hi Corrine,

"The dropdown list will open, but I cannot select anything from the >
list. This form needs to be only for running searches, not for
editing in any way."

My guess is that you have the form AllowEdits property set to False

If you want the user to be able to edit the search combo, instead do
this:

for each control, set:
Locked = True
and. optionally,
Enabled = False

and set AllowEdits = True for the form

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Corrine wrote:
I have a form with a dropdown list from which I want to be able to
select an item and have the rest of the form populate additional
fields with that record's information. Preferrably the form will
start out blank. Currently it is automatically populating with the
first record and is only letting me use the navigation buttons (at
the bottom of my form) to move from record to record. The dropdown
list will open, but I cannot select anything from the list. This
form needs to be only for running searches, not for editing in any
way. Suggestions? I'm working in Access '07.

 

[Corrine]

Wait! I lied! This form is based on a query, not my tables. The query is
based on the two forms. Because it's pulling information from the query it
shouldn't matter that the information was originally found in two tables.
Hmm. Is that square one I see again?

(That was wierd. It posted my last reply twice!)

AllowEdit = yes for the form.

The combo box is from my tbl "Company" and the rest of the form's
information is coming from my tbl "Agreement". The idea was that picking the
company name from the combo box the agreement info will appear. The tables
are linked through the company ID, though, not the name, now that I think of
it.

Thanks for the suggestion.

Hi Corrine,

it is perfectly fine for the RowSource of a combo to list values from
the same table

is AllowEdits = yes for the form?

Is the form based on just ONE table? If the form RecordSource is based
on multiple tables, you may not be able to edit or add information. It
is, therefore, best to base each form and subform on just one table.

"This database is making me feel stupid!"

read Access Basics -- it is just 100 pages, has lots of screen shots,
and should give you more confidence

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal
remote programming and training

*
have an awesome day
*

Crystal,

Yes, Locked=False.

I just thought of something. The info for the combo box is being pulled from
a different table than for the rest of the form. Could that be causing any
trouble?

It seems to be having trouble with this part of the code:
DoCmd.RunCommand acCmdRecordsGoToNew
End If

Thank you for your help! This database is making me feel stupid!

:

Hi Corrine,

"now I'm back to my original problem"
not sure which original problem you mean ... not being able to pick
something?
is Locked = False
for the control?

"It's giving me a "syntax error" for that coding."

please post the line that the compiler is stopping on

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Corrine wrote:
AllowEdits was already True, but I had Enable = No. I turned Enable back to
Yes, but now I'm back to my original problem.

It's giving me a "syntax error" for that coding.

:

Hi Corrine,

if the combo won't drop, then my guess is that the control is not
enabled or you have AllowEdits = False for the form

~~~
to go a new record when a form is opened, put this code in the [Event
Procedure] for the form LOAD event:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Form_Load()
If Not Me.NewRecord Then
DoCmd.RunCommand acCmdRecordsGoToNew
End If
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If the user doesn't type anything, the new record will not be saved



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Corrine wrote:
Tried it both ways. Dropdown still won't work.

Also, or in the meantime, how do I get the form to open by default as blank
and not populate until I choose something from the dropdown list?

Thanks for your help!

:

Corrine and Tori,

oh, hey, I totally forgot about this...

for a form where you do not want to allow changes to the data, make the
form RecordsetType --> Snapshot

that way, you do not have to deal with Locked or Enabled for the
controls -- just leave them set to the defaults

you will be able to change anything that is not bound to the underlying
RecordSource, such as a 'find combo'

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




strive4peace wrote:
Hi Corrine,

make sure that, for the combo:

Locked --> No (False)
Enabled --> Yes (True)

this is opposite of what it should be for your data controls

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Corrine wrote:
Now the dropdown won't open at all.

:

Hi Corrine,

"The dropdown list will open, but I cannot select anything from the >
list. This form needs to be only for running searches, not for
editing in any way."

My guess is that you have the form AllowEdits property set to False

If you want the user to be able to edit the search combo, instead do
this:

for each control, set:
Locked = True
and. optionally,
Enabled = False

and set AllowEdits = True for the form

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Corrine wrote:
I have a form with a dropdown list from which I want to be able to
select an item and have the rest of the form populate additional
fields with that record's information. Preferrably the form will
start out blank. Currently it is automatically populating with the
first record and is only letting me use the navigation buttons (at
the bottom of my form) to move from record to record. The dropdown
list will open, but I cannot select anything from the list. This
form needs to be only for running searches, not for editing in any
way. Suggestions? I'm working in Access '07.

 

[strive4peace]

Hi Corrine,

a query does not store information, it pulls information from tables --
so if your query uses two tables, then so does your form ... and herein
lies the problem...

Why are you using 2 tables in the form RecordSource?

~~~
The tables are linked through the company ID, though, not the name

If you are using a combobox as the DisplayControl in the related table,
you should switch it to textbox. Don't use lookup fields in table design

The Evils of Lookup Fields in Tables
http://www.mvps.org/access/lookupfields.htm



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Wait! I lied! This form is based on a query, not my tables. The query is
based on the two forms. Because it's pulling information from the query it
shouldn't matter that the information was originally found in two tables.
Hmm. Is that square one I see again?

(That was wierd. It posted my last reply twice!)

AllowEdit = yes for the form.

The combo box is from my tbl "Company" and the rest of the form's
information is coming from my tbl "Agreement". The idea was that picking the
company name from the combo box the agreement info will appear. The tables
are linked through the company ID, though, not the name, now that I think of
it.

Thanks for the suggestion.

Hi Corrine,

it is perfectly fine for the RowSource of a combo to list values from
the same table

is AllowEdits = yes for the form?

Is the form based on just ONE table? If the form RecordSource is based
on multiple tables, you may not be able to edit or add information. It
is, therefore, best to base each form and subform on just one table.

"This database is making me feel stupid!"

read Access Basics -- it is just 100 pages, has lots of screen shots,
and should give you more confidence

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal
remote programming and training

*
have an awesome day
*




Corrine wrote:
Crystal,

Yes, Locked=False.

I just thought of something. The info for the combo box is being pulled from
a different table than for the rest of the form. Could that be causing any
trouble?

It seems to be having trouble with this part of the code:
DoCmd.RunCommand acCmdRecordsGoToNew
End If

Thank you for your help! This database is making me feel stupid!

:

Hi Corrine,

"now I'm back to my original problem"
not sure which original problem you mean ... not being able to pick
something?
is Locked = False
for the control?

"It's giving me a "syntax error" for that coding."

please post the line that the compiler is stopping on

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Corrine wrote:
AllowEdits was already True, but I had Enable = No. I turned Enable back to
Yes, but now I'm back to my original problem.

It's giving me a "syntax error" for that coding.

:

Hi Corrine,

if the combo won't drop, then my guess is that the control is not
enabled or you have AllowEdits = False for the form

~~~
to go a new record when a form is opened, put this code in the [Event
Procedure] for the form LOAD event:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Form_Load()
If Not Me.NewRecord Then
DoCmd.RunCommand acCmdRecordsGoToNew
End If
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If the user doesn't type anything, the new record will not be saved



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Corrine wrote:
Tried it both ways. Dropdown still won't work.

Also, or in the meantime, how do I get the form to open by default as blank
and not populate until I choose something from the dropdown list?

Thanks for your help!

:

Corrine and Tori,

oh, hey, I totally forgot about this...

for a form where you do not want to allow changes to the data, make the
form RecordsetType --> Snapshot

that way, you do not have to deal with Locked or Enabled for the
controls -- just leave them set to the defaults

you will be able to change anything that is not bound to the underlying
RecordSource, such as a 'find combo'

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




strive4peace wrote:
Hi Corrine,

make sure that, for the combo:

Locked --> No (False)
Enabled --> Yes (True)

this is opposite of what it should be for your data controls

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Corrine wrote:
Now the dropdown won't open at all.

:

Hi Corrine,

"The dropdown list will open, but I cannot select anything from the >
list. This form needs to be only for running searches, not for
editing in any way."

My guess is that you have the form AllowEdits property set to False

If you want the user to be able to edit the search combo, instead do
this:

for each control, set:
Locked = True
and. optionally,
Enabled = False

and set AllowEdits = True for the form

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Corrine wrote:
I have a form with a dropdown list from which I want to be able to
select an item and have the rest of the form populate additional
fields with that record's information. Preferrably the form will
start out blank. Currently it is automatically populating with the
first record and is only letting me use the navigation buttons (at
the bottom of my form) to move from record to record. The dropdown
list will open, but I cannot select anything from the list. This
form needs to be only for running searches, not for editing in any
way. Suggestions? I'm working in Access '07.

 

[Corrine]

I know queries don't store info. I want the same info it is pulling to be
accessible via my form. That was why I chose the query as my record source.

There are no lookup fields in my tables.

Hi Corrine,

a query does not store information, it pulls information from tables --
so if your query uses two tables, then so does your form ... and herein
lies the problem...

Why are you using 2 tables in the form RecordSource?

~~~
The tables are linked through the company ID, though, not the name

If you are using a combobox as the DisplayControl in the related table,
you should switch it to textbox. Don't use lookup fields in table design

The Evils of Lookup Fields in Tables
http://www.mvps.org/access/lookupfields.htm



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Wait! I lied! This form is based on a query, not my tables. The query is
based on the two forms. Because it's pulling information from the query it
shouldn't matter that the information was originally found in two tables.
Hmm. Is that square one I see again?

(That was wierd. It posted my last reply twice!)

AllowEdit = yes for the form.

The combo box is from my tbl "Company" and the rest of the form's
information is coming from my tbl "Agreement". The idea was that picking the
company name from the combo box the agreement info will appear. The tables
are linked through the company ID, though, not the name, now that I think of
it.

Thanks for the suggestion.

:

Hi Corrine,

it is perfectly fine for the RowSource of a combo to list values from
the same table

is AllowEdits = yes for the form?

Is the form based on just ONE table? If the form RecordSource is based
on multiple tables, you may not be able to edit or add information. It
is, therefore, best to base each form and subform on just one table.

"This database is making me feel stupid!"

read Access Basics -- it is just 100 pages, has lots of screen shots,
and should give you more confidence

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal
remote programming and training

*
have an awesome day
*




Corrine wrote:
Crystal,

Yes, Locked=False.

I just thought of something. The info for the combo box is being pulled from
a different table than for the rest of the form. Could that be causing any
trouble?

It seems to be having trouble with this part of the code:
DoCmd.RunCommand acCmdRecordsGoToNew
End If

Thank you for your help! This database is making me feel stupid!

:

Hi Corrine,

"now I'm back to my original problem"
not sure which original problem you mean ... not being able to pick
something?
is Locked = False
for the control?

"It's giving me a "syntax error" for that coding."

please post the line that the compiler is stopping on

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Corrine wrote:
AllowEdits was already True, but I had Enable = No. I turned Enable back to
Yes, but now I'm back to my original problem.

It's giving me a "syntax error" for that coding.

:

Hi Corrine,

if the combo won't drop, then my guess is that the control is not
enabled or you have AllowEdits = False for the form

~~~
to go a new record when a form is opened, put this code in the [Event
Procedure] for the form LOAD event:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Form_Load()
If Not Me.NewRecord Then
DoCmd.RunCommand acCmdRecordsGoToNew
End If
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If the user doesn't type anything, the new record will not be saved



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Corrine wrote:
Tried it both ways. Dropdown still won't work.

Also, or in the meantime, how do I get the form to open by default as blank
and not populate until I choose something from the dropdown list?

Thanks for your help!

:

Corrine and Tori,

oh, hey, I totally forgot about this...

for a form where you do not want to allow changes to the data, make the
form RecordsetType --> Snapshot

that way, you do not have to deal with Locked or Enabled for the
controls -- just leave them set to the defaults

you will be able to change anything that is not bound to the underlying
RecordSource, such as a 'find combo'

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




strive4peace wrote:
Hi Corrine,

make sure that, for the combo:

Locked --> No (False)
Enabled --> Yes (True)

this is opposite of what it should be for your data controls

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Corrine wrote:
Now the dropdown won't open at all.

:

Hi Corrine,

"The dropdown list will open, but I cannot select anything from the >
list. This form needs to be only for running searches, not for
editing in any way."

My guess is that you have the form AllowEdits property set to False

If you want the user to be able to edit the search combo, instead do
this:

for each control, set:
Locked = True
and. optionally,
Enabled = False

and set AllowEdits = True for the form

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Corrine wrote:
I have a form with a dropdown list from which I want to be able to
select an item and have the rest of the form populate additional
fields with that record's information. Preferrably the form will
start out blank. Currently it is automatically populating with the
first record and is only letting me use the navigation buttons (at
the bottom of my form) to move from record to record. The dropdown
list will open, but I cannot select anything from the list. This
form needs to be only for running searches, not for editing in any
way. Suggestions? I'm working in Access '07.