auto populate issues and unlimited fields, newer than newbie.

[kstarnes]

I have (sort of) built a database and I think worked out the normalization issues. Its for a county council and is designed to keep track of the contracts they have going for projects. Two departments work on the database, the department that keeps up with the contractor (who they are, what they do, who the project manager is, dates, etc.) and the finance department who keeps up with payments, outstanding balances etc. The two departments were having problems communicating what had been done etc. .>>Enter database. I have a created forms for both departments, but I want the finance departments form to work like this:

Finance person enters the serial number of the contract (which is my primary key and foreign key) Form then auto populates some of the information previously entered into another form by the contracts department (and a different table, in case that is important) and then the finance person can carry on inputting their information about the payments.

So my questions are as follows:

1) how do I format the first field (the serial number field) to look up the serial number to a) make sure it is already in put and if it is to b)auto populate information already associate with that serial number?
2) If the serial number has not already been entered in the contracts form, I don't want the finance person to be able to enter any payment information about it (I want them to have to talk to the contracts department)

I don't know anything about code or..really about anything to do with access so I would appreciate really detailed and simple answers if possible!

Thanks in advance!

 

[ajetrumpet]

Kstarnes,

First of all, most answers that are detailed are not simple, especially if you are not experienced with the program. But, I can give you some basic information that you can probably follow...

I want the finance departments form to work like this:

Finance person enters the serial number of the contract (which is my primary key and foreign key) Form then auto populates some of the information previously entered into another form by the contracts department (and a different table, in case that is important) and then the finance person can carry on inputting their information about the payments.

Here is the MS article for auto-populating from a combo box (this is what you need)...

http://support.microsoft.com/kb/319482/en-us

So my questions are as follows:

1) how do I format the first field (the serial number field) to look up the serial number to a) make sure it is already in put and if it is to b)auto populate information already associate with that serial number?

Well, the combo box is a lookup mechanism, so you can use it for that purpose, but maybe you would be better off giving your user like a textbox or something where they can enter a serial number, or a string similar to it, and, if it's not in the box (use DLookup in a form event for this) list, give them a message, and if it is in the box, just auto-populate it IN the combo box, along with the rest of the information. They wouldn't have to do anything if you did it that way!

2) If the serial number has not already been entered in the contracts form, I don't want the finance person to be able to enter any payment information about it (I want them to have to talk to the contracts department)

There are a number of ways to do this. Sometimes I just hide controls based on a search string, if I don't want the information associated with that string to be changed. To get the process of this right, you would have to get down to the specifics of the situation; more than what you have described so far.

The thing you might be most concerned about here is the information you will have to lookup, and if it is in more than one table, producing the rowsource for your combo box. Just know that rowsources for combo boxes are SQL statements too, so basically they are treated like queries in their own right, so in your case, you will probably have to write one with some type of JOIN in it, so you can get all of the information into the box, thus providing the information you will need to implement the process that is described in the above MS article.

 

[kstarnes]

Thanks...but anything to do with SQL is beyond me...I studied political theory sigh. I think this is going to have to be referred to someone else.