Combo boxes in access

[Blasting Cap]

I have an access table that has optional values in it for four separate
fields. They are related as to the last 4 large items a person in the
table has purchased. The four fields are repeated 4 times.

In it the 4 fields are:

Property1 - a yes/no field (checkbox)
Property1Action - I want this to be a combo box that will populate with
the value of what's in the database for Property1Action if the Property1
field is filled in in the database.
Property1Number - the item number, a textbox
Property1Date - a textbox date field.

I want to fill in the Property1Action field if there's something in the
field on the database, but for new entries, I want it to prompt me with
a combo box to select what's in the field (it'll only be 1 of 2 values).

I don't have a lot of experience coding in Access, so I am sure that I'm
just overlooking something.

Can someone give me an idea as to how to populate Property1Action box
with the data in the database, yet to have it be able to select one of
two options in a combo box? I may also want to change these combo boxes
if they purchase something new, i.e. to "bump down" property1 to
property2, etc. Everything I've done thus far only has the combo box
give me one entry below it, and it's not populated with anything.

Any help would be greatly appreciated.

BC

 

[tina]

suggest you forget about forms for the moment and concentrate on structuring
your table(s) properly. repeating a group of four fields multiple times in
the same table is "committing spreadsheet". as MVP John Vinson says, "you're
using a relational database, so use it relationally". for more information,
see http://home.att.net/~california.db/tips.html#aTip1.

hth

 

[Blasting Cap]

I hadn't decided to do that - yet. The additional 3 popped up at the
last minute, and it was easier for me to insert the fields in the access
table than to do it right. I was going to leave the 1-4 yes/no boxes in
the original database.

When I do that, how do I then populate the boxes?

I know that once I put a checkmark into the first box, that I want to
add a record to this new table (fields would be the userid, the action,
the item number and the date). I've still got the problem of populating
the combo box with whatever is in the new table, and then only
conditionally displaying it when the box is checked.

Thanks for the link - I'll do some reading on it.

BC

 

[John Vinson]

I hadn't decided to do that - yet. The additional 3 popped up at the
last minute, and it was easier for me to insert the fields in the access
table than to do it right. I was going to leave the 1-4 yes/no boxes in
the original database.

When I do that, how do I then populate the boxes?

Do it quick... or do it right.

Take your pick. I'd suggest doing it right.

John W. Vinson[MVP]

 

[Blasting Cap]

I've got it done.

I set it to match the record number of the original database, since I
can have duplicate numbers in the property numbers, but cannot have
duplicates in the customer numbers.

So now, rec#1 in the Property database has these fields:
Property1Action
Property1Number
Property1Date
Property2Action
Property2Number
Property2Date
Property3Action
Property3Number
Property3Date
Property4Action
Property4Number
Property4Date

plus an ID field, that should match the id field of the original database.

How can I populate the combo boxes so that they can be edited/changed
when there is an existing record there, or show all the choices I want
for them when there isn't an existing record?

Ex -

Cust #1 has bought Property 123

So, in the main table, there'd be a check by Property1, and in the above
table, there'd be entries in Action, Number & Date for Property 1. If
he sold it, then I'd want to note that, by being able to edit the info
in the table & copy the Property 1 info to Property 2, and enter the new
stuff into Property 1.

Anyone have an idea how to do this?

 

[John Vinson]

I've got it done.

I set it to match the record number of the original database, since I
can have duplicate numbers in the property numbers, but cannot have
duplicates in the customer numbers.

So now, rec#1 in the Property database has these fields:
Property1Action
Property1Number
Property1Date
Property2Action
Property2Number
Property2Date
Property3Action
Property3Number
Property3Date
Property4Action
Property4Number
Property4Date

Sorry.

THIS DESIGN IS STILL WRONG.

You have a Many (cuatomers? I don't know your business) to Many
(properties) relationship.

The correct way to store this is with THREE TABLES: Customers, primary
key CustomerID; Properties; and Actions.

The Action table would have fields

CustomerID; Action; PropertyNumbre; ActionDate

If one customer buys a FIFTH property - you just add a fifth record to
thist table. With your non-normalized, repeating fields, you're in
deep trouble - your table cannot HANDLE a fifth purchase.

plus an ID field, that should match the id field of the original database.

How can I populate the combo boxes so that they can be edited/changed
when there is an existing record there, or show all the choices I want
for them when there isn't an existing record?

Since I have no way to know what the source of the combo boxes might
be, nor even what fields might have combo boxes, it's more than a bit
difficult for me to answer.

Ex -

Cust #1 has bought Property 123

So, in the main table, there'd be a check by Property1, and in the above
table, there'd be entries in Action, Number & Date for Property 1. If
he sold it, then I'd want to note that, by being able to edit the info
in the table & copy the Property 1 info to Property 2, and enter the new
stuff into Property 1.

Simply ADD A NEW RECORD to the table if they buy a new property.

Anyone have an idea how to do this?

By properly understanding the concepts of normalization, for
starters...

John W. Vinson[MVP]

 

[Pat Hartman \(MVP\)]

Sorry, the structure is still not right. The table structure should be:
UniqueID (autonumber primary key)
PropertyID (foreign key to join back to main table)
PropertyAction
PropertyNumber
PropertyDate

With this structure, you can have any number of instances of actions. You
are not limited to four. Keep in mind that once you have more than one of
something, you have MANY and many ALWAYS belongs in a separate table. You
may enforce business rules that limit the number of occurances to some
particular number but the database engine will not impose that type of rule.
Use a subform to display the actions and you will not have to worry about
how many are entered.

 

[Blasting Cap]

I've taken your suggestion on the Property database. The CustomerID
would correspond to the autonumber on the customer table. The reason I
held it to 4 was because that was all the history the person wanted for
any given customer.

The new fields would be:
id
CustomerID
PropertyAction
PropertyNumber
PropertyDate

so each "transaction" would get a new number, but I could have many
different customerID's and Property numbers in the table.


I'm still not understanding though what has to take place though on the
section where I display the properties. What I know I want to do is for
a given Customer number, display the 4 most recent property transactions
in date order (newest first). That way would be the easiest and most
relevant if I wanted to retrieve just the top 4 for display.

If Customer1 has only one transaction, I would want it to display in the
box below his contact info the following:

a checkmark in Property #1 checkbox, Property 1 Action = whatever he did
(bought or sold it) - in a combo box (this may not be the best way to do
this). Property Number, and Property Transaction Date. If they
bought/sold something new, I'd want to be able to put a checkmark in box
2, then have the Property 2 action give me a combo box with 2 options
(bought/sold) in it, and have the Property Number be a free-form text
field, and property date. When Customer1 is brought back up, the most
recent transaction would be first.

If Customer1 has only 1 property transaction, I'd want to display just
that one.

On an add, I'd want to add this new customer number to the Property
database, in addition to all the other info (Action, number & Date).

On your suggestion below, I do not know why I'd need the properties
table. I could have the same property show up for multiple customers.
Unless I am thinking of it incorrectly, what property number I have
should not matter to any of them.

BC

 

[John Vinson]

On Sun, 14 Jan 2007 09:45:04 -0500, Blasting Cap

Answers inline.

I'm still not understanding though what has to take place though on the
section where I display the properties. What I know I want to do is for
a given Customer number, display the 4 most recent property transactions
in date order (newest first). That way would be the easiest and most
relevant if I wanted to retrieve just the top 4 for display.

You can use a Subform on the Customer form, based on this new
multirecord property-transactions table (NOT on the Properties table).
You would use a Query sorting the transactions in decreasing date
order; you can either make the subform just big enough to show four
(and let the user scroll down to see more, I can't see any reason not
to do so), or - if you really want to conceal all earlier transactions
from view - use the Top Values property of the query to restrict it to
four records.

If Customer1 has only one transaction, I would want it to display in the
box below his contact info the following:

a checkmark in Property #1 checkbox, Property 1 Action = whatever he did
(bought or sold it) - in a combo box (this may not be the best way to do
this). Property Number, and Property Transaction Date. If they
bought/sold something new, I'd want to be able to put a checkmark in box
2, then have the Property 2 action give me a combo box with 2 options
(bought/sold) in it, and have the Property Number be a free-form text
field, and property date. When Customer1 is brought back up, the most
recent transaction would be first.

What's the point of the checkbox? Just use the very EXISTANCE of the
transaction record. Either it's there on the subform, or it's not
there on the subform. What does the checkbox convey?

If Customer1 has only 1 property transaction, I'd want to display just
that one.

Exactly... that's what the subform will do. If they have one
transaction, it will show that one transaction; if they have four, it
will show four; if they have eleven, you can scroll down and see all
eleven (or suppress them as noted above).

On an add, I'd want to add this new customer number to the Property
database, in addition to all the other info (Action, number & Date).

The Form and Subform will allow that to be done with ZERO programming.
Just bind the main form to the Customers table, and the subform to the
transactions table.

On your suggestion below, I do not know why I'd need the properties
table. I could have the same property show up for multiple customers.
Unless I am thinking of it incorrectly, what property number I have
should not matter to any of them.

Since you have not said what these "properties" are, or what the
property number means, I don't know; but if this is (say) real estate
parcels, you could use a Combo Box on the subform bound to the
property number and storing the property number, but displaying for
view the property address, or the parcel number, or whatever attribute
in the properties table are meaningful to the user.

John W. Vinson[MVP]