Forms and Controls

[brian4167]

I am new to Access and having a problem. I created a database using several different tables. All tables are linked through a primary key on each table. I created a form and everything went well, until I realized that I forgot to select a few fields through the Forms Wizard that I wanted represented on the Form. However, I am finding it difficult to go back and add the control as it does not appear on the Field list. Can anyone help me out in telling me how to add a field to a field list? Thanks in advance!

 

[SOS]

Welcome to AWF...

To add a new field to the form, open the form in design view, go to VIEW > FIELD LIST and then you should be able to drag and drop onto your form. If the field is not listed in the list then you may have used a query for the form's recordsource and you will have to open that query in design view and add the field in first, save it, and then it should be in the list.

 

[brian4167]

I created the Form with the Wizard, so I was able to select the fields (from the Wizard) that I wanted from the Table - not all of the fields from the Table populate in the Field List. This is where my problem is. The Form is created from a table, not a query. Ideas?

 

[SOS]

I created the Form with the Wizard, so I was able to select the fields that I wanted - not all of them populate in the Field List. This is where my problem is. The Form is created from a table, not a query. Ideas?

If the field is in the table is should be in the field list, if the Record source of the form (select the form in design view and look at the Record Source) is the table then it should be there. If, however it is something like SELECT ... then you will need to click the ellipsis to the right of the space where the Select statement is and it will open the query. You can then add the field you need from the table and then close the query and select YES when it says "Do you want to save ...etc."

 

[brian4167]

Ok, that worked... Thanks a lot for the assistance! Now, one more question, now that I have worked with the Record Source for my form, it appears that not all data is pulling over from the tables. I have 6 tables that are linked to this Form. My question is, how do I make sure that all 6 tables are listed in the Record Source section in the Property Sheet?

 

[SOS]

6 tables? What are they and how do they relate to each other? You aren't likely going to join 6 tables all in one form. You will probably need subforms to do the work, but it all depends on what the structure is.

 

[brian4167]

I have 6 tables (Pharmacy Data, Pharmacy Admin, Reinstatment, Comments, Exclusions, Dates, & Notice) and they're all set with KEY ID, which is the Pharmacy ID.

 

[SOS]

I think we definitely need to visit the structure of the tables and the relationships. It looks like you might have some one-to-many items going on here which would require the use of a subform or more.

 

[brian4167]

Ok, I am a little lost right now because even when I try to build a query and run it, I don't get any returned data. It seems like the tables don't get their relationship.

 

[SOS]

Do you have the relationships set in the Relationships window? If so, can you post a screenshot?

 

[brian4167]

It won't allow me to print screen/copy/paste.

 

[SOS]

It won't allow me to print screen/copy/paste.

You can open Word, and paste there. Zip the word file and upload.

 

[brian4167]

Ok, see if this works.

 

[SOS]

Ok, see if this works.

nothing here. Did you zip the word document? If so, it has to be under 785Kb in final zipped size. You can upload several attachments, each 785Kb or under but they need to be equal to or under 785Kb.

 

[brian4167]

Ok, try this...

 

[SOS]

First thing is that you have PharmacyID as the primary key in each table. This is not correct. You need to have a primary key for each table but the PharmacyID should only be the primary key in the PharmacyData table (I would remove spaces from table and field names).

Then PharmacyID would be in the other tables but as a FOREIGN Key. It would be a long integer datatype and not autonumber, whereas the PharmacyData table would have PharmacyID as an autonumber but only that table.

So you would have something like:

PharmacyData
PharmacyID - Autonumber (PK)
Business
General
etc.

Comments (do not use special characters like ' in field names)
CommentID - Autonumber (PK)
PharmacyID - Long Integer (Foreign Key)
CommentDate - Date
Comment

Notices
NoticeID - Autonumber (PK)
PharmacyID - Long Integer (FK)
etc.


And so on...

And you then link PharmacyID from PharmacyData with PharmacyID in the other tables. Currently you have it now that only one record could exist for each Pharmacy in each of the other tables.