Column Width in Table

[jcbhydro]

Good Evening,

One of the Tables in my membership database is named [WaitList] and has a linked Form.
The Form is configured to pull data from another Table named [Groups]. The fields are [Group ID], [Group name] and [Leader].plus others from a second Table.
When the [Group name] is selected from a Form Combo Box it succesfully records the [Group ID] in the Form and Table, but repeats the [Group ID] in the [Group Name] column of the Table. I am convinced that the problem lies with the Column Width setting on the Property Sheet which is currently set at 0;2.54, but trying various combinations has not solved the problem.
The Row Source for the [Group Name] field is
; SELECT [Groups].[Group ID], [Groups].[Group Name], [Groups].[Leader] from [Groups] and the Column Count is set at 1 (2nd in Table)

I believe the [Leader] field is a different problem in that the Form correctly finds the ID and the Name but not the Leader. I have checked the property sheet for anomalies, but without success.
I have built this type of Table/Form relationship many times but have failed on this occasion.

Any suggestions would be extremely helpful.

jcbhydro

 

[CJ_London]

column count should be 3

 

[jcbhydro]

Many thanks for the rapid response.

Column Count 3 did the trick, but I can't fathom the count number!

Did you have any thoughts about the failure of the Form to pull in the [Leader] data from the same [Groups] table?

jcbhydro

 

[CJ_London]

Don't understand what you are trying to do​

 

[jcbhydro]

Thank you for your continued interest.
I will try to be more concise.
The [Waiting List] Form contains 6 fields in total and 3 of them are retrieved from the [Groups] Table.
The fields are; [Group ID], [Group Name] and [Leader]. The Group Name is entered by selection from a Combo Box which should retrieve the other 2 fields from the [Groups] table.
The [Group ID] field is retrieved, but not the [Leader]
The Row Source definition for the [Group Name] Combo Box is;
SELECT [Groups].[Group ID], {Groups].[Group Name], [Groups].[Leader] FROM [Groups];
All of that seems logical but it doesn't do the job.

Regards,

jcbhydro

 

[CJ_London]

column numbers start from 0 (but the bound column starts from 1)

so in your control, Group ID is column(0), Group Name is column 1 and Leader is column 2

This is three columns, so to see all three columns, the column count must be 3 and normally the bound column is 1 (i.e. Group ID)

setting column widths to

0 hides the first column
0;2.54 hides the first column (column(0)), sets the width of the 2nd column (column(1)) to 2.54cm and the third column to whatever is remaining.

so as you have is set up, groupID will be hidden, groupname will be displayed and when the dropdown is active, you will also see the Leader column as well.

If you want to see the leader value (other than in the dropdown), create an unbound textbox control and set it's controlsource to

=me.[group name].column(2)

 

[jcbhydro]

Hi,

Many thanks for the further suggestion.

However, the remaining problem is not what is displayed in the [Wait List] Table.
If random names are entered by hand into the [Leader] Textbox in the [WaitList] Form they are correctly displayed in the Table.

The question remains why is the [Leader] data not retrieved from the [Groups] Table by the Form when the Combo Box selection is made.

jcbnydro

 

[CJ_London]

sorry, you've lost me again - perhaps post a screenshot of your form

 

[jcbhydro]

CJ,

I am attaching a txt screen print of my Waiting List Form, I hope that this iss satisfactory.
As you will see the [Leader] field is empty for each record.
The Combo Box [Group Name] is meant to retrieve [Group ID] and {Leader] from the [Groups] Table. It achieves the former but not the latter.
Both of these fields are Text box type.

jcbhydro

 

[jcbhydro]

I have been very careless in ssending you a txxt ffile.

I am resending it as an xls file,

jcbhydro

 

[CJ_London]

unfortunately not a huge help - perhaps you can upload a copy of your db - remove anything sensitive

 

[jcbhydro]

Good Morning,

I have removed all the Reports and Queries from my file, but it is still more than 3.5 Mb.
Conseqquently, i have zipped it to about 11.5Mb., which should pass the upload query. The records have been deleted, apart from some fictitious characters.

Regards,

jcbhydro

 

[jcbhydro]

Apologies for another careless typo. on my part. The file length is less than 1.5 Mb.

Sorry to sound impatient! Have you spotted a reason for my missing data for 'Leaders' ?

Regards,

jcbhydro

 

[CJ_London]

you seem to be using excel thinking. You are also using lookups in tables which create exactly the sort of confusion you are experiencing.

the group members table only need the two ID columns, it does not need the name columns (use combo boxes in the forms)

the groups table does not need a lookup for leader (you do this in the form) and the Leader field should be type number (long) and called LeaderID (and will be a combobox in the forms)

the waitlist table should be using the id fields and the groupname and leader fields are not required. You do not need the member name columns, and I would expect the date added column to have a default value of date() or now()

I do not have the time right now to do the redesign and modify your tables and forms, but will see if I can do something in the next day or so. In the meantime, suggest modify your tables as above and try redesigning your forms accordingly.

Note when you are recreating your forms, the memberID field (for example) will come through as a textbox. Right click on the control and select 'change to' then combo. Then you can complete the rowsource there that you previously had in your table

 

[jcbhydro]

Many thanks for your comments/suggestions.

This is an inherited database, so it came with lots of warts, which I am trying to eradicate.

However, I wouldn't want you to waste your time on redesigning tables, etc for my benefit.

I'll see how i get on based on your suggestions.

Regards,

jcbhydro

 

[jcbhydro]

Hi CJ,

Looking at your suggestions, I am uncertain that these will achieve the required result.

I will explain the rationale for the structure of the Groups Table/Form.
The Groups Form is utilized by a user to enter new data for a Group ID and for a new Group Name. The Group ‘Leader’ Name is selected from a Combo Box of all members.
The date field is not a ‘now’ date but one which is entered later on the occurrence of a specific Group related event.
The Group Table is intended to replicate this data for use in numerous Queries and Reports.
If the Table field ‘Leader’ is defined as a number field as you suggest, it merely displays the Member ID of the Leader. What I need here is a Name not an ID, which I achieved by defining ‘Leader’ as a combo box in the Table. Maybe that is not correct, but the result iprovides what I require.

The Groups Member Form is used to define which member belongs to which Group. Thus a user selects a group from a Group Name combo box, causing the Group ID to be displayed. Similarly, a member’s name is selected from the Member Name Combo box and the Member ID is displayed. These 4 fields are thus displayed in the Group Member table.
If the table is restricted to the 2 ID fields as you suggest the names are not available for selection in the form. To the database user the IDs are meaningless, so using these as selectors is not feasible.

The same principals have been used in the structure of the Wait List Table and Form, such that selecting a Group and a Member from relevant Combo Boxes should result in the Table field names as defined.

Please don’t allow my problem to impinge on your more important activities, but your comments will be very welcome when and if you can find time.

Regards,

jcbhydro

 

[CJ_London]

You are confusing what you see in the tables as what you will see on the form. If you store an ID you can show the name with a combobox on a form.

Regret I don't have time to explain in detail, you need to read up on normalisation and relationships and understand the principles. Also, try a few of the access templates to see how they work. You tube is often a good place to look for some tutorials or you can try

http://mapdataconsulting.com/tutoria...ding_page.html

not tried it, but the login name and password is access5252
44Access

see this link for the author

 

[CJ_London]

You are confusing what you see in the tables as what you will see on the form. If you store an ID you can show the name with a combobox on a form.

Regret I don't have time to explain in detail, you need to read up on normalisation and relationships and understand the principles. Also, try a few of the access templates to see how they work. You tube is often a good place to look for some tutorials or you can try

http://mapdataconsulting.com/tutoria...ding_page.html

not tried it, but the login name and password is access5252
44Access

see this link for the author
http://www.accessforums.net/tutorials/screencast-video-tutorials-combo-box-series-others-52741.html