Duplicate data into subform and automatically fill fields

Question

Hello,

I've trying looking in forum and all but never found a reply that I could apply/undertsand because I'm a real beginner to Access 2010.

I have 3 tables:

One called "department managers & names" with 2 fields: "department" and "department manager" which are related. For example, Mr X manages dpt PROC_BUC. 

One is called "Requested bids" and has several fields: "department" and "department manager" are here as well, and in addition to that we have "bidding number" and "product to purchase".

One is called "received offers" and also includes the fields "department" and "department manager", "bidding number", but with more details like bidding company names, prices, etc.

I created a form linked to the "requested bids" table with all the fields from the table and a subform (as a table format) related to the "received offers". 

Since I need to update this data daily with the newly received offers, the first thing I'm trying to do is to have default values in certain fields of the subform. 

For example, since the "department manager", "department", "bidding number" and "product to purchase" will always be the same since they are related to one bid in particular, is it possible that every time I add a new record on the subform, this data is automatically input so that i won't have to type them all the time? 

Another thing please : in the main form, I created a combo box for the field "department manager" which takes its data from the table "depatment managers & names". There is also a textbox which is linked to the "department field". What I would like to do please is thta when I choose a manager name from the combobox, the name of the department (in field "department") is automatically input. I've read things about some afterupdate event but I don't know at all how to do it.

Sorry for the long message, I would be very grateful for you help.

Thank you in advance

Answer 1

PK =Primary Key

Autonumber is an Access datatype that generates a unique ID with each record.

FK = Foreign Key

You NEED to understand these terms before you do anything more. Access is a relational database If you are not going to use it relationally then go back to Excel, You need to learn more about relational databases before you start entering data. The foundation of a efficient database is a proper design. You need to get that down first. One to one relationships are rare, they have a place but I don't see them nuch with your database,

Answer 2

Well, actually I have a proplem since I deleted the field "department" in my "bid" table, I can't input it when opening a new request in my form (and I need to fill this part so that my colleagues and boss know that this department requested this bid). Is there any way to fx it please?

Thanks.

Answer 3

Hello,

Thank you both for your reply and advice. I  deleted spaces and duplicates as recommended.

Scottgem: my workflow is that throuhout the day I will receive new offers from bidder (companies) and that I progressively input the information on the received offer related to a particular bid (can have several bids at the same time). Thing is, my colleague does the same and we work on a shared excel file, which is horrible because it has lot of bugs and began to duplicate some rows data to other rows, deleting the information already input there. So I wanted to have some nice access database where my colleague and I could work simultaneously (we never work on the same bids).

However, sorry but I didn't understand these parts (I'm a real beginner and some concepts are blurry):

(PK Autonumber)

(FK) - field key?

And relationshipwise, I seem to manage only "one to one" relationships now that there are no duplicate fields. Is that ok to make things work?

Thanks

Answer 4

I think part of the problem is that you're trying to store the Department Manager redundantly in the Requested Bids table.

In addition to Scott's usual excellent suggestions, you need to change your table design. Relational databases use the "Grandmother's Pantry Principle": "A place - one place! - for everything, everything in its place". The department manager's name should be stored once, and once only, in the Personnel table that Scott recommends; you should NOT store it anyplace else. If you have proper tables and relationships, you can use a Query joining the bids table to the department table, and then the department table to the personnel table, to LOOK UP the stored manager name. You neither need nor want to copy that name into the bids table!

Answer 5

First, don't use spaces  or special characters in object names, it will come back to haunt you. Second you should probably have a separate table for employees and for departments and a third table for DeptMgrs:

tblDeptMgrs

DeptMgrID (PK Autonumber)

DeptID (FK)

MgrID (FK)

Next, I'm not clear what your workflow is. Are you requesting bids from suppliers and received offers are what the suppliers come back with? If so, then your Bids table should look like this:

tblBids

BidID (PK Autonumber)

info about the bid

tblOffers

OfferID (PK Autonumber)

BidID (FK)

info about offer

Your subform is then linked tot he mainform on BidID and BidID is automatically populated. You do NOT need (and shouldn't) fill in anything more from the Bid in the offer table than the BidID.