Issue when using dropdown box on a form

[MEMFBI]

Hello
I hope I can explain this clearly. I have a form that updates 1 table Major Gas. Most data comes from other tables. The data source of the form is a query of multi tables.
If the user changes a selection of a drop down box, the screen reflects the fields from the table correctly, but what is happening ..as an example
Major Code is a drop down from table Pure Gas when record is first presented on the form it is A4 with a price of $10.34.
User changes this Major Code to A5 which has a price of $8.08. What is happening is record A4 's price is being changed to $8.08 in the Pure Gas table.


I am confused why this table is being changed at all.
I hope I have explained this correctly.

Thank you!

[Micron]

Most of the time you use combos to choose list items and then do things like filter records or choose list values to save in a new record, thus you do not bind them to anything (do not use the combo controlsource property). If you bind them you will set whatever field you've bound it to become the new chosen value. Forms with unbound controls usually need code to save or edit a record using the unbound data.

If you need to bind in your situation, then you must either realize the result of choosing a different value, or you need to use a single form view and disable them for existing records and enable them for new records or if you can, swap form mode from view to edit and disable/enable accordingly.
HTH

[MEMFBI]

Thank you. I apologize for my lack of knowledge. If I just want the user to be able to select a record by using a drop down box, but not change any records in the table of the source of that drop down box, how should I be creating the drop down box?
Perhaps I should be using a List Box, not a combo box?

[mike60smart]

Hi
Can you upload a zipped copy of the database?

[Micron]

how should I be creating the drop down box?

As mentioned, do not have anything set for the control source property. If it already filters (selects?) your records it should still do so without a controlsource property. Open the form in design, select the combo and remove whatever is showing on the property sheet for that control. If that doesn't work then maybe you will have to post a zipped db copy as suggested - you haven't said if this was a single form, split form, or main form/subform.

[MEMFBI]

Gas_Test.zip

I hope I attached the zip file correctly. It is a scaled down version. Open frm_Main, select a record..the form I am having issues with will open..it is Gas_Master.

Thank you!

[mike60smart]

Hi

I opened the Gas Master Form but it does not have any Combobox in the detail area?

[mike60smart]

Hi

I have modified the Gas Master Form which now has 2 Combobox's. 1 for selecting the Category and the other for selecting the Cycle.

[MEMFBI]

Hi

I have modified the Gas Master Form which now has 2 Combobox's. 1 for selecting the Category and the other for selecting the Cycle.

Actually the form has @ 10 combo boxes on it. The one in question is cbo_majcd0

[mike60smart]

In the Db that you uploaded you said select frmMain - then select a record?

When I select frm Main there is a Command Button to open the Gas Maintenance Form.

It is this Form that I have updated.

[mike60smart]

Hi

It is the Master Cost File Maintenance Form where you have the problem.

I just looked at it and this indicates that the table "PGAS000" needs to be normalised.

The screenshot of the Controls indicates that you have what is known as Repeating Groups.

Instead of fields in you PGAS000 table they should be records in a related table.

[ssanfu]

I would strongly suggest/recommend that you stop and normalize the tables, fix the field names, field types and the relationships.


This image is for the look up Tables



This image is for the related Tables

[MEMFBI]

Hi

It is the Master Cost File Maintenance Form where you have the problem.

I just looked at it and this indicates that the table "PGAS000" needs to be normalised.

The screenshot of the Controls indicates that you have what is known as Repeating Groups.

Instead of fields in you PGAS000 table they should be records in a related table.

Thank you. This data was downloaded from another system. I will look into normalization.

[MEMFBI]

I would strongly suggest/recommend that you stop and normalize the tables, fix the field names, field types and the relationships.


This image is for the look up Tables



This image is for the related Tables

Thank you . I will read up on normalization.

[ssanfu]

First, my apologies. I spent a lot more time looking at your dB and realized the "Related Tables" appear to be multiple copies of table "PGASC02" (because of the repeating fields)and are Look up Tables.
Only the table "Gas_Notes" (with its repeating fields - which should be redesigned) is a related table.

Your design is not how I would design the tables. I shouldn't have said "Bad design".
My tables (99.98% of the time) have a PK field of type Autonumber. See Microsoft Access Tables: Primary Key Tips and Techniques

I have been working on your dB..... if/when I get time I'll post it. I have deleted all of the records just to make it easier to add/change fields/relationships.


It looks, to me, like "PGAS000" is the main table. See if this helps any.
There are 70 fields (if I counted correctly) in the table, so I had to do a little cutting and pasting to get all of the fields in the image.....