Data base help

[Guest]

I’ll try to keep this short and sweet. I want to be able to track what I’m
spending on my employee’s Flight, Hotel, and Car (IE Travel Profile). This
travel profile will also contain job codes and department codes which I’ll
use later on in reporting.

Right now I have three tables Employees, Job Codes, and Expense details. Ok
here is my form as I have it. Two parts to the form, Top part of the form is
my employee information. Pulling the info from the employee table. The
seconded part would be a subform listing all the expense details for that
employee for the entire year. The next recorded would be the next employee
with all of their travel info for the entire year.

I hope you can get a picture of what I’m trying to do. If not let me know
and I’m not sure if I if can post a screen shot of my form, or maybe I’m
going in the wrong direction.

Thanks for you help

Todd Price

 

[Gary Miller]

Todd,

Somehow I am missing the question you're asking here. Could
you be more specific on exactly what you are having trouble
with?

 

[Jeff Boyce]

Todd

While this is do-able in Access, I'm wondering why you are spending the
time/energy to create what sounds a lot like a commercial
bookkeeping/accounting application...

Regards

Jeff Boyce
<Office/Access MVP>

 

[Guest]

Gary,
How this is: Ok let’s say that I have about 100 employees and each employee
having their own record. And on that record I want to have all of the
expenses for that employee for the entire year in a sub form.

I currently have the sub form set up with the top part of the form listing
the employee information and the bottom part listing the expenses in a sub
form. But right now it pulls all of the info from the expense table, but
what I want it to do is to pull just their info from that table on their
record. I know I need some sort of record locator number assigned to each
item entered. I m just not sure where to put that locator and how to call it
back up.

IE. Record 1 is for employee A. The sub form will show all of employee A’s
travel expenses. Then record 2 would be for Employee B with all of their
info and so on. I think I’m painting a clear picture but I could be wrong.

 

[Guest]

Jeff,
The time/engery was nothing for me to create. It took less then a day to
creat what I have now. I'm just missing this one part and I think I'll have
it. I've looked at commercial application, but they offer alot more stuff

 

[Gary Miller]

Todd,

The picture your painting sounds correct. If you are seeing
all of the expenses for everyone listed on each employee
record of the main form then it sounds like your problem is
with your data link between the two forms.

If you have set things up correctly there should be an
EmployeeID of some sort in the expense table to show who's
expense it is. This should hopefully match the EmployeeID in
the Employee table. In the data section of the subform
container control you need to make sure that the MasterField
and ChildFields are properly setup to refer to these ID
fields of compatible data types. If this is done correctly
only the records with that Employees ID will appear in the
subform.

If your expense records were not entered through a
Form/Subform setup it could be possible that you actually
don't have that ID in expenses.

 

[Guest]

OK
Now that I have that working, how about this. Ok let’s say that I want to
go to John Doe’s record. I have no idea what record number he is. How
should I set up the form so I can jump to his record just by typing in his
name? I would also like to be able to type in the last name when needed to
pick the correct record.

Thanks for all your help

Todd

 

[Gary Miller]

There is a wizard that can help you do this.

Make sure that your Wizard is turned on on the Toolbox
toolbar. Select a combobox control and drag it to the form
Header, not the detail. One of the first options will ask
you what you want it to do. Select the options to find a
record on your form and to save the value for later use. You
will want to bring in your ID, LastName and FirstName fields
and then collapse the ID field to a 0 width to hide it. Now
the combobox will display Last and First name but will
actually use the ID field to go to the record you select.
The wizard generates the code for you and places it in the
AfterUpdate event of the combobox.

 

[Guest]

Thanks Gary. I think I have the database working the way I want to. Thanks
for all your help. You are an Access GOD!!!!!!

 

[Gary Miller]

Hardly, but glad that you got it working the way you want.