Trying To Update Foreign Key..... Help Appreciated!

[SheetsERR]

Hey there, folks!

I'm a relative novice in MS Access. I certainly would appreciate some guidance - thanks in advance!

I've got a 5 table/3 form db. It's not big stuff, but when it's done, I'll have a happy camper. Anyway, when one record is completed on my main form, a like entry is input to the main table of the database. The PK of the main table links to a FK in another table. The PK just happens to be of the Autonumber data type. I can't, for the life of me, figure out how to get the primary key to link to/update the foreign key in the other table.

Note: The primary key is not on the db's main form. I don't really want the user to see it. It is entered when a record is completed in the main form/table.

As I said, I would be most grateful if someone could shed some light here. I know a good bit about SQL Server admin, so I'd be willing to return the favor by fielding ?'s in that area.

Thanks much!
SheetsERR

 

[LittleSmudge]

So lets get this in some sot of order.

'Potentially Happy Camper' fills in the MainForm which is BOUND to the Main Table and has an AutoNumber primary key.

Well there's no problem there ( Lets NOT get into the debate about how AutoNumbers are not appropriate as Primary keys )

The problem lies in the fact that you now want to get this Primary Key value into the Foreign Key field in another table. - So what you will have then is a record in this second table that JUST has it's own PrimeKey and this ForeignKey - all other fields are blank !

WHY -oh WHY would you want to do this ?

Surely the time to do this would be when you are putting the rest of the data into the second table. Then all you need to do is find the MainTable prime key and put it into the FK field ( You could use a combo box to do this )


- So now explain why I'm on totally the wrong track Sheets and we'll take it froom there.. .. ..

G LS

 

[SheetsERR]

Thanks for the response. I suppose I need to throw a little more detail your way. I could be missing something (probably am). When I go home, I'll get it together and repost.

SheetsERR

 

[LittleSmudge]

Okay Sheets - and I'll have a look at it afresh after the weekend - enjoy.

G LS

 

[SheetsERR]

LS!

How are ya? If you can shed some light here, I'd be very grateful. I guess I didn't lay it out too well last time. Here's what I'm trying to accomplish....

This database will be used to track nursing home info.

Two tables of key concern are:
tbl_Home (the database's main table)
tbl_Staff

tbl_Home:
BusinessUnit - text ID field entered by user (PK)
Community
State
Status
StaffID
etc...

tbl_Staff
StaffID - autonumber PK
FirstName
LastName
DateofBirth
etc...
NOTE: tbl_Staff has already been populated with data.

There are a few other extraneous tables, but there's no need to bore you with their detials.

Frm_Home is the database's main form. The form contains mostly fields from tbl_Home. However, it does have a combo box that allows the user to select the accountant in charge of the home. This Accountant field is a concatenation of FirstName + LastName from a query based on tbl_Staff. As far as I'm concerned, this is an OK arrangement.

I would like to construct a database that is linked logically and is as normal as I can get it, but like I said before, I'm no MS Access guru. When a user enters a record via frm_Home and selects the home's accountant, I'd like the StaffID value from tbl_Staff to sync with (populate) the StaffID field in tbl_Home. Frm_Home itself does not contain the StaffID field.

Does this make any sense at all?

Thanks much!
SheetsERR

 

[LittleSmudge]

First off - a bit of advice from the weatherworn..
tblStaff has primary key StaffId - that's GREAT. Prime Key = name of table with "tbl" dropped off the start and "Id" added to end is very clear and helpful.

However, having StaffId as Foreign Key in tblHome is not so good.
In all Foreign Keys replace "Id" with "Ref" because the FK "Ref"ers to the PK's Id.
This makes debugging and maintenance LOTs easier.


Now, the other problem I see is that, as it stands at the moment you can store the StaffId from tblStaff into the tblHome. THEREFORE you can have MANY tblHome records that have the same StaffId stored in them, but only one per record. Therefore ( in English ) each Home can have a max of ONE member of staff and that member of staff can be allocated to many Homes.
( I must ask he question ) Is that what you want ?

Until this is cleared up / explained - I don't think it will be helpful for me to go further.

I look forward to your answers.

G LS

 

[SheetsERR]

LittleSmudge,

Correct you are! Each staff member can be assigned to more than one home. Each home can have only one staff member assigned to it. Hence, a one-to-many should exist from tbl_Staff.StaffID to tbl_home.StaffID.

By the way, thanks for the advice on the FK verbiage. I will incorporate it in my naming from here on out. I appreciate any advice that comes my way!

SheetsERR

 

[LittleSmudge]

All becomes clear to me now ( I think ).

On the frmMain you need a combo box ( No surprise there )

Call it cboAccountant
ControlSource = StaffRef ' Bind combo to the field in tblMain
RowSourceType = Table/Query
RowSource = SELECT [StaffId], [FName] & " " & [SName] AS AccName FROM tblStaff;

(This 'combines' First and Last name with a space between them - Is that okay ? )

ColumnCount = 2
ColumnWidths = 0
BoundColumn = 1




QED - at last.

G LS

 

[SheetsERR]

LS,

Thanks for the advice! When I get home this eve, I'll dig in and get back to you with the results. Hmmmm... suppose I should bring this db to work. I could look at it over lunch.

I'm confused, though. I do have a combo box in the main form (frm_Home). When a record is entered in the main form, the accountant is selected. Likewise, the main table is also updated with the concatenated accountant. How can I get that autonumber PK that is associated with a particular accountant to populate when the accountant selection is made? The accountant field gets populated in the main table, but the StaffID does not come over from tbl_Staff.

Well, like I said before, I guess I'll compare your response with what I have at home. Need to make sure my i's are dotted and t's are crossed.

Thanks again,
SheetsERR

 

[LittleSmudge]

I think your last comment is an 'i's and 't's issue.


I've changed the text below from what you originally wrote - to what I believe should be happening.

I do have a combo box in the main form (frm_Home). When a record is entered in the main form, the accountant is selected. Likewise, the main table is also updated with the accountant's PK value. How can I get that autonumber PK that is associated with a particular accountant to populate when the accountant selection is made? The accountant field gets populated in the main table with the StaffId ( not the name text ), SO the StaffID does come over from tbl_Staff.

On the main form the combo box which is bound to the StaffRef field in tblHome does the lookup and works out the concatenated text string automatically every time you look at a new record.


'ope-that-'elps.

G LS

 

[SheetsERR]

LS,

I went home and gave your suggestion a try. Unfortunately, I'm still having trouble. My design skills just aren't where they should be.

Currently, my main form has a combobox called Accountant that concatenates the FirstName and LastName fields from tbl_Staff. The combobox is based on a query.

Accountant is also a field in my main table, but it is not an FK. The combobox is bound to it, though. Hence, the field gets populated as soon as the user selects an accountant with the combo box on the main form.

Do you see why I'm using an autonumber PK (tbl_Staff.StaffID) for each accountant in tbl_staff? First and last name are not enough to uniquely identify an accountant. Anyway, ss I said, the Accountant field in the main table is getting populated with data. I need to figure out how to get that darn autonumber from tbl_Staff over to the main table when a user finishes data entering a record. I have a StaffID (changing to StaffRef soon) FK field in the main table as a Number/Long Int. I just can't figure out how to get data into it.

Am I just thinking about this in a bass ackwords way? I'm going to start climbing the waals soon!

Again, LS, I really appreciate the time you've taken to lend a hand! Eventually, this will become clear to me.

SheetsERR

 

[LittleSmudge]

Hi Sheets. - I'm back after a long weekend away.

You said in your last post
Accountant is also a field in my main table, but it is not an FK
I assume you mean the tblMain!StaffRef which is of type LongInt - This IS the FK ( Any field that takes the value of another table's Primary key in order to link a record in one table to records in another is a Foriegn Key. )

Given that you are ( or intend to be ) storing the Accountant's StaffId in the main table's StaffRef field you should not ( Must Not ) store the Accountant's concatenated name in the main table as well ( just in case you are doing ).


See the suggested format of the combo box in my forth post.


( As you'll gather from this disjointed post - I'm not clear on exactly where your problem is. All we've talked about so far will work - if I've interpreted what you're saying correctly. ) So I'll be expecting more clarification about where I've missed the point !

G LS

 

[SheetsERR]

Hey, LS!

I think the fog might be clearing a bit - we'll see. Well, that could be the beer I just drank.

Thanks for the advice. I'm going to give it a good college try this weekend. I'll let you know how it goes.

Have a good 4th!
SheetsERR

 

[SheetsERR]

GL!

This has been a riddiculous venture, but with your help, I've finally gotten it to work. You nailed it right on the head.

Thanks,
SheetsERR

By the way, I know SQL pretty well. Do you know of any books that hit on the difference between Access SQL and run-of-the-mill ANSI 92 SQL? I trust your opinion. Believe it or not, I'm finding it pretty damn difficult to skip between SQL Server and Access.

 

[LittleSmudge]

I'm not aware of a 'good book' on the issues. However, as I'm working on developing in the other direction Access to SQL Server then I'd be interested if you do manage to find something useful.

Well done with your success.


G LS