programming button to update records

[lstairs]

Ok, I’ve got a database whose function is primarily to track the number of labor hours assigned to each job order number (JON). I’m using Access2003.

I have two main tables: tblLaborHoursByJON and tblJobDescriptionsAddlInfo.

- tblLaborHoursByJON includes columns: JON, FiscalYear, DivisionCode, LaborHoursST, LaborHoursOT, and EstHours

· the combination of JON, FiscalYear, and DivisionCode is set as my primary key as there are multiple entries for each JON
o within each JON there are sometimes multiple entries by year or division
· this table specifically holds labor hour data

- tblJobDescriptionsAddlInfo includes columns: JON, Description, Award, Accept, Assist, and Parent

· the JON is the primary key here
· this table tracks descriptive info for each JON (it’s name, when it went into effect, if it’s related to another JON, etc.)

- the tables are related by JON

I want to create an input form to enter new information. For tblLaborHoursbyJON, I want the form to replace the old data if the JON/FiscalYear/DivisionCode primary key exists already. If not, I want it to create a new record. However, I need the form to check if descriptive data exists already for the JON being entered. If not, a separate form needs to open up to allow the user to enter information for those fields.

My problem is that I don’t know how to write VBA code. I created the form with input fields: JON, FiscalYear, DivisionCode, LaborHoursST, LaborHoursOT, and EstHours. At the bottom, I put a button [cmdEnter]. I set the properties of the button that OnClick [Event Procedure], but now I have to write the procedure.

I want the procedure to look like this:

Does JON exist?
If no,
Open frmAdditionalInfo
[user will enter the appropriate info: JON (from
previous form), JobDescription, Award, Accept,
Assist, ParentJON ]
On Click – cmdEnter
Append new record to tblJobDecription…
Close form
Continue to “If yes” of previous form (or restart
procedure)
If yes,
Does primary key exist?
If yes,


Replace record with new data
If no,
Create new record with this data
Close form

I’m just not sure how to turn this into code and would greatly appreciate help. I’m also open to suggestions if there’s a better way to do this.

[Guus2005]

First. The used font suggests that you allready posted this question somewhere else.
Post a sample database describing the problem.

You post a series of questions which are a problem to you. Show me what you got and i'll help.

[lstairs]

Actually, I just typed it up in Word first because I knew it was gonna be long. I wanted an easier layout to review it before posting.

Here's a sample database. It contains the tables I'm using so far. (I left the codes table, but you can ignore it. I'm not sure that I'll even end up using it, but it's there in case I decide to use it for a report later on...) It also contains the two forms I've built so far... I want to use the forms to update the tables (according to the procedure I described previously). I tried to be specific and descriptive but let me know if you need more info.

[lstairs]

Event Procedure may not be the right name for what I want to do. It was the option in the drop down box for "On Click". I assumed it meant "to call a macro/program..." but I'm realizing that may not be a correct assumption.

[Guus2005]

Your approach to this problem could be a lot easier.

Create a bound form the the table.
Create a combobox with all available JON.
You can program the "on not in list" event of the combobox when a JON is entered which does not exist.

Here are a few sample databases from which you can draw some codesamples.

HTH

[lstairs]

So, I program the combo box to call the AdditionalInfo form if the entered JON is not listed? And then I program my Enter button to update or add the record?

That does seem simpler and gives me a good starting point. Thanks for the help.