Records changing in combo box

[Guest]

I have made a database which has 4 forms comprising contractors, contracts,
advertising companies and advertising details.

Each of the forms has a drop down combo box, here is an example of what the
combo box does on the contractor form: user clicks on combo box which brings
up a list of contractors(around 200), they can then select one. After
selecting a contractor their address details appear in a subform and also the
contracts they have applied for appears in another subform. This all works
fine, so whenever the user selects a contractor the 2 subforms display
required information.

The only problems im having is with records being changed, if the user uses
the scroll button on the mouse it seems to sometimes overwrite contractor
names. It doesnt overwrite address and contract details, it just makes
duplicate contractor names where they shouldnt be.

I think i have solved that problem by downloading the mousescroll off thing
from website suggested on other posts, so now the scroll doesnt work on combo
boxes.

But im still having a problem with the first contractor name being
overwritten by the last contractor looked at. If the user looks at a
contractor called North and then closes the form, North then seems to
overwrite the first record and North then appears twice. The strange thing is
that it doesnt just overwrite it in the form, it also makes North appear
twice in the original table.

Does anyone have any ideas to help me?

 

[Rob Parker]

It sounds like the combo-box used to select the contractor is bound to a
field in the form's recordsource. Make sure that it is unbound. Its
recordsource can be (in this case, probalby should be) a select statement
based on the same table/query as the form's recordsource.

HTH,

Rob

 

[Guest]

Rob

I think that the combo box is unbound as i told the wizard to look up values
in a table, this was ContractorDetails table. Then told it to remember value
for later use.

I then changed the row source so that it was using ContractorID and
ContractorName which are both in table ContractorDetails. Bound Column was
changed to 2 in the combo box, so that the contractorname is displayed in the
box.

Any other ideas?

 

[Rob Parker]

I don't normally use wizards, so I'm not exactly sure what you mean when you
say "told it to remember value for later use" (and I don't know what Access
does if you tell it that). However, you can tell if the combo-box is
unbound in design view - it will show "Unbound" in the control; if your
combo-box is bound, the control will contain the name of the field to which
it is bound. You can also check by looking at the Control Source property
in the Data tab of the Properties dialog - an unbound control will have no
entry there, and a bound control will have a field name (or perhaps an
expression).

I still think that a bound combo-box is likely to be the source of the
problem. Can you check that and post back if that's not the case? (BTW, I
won't reply again for some time - it's now after midnight local time, and
I'm off to bed!)

As a side point, you can control what the combo-box displays by settting the
column width to 0 for the columns you don't want to see, rather than by
changing the bound column. This is preferable to changing the bound column,
particularly if you use wizard-generated code to find a record based on the
combo-box selection, since if you change the bound column after the code is
generated, you will need to also change the code to reflect that change.

Rob

 

[Ron2005]

Ricardo,

Here is a sort of schematic as to how combo boxes work. The combo box
will be called EmployeeCombo:

1) Rowsource is the query that will supply the information for the
dropdown. It can contain as many fields as you want. For this example I
will say it extracts: Emp#, EmpName, EmpPaygrade. in that order in
the query.

2) Related to that will be the following:

Column count would be set to 3 since it contains 3 columns in the
query.

Column widths would control what columns would show in the dropdown
box. In this example I will say 0", 1.5", 1.0". this will mean that
when I see the dropdown It will show the EmpName and the EmpPaygrade.

Bound column is the column containing the data that will be saved in a
field and/or loaded into a table field if it is bound (something has
been selected in "Control Source" for my example I am going to say 1.

With the box I have described above, When I press the arrow on the
dropdown I will see the employee name and paygrade, After I have
selected an individual, I will see the employee name in the box (since
it is the first visible control in the list of visible fields), and the
value that will be saved is the Emp# since it is the bound column. If I
load a field with me.EmployeeCombo it will show the Emp#.

In addition, each of the fields of the selected "record" of the query
can also be addressed and used by using
me.EmployeeCombo.Column(0) to get the Emp#
me.EmployeeCombo.Column(1) to get the EmpName
me.EmployeeCombo.Column(2) to get the EmpPayGrade
and of course
me.EmployeeCombo to get the Emp#

I hope this helps clarify some of the use of combo boxes and may give
you some ideas on how it can be used.

Ron

 

[Guest]

Rob or Ron

My combo box is definitely bound as it has contractorname in the control
source. I have already done things like changing the column width and bound
columns. I think the way i have done it is far more complicated than it needs
to be, i have tried making it unbound and the list works when i do this but
then the other details dont appear below.

I will try to tell you now exactly what im wanting to happen as its probably
easy to do but im just doing it a stupid way!

I have a Table called TContractorsDetails which has the fields ContractorID,
ContractorName, Address, Address2, Address3, PostCode, PhoneNumber, FaxNumber
and EMail.

What i want is a combo box which has all the contractors names listed, so
that when the user clicks on one of the names then all of that contractors
details will appear on the form. Is there an easy way of doing this as if
there is i will just make a new form the way u say?

The only way i managed to get it to work was having ContractorName and
ContractorID on a form, then all of the other fields were on a subform and i
linked the form to subform using ContractorName i think. This did work as
when a ContractorName was selected in combo box their details appeared below
in the subform. But then that stupid problem which i mentioned before about
overwriting records happened.

cheers for your help guys

 

[Ron2005]

I suggest the following:

the combobox rowsource be either the table you have indicated above or
a query that gets those fields. Using the aboveTcontractors details as
the example with the fields listed above selected in the query in that
sequence.

The number of columns would be 9

The column widths would be 0,1,0,0,0,0,0,0,0 - This will cause the
contractor name to show in the dropdown.

The bound column should probably be 1 so that it will store that
information and you will therefore always get the current information

In the afterupdate or the after change (assuming that you do not allow
entries other than what the dropdown can pick) put the following code

me.txtboxforaddress = me.comboboxname.column(2) ' this is the third
column in the query
me.txtboxforaddress2 = me.comboboxname.column(3) ' this is the fourth
column in the query

etc until you have filled out all of the fields.

This will fill the information ONLY when then select a contractor.

If it is to be the other way around - form comes up with an existing
contractor and you want the fields filled, then
in the source for txtboxforaddress you would put
=me.comboboxname.column(2) etc for the rest of the fields. If you use
this method, you may or may not need to refresh or repaint the screen
in the afterupdate event of the combobox after you have selected a
different contractor. I simply have not made that type of screen.
Experiment first.

Ron

 

[Rob Parker]

Hi Ricardo,

I've seen Ron's reply to your last post; you could try that, but it's not
what I would recommend. Here's my approach:

First, a couple of assumptions:
ContractorID is the primary key for TContractsDetails, and is a number (I
guess it's an autonumber) If it's not a number, you'll need to add text
delimiters to some code.
You only want to display a single contractor's details and contacts in the
subforms.

If this is so, there is no reason to be displaying the contractor details in
a subform; they would normally be displayed in the main form, and the
subform shows the contracts for the selected contractor (the standard
practice - 1 parent record on the main form, and multiple child records in
the subform).

The selector combo-box - let's call it cboxSelectContractor - will be placed
in the header section of the main form, and will not be bound to the form's
recordsource. The details section of the form will contain textboxes bound
to each field in TContractorsDetails (the ContractorID may have its Visible
property set to No if it's an autonumber); this will allow you to edit any
of those records.

You can set up your combo-box using the Wizard to "Find a record on my form
...."; you'll probably finish up with the combo-box's RowSource being a SQL
statement such as "SELECT DISTINCTROW [TContractorsDetails].[ContractorID],
[TContractorsDetails].[ContractorName] FROM [TContractorsDetails];", with
the bound column being 1, the column count being 2, and the column widths
being 0cm; 2.54cm (or some value in inches - change the second one to suit).
The combo-box will have the following code in its AfterUpdate event:

Sub cboxSelectContractor_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[ContractorID] = " &
Me![cboxSelectContractor]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

And that's all you need!

HTH,

Rob

 

[Guest]

ok ill try that and see how i get on.

cheers guys

Hi Ricardo,

I've seen Ron's reply to your last post; you could try that, but it's not
what I would recommend. Here's my approach:

First, a couple of assumptions:
ContractorID is the primary key for TContractsDetails, and is a number (I
guess it's an autonumber) If it's not a number, you'll need to add text
delimiters to some code.
You only want to display a single contractor's details and contacts in the
subforms.

If this is so, there is no reason to be displaying the contractor details in
a subform; they would normally be displayed in the main form, and the
subform shows the contracts for the selected contractor (the standard
practice - 1 parent record on the main form, and multiple child records in
the subform).

The selector combo-box - let's call it cboxSelectContractor - will be placed
in the header section of the main form, and will not be bound to the form's
recordsource. The details section of the form will contain textboxes bound
to each field in TContractorsDetails (the ContractorID may have its Visible
property set to No if it's an autonumber); this will allow you to edit any
of those records.

You can set up your combo-box using the Wizard to "Find a record on my form
...."; you'll probably finish up with the combo-box's RowSource being a SQL
statement such as "SELECT DISTINCTROW [TContractorsDetails].[ContractorID],
[TContractorsDetails].[ContractorName] FROM [TContractorsDetails];", with
the bound column being 1, the column count being 2, and the column widths
being 0cm; 2.54cm (or some value in inches - change the second one to suit).
The combo-box will have the following code in its AfterUpdate event:

Sub cboxSelectContractor_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[ContractorID] = " &
Me![cboxSelectContractor]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

And that's all you need!

HTH,

Rob

Rob or Ron

My combo box is definitely bound as it has contractorname in the control
source. I have already done things like changing the column width and bound
columns. I think the way i have done it is far more complicated than it needs
to be, i have tried making it unbound and the list works when i do this but
then the other details dont appear below.

I will try to tell you now exactly what im wanting to happen as its probably
easy to do but im just doing it a stupid way!

I have a Table called TContractorsDetails which has the fields ContractorID,
ContractorName, Address, Address2, Address3, PostCode, PhoneNumber, FaxNumber
and EMail.

What i want is a combo box which has all the contractors names listed, so
that when the user clicks on one of the names then all of that contractors
details will appear on the form. Is there an easy way of doing this as if
there is i will just make a new form the way u say?

The only way i managed to get it to work was having ContractorName and
ContractorID on a form, then all of the other fields were on a subform and i
linked the form to subform using ContractorName i think. This did work as
when a ContractorName was selected in combo box their details appeared below
in the subform. But then that stupid problem which i mentioned before about
overwriting records happened.

cheers for your help guys

 

[Guest]

Cheers rob i tried what you said and it works a treat! thanks a lot.

cheers for your help aswell ron

ricardo

Hi Ricardo,

I've seen Ron's reply to your last post; you could try that, but it's not
what I would recommend. Here's my approach:

First, a couple of assumptions:
ContractorID is the primary key for TContractsDetails, and is a number (I
guess it's an autonumber) If it's not a number, you'll need to add text
delimiters to some code.
You only want to display a single contractor's details and contacts in the
subforms.

If this is so, there is no reason to be displaying the contractor details in
a subform; they would normally be displayed in the main form, and the
subform shows the contracts for the selected contractor (the standard
practice - 1 parent record on the main form, and multiple child records in
the subform).

The selector combo-box - let's call it cboxSelectContractor - will be placed
in the header section of the main form, and will not be bound to the form's
recordsource. The details section of the form will contain textboxes bound
to each field in TContractorsDetails (the ContractorID may have its Visible
property set to No if it's an autonumber); this will allow you to edit any
of those records.

You can set up your combo-box using the Wizard to "Find a record on my form
...."; you'll probably finish up with the combo-box's RowSource being a SQL
statement such as "SELECT DISTINCTROW [TContractorsDetails].[ContractorID],
[TContractorsDetails].[ContractorName] FROM [TContractorsDetails];", with
the bound column being 1, the column count being 2, and the column widths
being 0cm; 2.54cm (or some value in inches - change the second one to suit).
The combo-box will have the following code in its AfterUpdate event:

Sub cboxSelectContractor_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[ContractorID] = " &
Me![cboxSelectContractor]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

And that's all you need!

HTH,

Rob

Rob or Ron

My combo box is definitely bound as it has contractorname in the control
source. I have already done things like changing the column width and bound
columns. I think the way i have done it is far more complicated than it needs
to be, i have tried making it unbound and the list works when i do this but
then the other details dont appear below.

I will try to tell you now exactly what im wanting to happen as its probably
easy to do but im just doing it a stupid way!

I have a Table called TContractorsDetails which has the fields ContractorID,
ContractorName, Address, Address2, Address3, PostCode, PhoneNumber, FaxNumber
and EMail.

What i want is a combo box which has all the contractors names listed, so
that when the user clicks on one of the names then all of that contractors
details will appear on the form. Is there an easy way of doing this as if
there is i will just make a new form the way u say?

The only way i managed to get it to work was having ContractorName and
ContractorID on a form, then all of the other fields were on a subform and i
linked the form to subform using ContractorName i think. This did work as
when a ContractorName was selected in combo box their details appeared below
in the subform. But then that stupid problem which i mentioned before about
overwriting records happened.

cheers for your help guys