Forms using linked tables - best approached

[rtcary]

I have helped an organization "clean up" their data by creating an online application using MySQL and PHP. Since this type of environment is outside their capabilities, my goal is to create an Access DB, however my experience with Access is limited and would appreciate some advice.

The DB consists of a main table (Clients) and two linked tables (1:many), though the relationships are not fully normalized due to the initial "dirty data". To keep things simple all output will be done via queries; it is the creation of an input for for Clients (Add entry) where I need some advice. In PHP, the User is presented with drop-downs for the two linked tables along with validation that selections were made. Can this be done with Forms in Access? If so, is there an example of how to set it up?



There is a fourth table, Sales, that links to Clients where the Clients table would be presented as a Query with the User required to select a Client (the linked table data would be shown). Can this be done with coding (that would be to complicated for the organization)?

With appreciation...

Rtcary

[CJ_London]

In PHP, the User is presented with drop-downs for the two linked tables along with validation that selections were made. Can this be done with Forms in Access? If so, is there an example of how to set it up?

yes it can, no examples that I know of - all depends on what you mean by 'validation that selections were made' - might mean changing the family key in one record, or updating a flag or something else

[rtcary]

On the Client form, the two drop-downs have the top item "*Select*". When the record is about to be posted, if that item is the value, the post is rejected.

Todd

[rtcary]

Now that I have poked around Forms a little I'm getting an idea as how one sets up the Form for a Table; Clients in this case. What I do not know how to accomplish is setting up, lets say, a second table that lists all of the possible choices for the type of Ad the customer has linked to him. There must be a link and it can be changed during edit.

Should a lookup relationship be used?

Todd

[ssanfu]

I would use a combo box. You can use a query for the control "Row Source".

[CJ_London]

rule is one table, one form. So if you have two tables, clients and ads, and one client can have many ads then you would have two forms - a main form for the client and on that form a subform for ads. To represent the relationship you populate the subform linkchild and linkmaster properties - the clientID in the ads table and the clientID in the client table. If you have created a relationship in the relationships window and enforced referential integrity, these properties will be populated automatically

[rtcary]

My apologies for not being clearer. A client can have one and only one Ad, however during an edit of the Client record, the Ad may be changed. In my PHP version, I have a list of possible Ads.

Unfortunately, I cannot have referential integrity set since the data from previous iterations was not "clean". By next year, it should be cleaned up.

Todd

[CJ_London]

A client can have one and only one Ad, however during an edit of the Client record, the Ad may be changed

then ssanfu's suggestion is the one to go for.

[rtcary]

Thanks! I'll play around with that.

Rtcary