Using entered fields to complete a record entry

[bflotim]

I have 3 tables, an employee info table, a table of various actions and
subactions and a transaction table that draws from the first 2 tables.

On a form that looks to create new records in the transactions table, I
select the employee that it affects drawing that info from the employees
table(no problem here), then I select an action from a combo box populated
from the actions table. Based on the selection in this combo box a 2nd combo
box is populated with related subactions (no problem here either).

Ultimately, what my transactions table consists of, is the current date, the
employee id #, and the action #. Each action record is made up of the action
#, action, subaction and a monetary amount. What I want to do is, based on
the selections in the Action combobox and the subactions combo box, I want to
display the monetary amount on the form and determine the action # of the
record to store in the transactions table.

Thanks for your help.

 

[Mike Painter]

I have 3 tables, an employee info table, a table of various actions
and subactions and a transaction table that draws from the first 2
tables.

On a form that looks to create new records in the transactions table,
I select the employee that it affects drawing that info from the
employees table(no problem here), then I select an action from a
combo box populated from the actions table. Based on the selection
in this combo box a 2nd combo box is populated with related
subactions (no problem here either).

Ultimately, what my transactions table consists of, is the current
date, the employee id #, and the action #. Each action record is
made up of the action #, action, subaction and a monetary amount.
What I want to do is, based on the selections in the Action combobox
and the subactions combo box, I want to display the monetary amount
on the form and determine the action # of the record to store in the
transactions table.

Thanks for your help.

It sounds like this is all doable with properly related tables.

Employee ID and Action number would be foreign keys and filled from your
combo boxes.

The rest would be just a form based on a query.

 

[bflotim]

It sounds like this is all doable with properly related tables.

Employee ID and Action number would be foreign keys and filled from your
combo boxes.

The rest would be just a form based on a query.

Yes, but I must be doing something wrong. I have made some headway but I
don't think it is exactly what I want. Let me explain.

My form is based on a query covering the 2 tables that I am getting my info
from. The tables are related in exactly the manner that you mention. The
rest is as follows:

EmployeeID is a combobox that returns ID#, Lastname, and Firstname from the
Employee Table. After selection, only the ID# is displayed.

Lastname is a textbox that uses cboEmployeeID.column(2) as its Control Source.
Firstname is similar only reading column3. These work fine, User can drop
down EmployeeID list and see/choose from any of the information, afterwards
all the boxes are filled in for display. (All this works fine)

The next control is the cboAction (combo box) populated from a query of the
tblActions. Upon selection there is an After Update Event Procedure that
populates the cboSubAction based on the selection made in cboAction with the
SubAction, ActionID and Amount from the Actions Table(The actionID and Amount
are not displayed in the combo box, columnwidth is set to 0). Both the
cboAction and cboSubAction are unbound.

The cboSubAction also has an After Update Event Procedure that then displays
the ActionID in a newly created control that I was thinking to hide on the
form (no need for user to see this) and also sets the ActionNumber field for
the tblTransactions.

Private Sub cboSubAction_AfterUpdate()
txtActionNum.Value = cboSubAction.Column(1)
Me.ActionNumber = cboSubAction.Column(1)
End Sub

The txtAmount control is simply unbound and has the ControlSource set to
read the column from the cboSubAction.

This seems to work for display purposes and updating the tblTransactions.
The problem I am having now is that when I go to enter another record the
cboAction, cboSubAction, txtAmount and txtActionNum do not clear themselves.

In addition, if you go back to look at records in the form, none of these
display. Ultimately, I would like it so that this is simply an entry form
anyway and that records entered cannot be brought back up on the form and
cannot be edited. This will be taken care of through Reports, etc.

I appreciate any assistance that you can provide.

 

[Mike Painter]

Yes, but I must be doing something wrong. I have made some headway
but I don't think it is exactly what I want. Let me explain.

My form is based on a query covering the 2 tables that I am getting
my info from. The tables are related in exactly the manner that you
mention. The rest is as follows:

EmployeeID is a combobox that returns ID#, Lastname, and Firstname
from the Employee Table. After selection, only the ID# is displayed.

The combobox and Employee ID field in the form should not have the same
name.
You lookup the value in cboEmployeeID and the control source is the foreign
key EmployeeID in the main table.
As soon as that is filled the name from the related table will show if those
fields are on the form.

I think the Northwind database has examples of what you need. to see. I've
just switched to 2007 and all teh templates seem to be designed to show off
new features.

Populating the subitem combobox will probably require a requery or refresh
at some point but there should be no other code needed.