Combo Boxes

[ssanfu]

<snip>
Steve, you mentioned about these 2 lines of code being at the top of every module, could you explain what they do as I've not noticed them before?

See http://www.fmsinc.com/MicrosoftAcces...ons/index.html

<snip>
One thing I have attempted to alter is the sub forms User_ID combo box. Ideally I would like this to simply be a text box but when I tried to change it, the Clear button returned the below error:

The code should not be the cause of the error. Don't know what you did/how you tried to change the combo box to a text box.



Close all forms.
Open the subform in design view

One method is to:
Select the combo box.
Right click on the combo box, select "Change to", then select "Text Box".
Close and save the sub form.

Another method:
Delete the combo box
Re-add the "User_ID" field (text box control) to the form.
Close and save the sub form.

Open the main form. You will (should) see






-----------------------------------------------------------------------


I missed a reserved word - "Password" (in table tbl_Users) is also a reserved word .... I would use "UserPW" or "UserPassword".

[Beanie_d83]

One method is to:
Select the combo box.
Right click on the combo box, select "Change to", then select "Text Box".
Close and save the sub form.

This is the method I used to begin with, but as you rightly say it only returned the user_ID number which wasn't what I wanted so I then made the control source for the text box the Username field from tbl_users. This has populated the text box with the correct information but won't clear when the button is clicked. It will only clear when I close the form and reopen again.

[ArviLaanemets]

You have a Main form (most likely an unbound form) with 2 unbound controls, the value for 2nd one determined by value of 1st one.
And you have on Main form a single form as subform, linked to 1st of unbound controls. NB! The second unbound control doesn't affect anything!

The 1st question is, why do you need the 2nd control at all? Why not display it simply in combo box. An example:
You have a table tblComponentTypes: CType, CTypeName, with values like "RPR", "Ram Piston Rod"

Your combo will have RowSource = "SELECT CType, CTypeName FROM tblComponentTypes" (in case you want the user to see only type name displayed) or like "SELECT CType, LEFT(CType & Space(10),19) & ":" & CTypeName FROM tblComponentTypes" ( in case you want to user to see something like "RPR :Ram Piston Rod" displayed). You set the ColumnCount to 2, ColumnWidths to "0;2.5", and Bound column to 1. When you select "Ram Piston Rod" or "RPR :Ram Piston Rod" in combo, it's value will be "RTR" (you see in combo only one column from RowSource - the 1st one with width > 0).

The 2nd question is about subform. Is it planned, that you can register several parts of same type in it? When yes, then this design is OK (but probably you need a combo in subform to select a record there too instead cycling through all parts of same type in subform to find the wanted one). When not, then you don't neef Main Form > Subform setting at all - you can have a single form with unbound combo on same form to search and select a record.

[Beanie_d83]

The 1st question is, why do you need the 2nd control at all? Why not display it simply in combo box. An example:
You have a table tblComponentTypes: CType, CTypeName, with values like "RPR", "Ram Piston Rod"

This was just something that I thought would be a neat idea....trying to exercise my (limited) access skills!! I'll try both options and see how I get on!

The 2nd question is about subform. Is it planned, that you can register several parts of same type in it? When yes, then this design is OK (but probably you need a combo in subform to select a record there too instead cycling through all parts of same type in subform to find the wanted one). When not, then you don't neef Main Form > Subform setting at all - you can have a single form with unbound combo on same form to search and select a record.

Yes, it would be useful if there was the ability to register multiple parts (even better, parts of different component type). The key thing on the sub form is that once a part has been entered into the DB it then can't be changed/modified. To put it another way, I only want a combo box on the sub form to be displayed on the new record/s to be entered.

[ArviLaanemets]

Yes, it would be useful if there was the ability to register multiple parts (even better, parts of different component type).

A standard usage of Main Form - Subform setting. Example:
The subform's sfParts source is form fParts. The source of form fParts is table tParts: PartID, CType, PartName, ....;
The subform's LinkMasterFields = cbbCType, LinkChildFields = [CType];
The form fParts has a textbox txtCType linked to field CType and set not visible, and in case PartID is autonumberic, also invisible text box tctPartID linked to field PartID;
When you have selected some type in Main Form, e.g. "RPR", then the first record having CType = "RPR" is displayed. When there is more than 1 such records, you can select next/previous record from bottom of form (or you can select another record with CType = "RTR" from table tblParts. When you insert a new record into subform, a new PartID is inserted into txtPartID, and the value from combo in Main Form ("RTR") is inserted into field txtCType. When you save the record, those values are saved as new record into table tblParts.
When you select another CType in Main Form, in subform records with newly selected CType will be available, etc.

The key thing on the sub form is that once a part has been entered into the DB it then can't be changed/modified. To put it another way, I only want a combo box on the sub form to be displayed on the new record/s to be entered.

Simply write a Current event for fParts which restricts editing and deleting records (and/or sets for all controls except ones meant for navigation Enabled = No or Locked = Yes.) always, except for new record. When new record, the editing (and access to controls) will be allowed.

When user starts the new record and decides that it is not needed, then user can abort creating the new record pressing Esc a couple of times (when the record was not saved meanwhile).

[ssanfu]

Is the sub form for displaying data or for adding new records/data?

[ArviLaanemets]

Is the sub form for displaying data or for adding new records/data?

For both. When the record is saved, you see it, but you can't edit it. When you add a new record, you can edit it until you haven't saved it. Btw, the form needs a BeforeUpdate event too, which checks, are all needed controls for new record filled, and when not, then aborts saving the record.

I use similar design in IT-devices database I designed for our company. Editing records is not restricted entirely, but restricted until an <Unlock Record> checkbox is clicked. After that all controls on Devices form and Transactions subform are enabled, and editing of records in form is allowed. Same happens when a new record is added. Also a button <Save and Lock Record> is displayed which saves changes made to record, runs a couple of procedures after this, and then locks data editing in form and subform again. There are 2 reasons for this setup.
1. To avoid accidental changes to data (when you change some field, and select then another record, then by default Access saves the change);
2. To ensure that whenever a new transaction is added or some existing transaction is edited, those couple of procedures are executed. These procedures are calculating values for some fields in Main table (Bad! Bad! ), which are used for filtering and ordering the Main form (some current statuses for device, like current user, current owner, current userlevel - a characteristic which has structured value range and allows to group or filter devices by firm of current user, by country of current user, by the device being free/broken/scrapped, etc.).

[ssanfu]

I'll step back.....

Good luck with your project...