Update Two fields (text boxes) Form

[j2curtis64]

I want to be able to update value in two text boxes based on the value in a combo box. This would be in a Access form.

I am getting a compile error: Method or data member not found

Below is the code i am using...

Private Sub ApprovingOfficial_AfterUpdate()
Me.AO_Credit_card = Me.ApprovingOfficial.Column(2)
Me.AO_Credit_card = Me.Department.Column(3)
End Sub

What am I doing wrong?

[boblarson]

What am I doing wrong?

I would say that what you are doing wrong is trying to store information that shouldn't be stored. That you just need to store the ID and the rest is in the other table where you can join it up and use it any time with a query.

[j2curtis64]

The form is acting as in input to enter data in a table. Right now when a persons name is chosen their ID number is updated in a txt box. I'd like to also update the department code based on the persons name.

Is not possible to do this or are you saying there just a better way to do this?

tHX

[boblarson]

The form is acting as in input to enter data in a table. Right now when a persons name is chosen their ID number is updated in a txt box. I'd like to also update the department code based on the persons name.

Is not possible to do this or are you saying there just a better way to do this?

tHX

So this is the only place where the person and department code are stored together? What it looks like to me is that you have a table which has the person's ID, Name, and department code and you are trying to put all three of those into another table. If that is what is happening (and I would think it would be because of your combo box rowsource and attempting to use the existing data from the combo box, then that is just plain wrong and not normalized. You only store data ONCE. You then only store the ID for the person in the second table because you already have their name and their department in the original table.

Now, if that is NOT the case and you are not storing the department anywhere other than here, then you would also need to ensure that you are using the right references to the columns. Columns in a combo box are ZERO-BASED. So the second column would be Column(1) and the third column would be Column(2), and so on. But still, you would not store the person's name again.

[j2curtis64]

Thx. I just returned to the office (on vacation), so I am late responding to you.

What is the best way to this? Okay...I have two tables.

One of the tables has names, department, and card number. This table is called Contacts.

My other table (Issues) is empty. See fields below. This is where I want to store data. This table is called Issues.

I have a form where I want to be able to input issues. The persons name (contacts table), department (CONTACTS table), card number (contacts table), date issue opened (issues), req. # (issues), transaction # (issues), status (issues), and rework type (issues).

Contacts table
name
department
card number

Issues table
name
card number
date issue opened
Req. # (issues)
Transaction #
Status (issues)
Rework type (issues)

The card number would be the common field. It would be a one (contact)to many relationship (issues).

Right now I store the name and card number in both tables. But, you mention the ID should be the only field stored. How would I do this? I want the end user to see the name, department, card number in the form. Does this make sense?

[boblarson]

This is how I see it should be set up (with the bare bones knowledge about your business rules from only what you've posted):

tblContacts
ContactID - Autonumber (PK)
FirstName - Text
LastName - Text
CardNumber - Text (I'm assuming text but it depends on what it is and if more than one card can be issued to a contact then it would need another table and be removed from this one.

tblIssues
IssueID - Autonumber (PK)
ContactID - Long Integer (FK)
DateIssueOpened - Date
ReqNumber (don't use special characters in field or object names)
TransactionNumber (And don't use spaces)
Status
ReworkType


So, you don't need to store their name, just their ID (for example, what if you had 100 Issues with a person named John Smith and his name really was Jon Smith. You would have to change it in 100 places instead of my setup where it would happen ONCE. Also, by using names what if you have two John Smith's? Common names can be that way. Best to have some other identifying information about them as well and not use names as a key in the other table.

[j2curtis64]

Thx. So how do I set up my form? I want the end user to select persons names from a combo box, then the card number and department would be automatically populated. After the form is completed the data would be stored in the Issues table.

I dont see how I can do that using the fields you suggested (no name or department).

Sorry for all the questions.
Thx

[boblarson]

Thx. So how do I set up my form? I want the end user to select persons names from a combo box, then the card number and department would be automatically populated. After the form is completed the data would be stored in the Issues table.

I dont see how I can do that using the fields you suggested (no name or department).

Sorry for all the questions.
Thx

You can populate text boxes to DISPLAY the information without storing it in the other table. And then when you want to pull it all together with reporting you use a QUERY with both of the tables in it and linked by ID.

See here for a sample combo box which can display extra data without storing it:
http://downloads.btabdevelopment.com...llInFields.zip

[j2curtis64]

But, I would like to display then store the data in the issues table after entering all the information.

The example you provided (thx) looks as though data cant be added.

[boblarson]

But, I would like to display then store the data in the issues table after entering all the information.

And I am telling you, as an Access Developer for 14 years, that you should not be storing that data in the Issues table. You ONLY store the ID which can then be brought together with the other data when you need to report on it. You use a query, for example, for your report and that query would pull from the issues table AND the contacts table.

This is called the rules of normalization and you should get familiar with them because they aren't just a set of things to maybe follow. There is definitely a rhyme and a reason to them. If you have a properly normalized database it makes it
1. Easier to maintain
2. Less likely that you will have data integrity issues
3. It is easier to get meaningful data back OUT of the database

So, you can not follow my advice, but I would highly suggest that you do.
Read this:
http://support.microsoft.com/kb/283878

[j2curtis64]

I hear what you are saying. I am trying to figure out how to do this.

The end user requirement is to have an input form where they can enter issues into some type of repository/table. I have one table that has a list of contacts. I would use this table for name, card no. etc..Actually, you already have this info.

I know you said use a query for this. Do you have any examples of doing what I am asking? The database you sent me only has existing records. I didnt see a way to add records.

[j2curtis64]

I am posting this again hopefully to get this finally resolved. (see attachment if you cant read the information below)

I've created a database that I am using to track issues. I have two tables.

Contact table that consist of the following columns:

ID, full Name, Position, Site, HierLevelSix, and CC

Issues table consists of the following columns:

Monthly, CH_ID, AO_ID, DateIssueOpened, LastUpdateDate, RequisitionNumber, TransactionNumber, Category, Status

I am using a form to insert records in the Issues table.

To not have duplicate information in both tables, I am using the CH_ID and AO_ID fields in the issues table. These ID's correspond to the values in the contacts table (ID).


Here is a record from the contacts table

ID LastName Position Site HierLevelSix CC CREDIT LIMIT Card Status
161 STEVE STEPHENSON CH DN 12345 888888 $1.00 A
158 MARSHA BRADY AO DN 12345 121212 $2.00 A


Here is a record from the Issue table

ID MonthlyPackage CH_ID AO_ID DateIssueOpened LastUpdateDate RequisitionNumber TransactionNumber Category Status
19 01-Dec-10 161 158 24-Mar-11 03208324 1 Justification INFO Closed


So how would I link the two tables to get good information for a report? For each issue opened there are two people assigned (Position-CH, AO).

I would like to show in a report Month, LastName, Position, Site, CC, DateIssueOpened, LastUpdateDate, RequisitionNumber, TransactionNumber, Category, and Status