Access 2010 Concepts

[zipmaster07]

Hey guys, hoping some people out there can help me with a few dilemmas I've been having. I've been using MSSQL and MySQL quite heavily for 3 or 4 years now, so i'm pretty familiar with databases and database administration in general. However, a client wants me to create an Access database for them and I've been struggling with it for a few days now.



Here's what I've got. they had a database (by database i actually mean one table in a database) that they want "enhanced". They want a central location to enter all their data, eliminate as many duplicate entries as possible, and be able to pull some useful data out of it. Easy enough, seeing as this is what databases are generally for.

I've been able to create the tables just fine. I've got typical customer information, state, contact type information, and there own specific data all created in the necessary tables. I'm having a problem with the data entry aspect of Access. I essentially/ultimately want a flow chart style data entry experience. You start with one form, it asks several questions from which you pick what you want to do. This would then bring you to another form where you could enter data. This second form might also lead you to another form which would pull some of the data you just entered from the last form. At the end of the forms you would have all the data you needed in all the corresponding tables. So instead of having to go to each table individually, finding and copying ids, remembering default values, looking up dates, etc, it just gets handled through the forms. For example:

The first form might ask:
Do you want to Add A Customer?
Do you want to Add A Contractor?
Do you want to Add An Order?
Do you want to Edit....?

If you choose to add a customer it would then bring you to another form that asked for that customers information. From here you would be taken to the Orders form. This form would pull data from the Customers form (the one you just filled out) such as the newly generated customer_id.

If you already have a customer in the database but simply want to add an order to that customer (so you already have a customer_id) then you can go straight to the Orders form and manually enter in the customer_id, instead of it pulling the data from a form you just filled out.

So here is my actual question -- Can Access do this? And more specifically, can Access do this easily. Another way of asking it is, do you have to bend over backwards to get Access to do this, are you essentially making the program do something it wasn't really made to do (and it's fine if this is the case)?

This is really all I want answered at this time (doesn't mean I won't have more questions later ) but let me explain some of the problems I've been having (I won't go into too much detail).

1) I'm not able to get data you just entered in one form to be pulled into the next. Is this best done through macro's or through code?

2) When I initially create forms Access seems to like to list all the data already in the table. In my opinion it isn't very user friendly when it comes to entering data. Remember, I'm trying to setup a batch/single data entry style of setup. So the data I enter on the form gets added to the table after I'm done entering it, and then if you want to view the data just go and look at the table itself (or I guess I could create another form just for looking at data). I don't want to see current data and have the ability to enter new data. I want the functionality separated out. Is this also something access can easily do?

3) I'm having a hard time getting Access to do something based off a certain event in one table and update/insert/do something else in another table. I've tried using both VBA and macros to accomplish this but the typical response is that it can't find the "other" table. Here's the error from the macro I setup:

cannot find the name 'lead' you entered in the expression. You may have specified a control that wasn't on the current object without specifying the correct form or report context....

I'm not trying to reference a form or report, lead is an actual table that i'm pulling data from as a parameter in a macro. How can this database not find it's own tables (it even pulls it up in intelisense when I put the table in the parameters field)

Anyway, I've probably gone on long enough. I'm hoping somebody has some good general advise on this type of situation. If not, oh well I guess I tried (back to an RDBMS systems I guess).

[pbaldy]

Generally speaking I don't see anything that Access can't do relatively easily.

1) Either probably, though I'd use code. If the data is saved you can do this:

BaldyWeb wherecondition

Or you can simply push data from one form to another:

Forms!TargetFormName.ControlName = Forms!SourceFormName.ControlName

2) A couple of ways to go. Forms have a Data Entry property, which will open the form without displaying existing records. There is an argument of OpenForm that can open it that way, so you can use a single form for data entry or editing existing forms.

3) It's not hard to do, so you've probably used the wrong syntax (don't know as you didn't post what you tried). Generally to work with data from code you'd use a recordset or execute SQL.

[zipmaster07]

thanks, I'll try making these modifications.

[pbaldy]

No problem, and welcome to the site by the way. Post back if you get stuck.

[zipmaster07]

Thanks pbaldy for your help, i did run into a small conundrum:

I've got a customer table that has typical contact and address information including state. This state field has a 5 character limit and it stores an ISO standard (typically confused with postal standards, but they're actually different - besides the point though).

All the states are listed in the state table which have the following columns:
- state (typical postal code standards, CA, UT, TX, etc)
- iso_3166-2 (This is the ISO 2 character standard for the entire world) - primary key
- description (description of the state. A.K.A. the full name of the state)
- country (The country that corresponds to the ISO 3166-2 code)
- postal_code_format
- phone_format
- iso_3166-1 (This is the ISO 3 character standard for the entire world. It only lists countries. Not their state/provinces/regions/etc)

customer.state is a foreign key on the customer table to state.iso_3166-2.

On a form to create a new customer I want to list all the states in a combo box, but I don't want to list their ISO standards, I just want to list their descriptions (and maybe the value of state.state) so the client doesn't have to know the ISO code of that state/province, but so the actual ISO code is still stored on the customer table.

I've been able to create the combo box just fine, but when I choose a value, like Florida, it says that:

The field is too small to accept the amount of data you attempted to add

This makes sense, the customer.state column only allows 5 characters and "Florida" is obviously longer then 5 characters. How do I get the combo box to only display "Florida" and/or "FL" but store the actual ISO standard (in this case "US-FL")

Also, for my Row Source of the combo box, I've used the following query which does work, it stores the data in the customer table correctly and display the correct information on the combo box:

SELECT state.[iso_3166-2], state.[description], state.[state]
FROM state
ORDER BY state.[description]

However, I can only click on state from the combo box drop down, I can't manually type in the state, because it is matching what I type to the iso_3166-2 column, which brings me back to the problem of having to know the actual ISO standard. Ideally I want to be able to both select from the list and manually type in my state, either the full name of the state (state.description) or just the postal code standard (state.state).

Any help would be appreciated.