Moving and updating data with command button

All right, I've finally been able to hunker down on your form suggestion (I've been sidetracked a couple days...) but I'm having trouble getting it to come out right.

I searched the forum for how to do it, and came across a very helpful post, but I'm doing something wrong. (Help with combo boxes, Please!)

I've set up my form to not be bound to any table or query. I have my first list box (lstPosting) bound to a query that takes the AnnouncementNo from tblPosting, plus other information to help the user see which posting it is (positionNo, job title, office).

on the afterUpdate even of this combo box I have me.lstApplicants.requery.

Then my second list box (lstApplicants) is taken from a query which takes the AnnouncementID from tblJunction, plus other information to help the user identify the applicant (SSN, and Applicant, which joins the name fields into one field, easy to read.)

For the row source, I have:

SELECT DISTINCTROW [qryApplicantslstbox].[AnnouncementID], [qryApplicantslstbox].[ApplicationID], [qryApplicantslstbox].[SSN], [qryApplicantslstbox].[Applicant] FROM [qryApplicantslstbox] where ((([qryApplicantslstbox].[AnnouncementID])=[Forms]![FormView]![qryPosting].[AnnouncementNo]));

However, when I open the form I get an "Enter Parameter Value" message box, asking for: Forms!FormView!qryPosting.AnnouncementNo.

I have tried several different things, including in row source
...where ((([qryApplicantslstbox].[AnnouncementID])=[Forms]![FormView]![lstPosting])); (this is what the posting seems to suggest I do...

Anyway, any insight would be appreciated... I have tried set up relationships both in the queries and in the main relationships page... I've also tried to take both list boxes from the same query... I am stuck.

Thanks.

--Sara
 
Sara,
<<
I have tried several different things, including in row source
...where ((([qryApplicantslstbox]. [AnnouncementID])=[Forms]![FormView]![lstPosting]));
>>

First
=====
When you type in a string for a Where clause and you want to use the name of a control, you have to break the string like:
"Where ((([qryApplicantslstbox]. [AnnouncementID])="
&
[Forms]![FormView]![lstPosting]
& ")"

If you leave it as one string, Access thinks
[Forms]![FormView]![lstPosting]
is a literal value.

Second
=====
Instead of using a SQL statement as a RowSource for lstApplicants use the query, qryApplicantslstbox, as the RowSource. In the query put [Forms]![FormView]![lstPosting] in the criteria cell under the appropriate field. Then save the query.

Back at the form, you have an OnClick event for lstPosting. In the event sub you put Me![lstApplicants].Requery

This will rerun qryApplicantslstbox and that will refresh the content of lstApplicants with the data that corresponds to the selected row in lstPosting.

HTH,
RichM
 
RichM,

I've taken your advice and put [Forms]![FormView]![lstPosting] in the criteria for AnnouncementID in my qryApplicationslstbox.

I am, however, still getting the same problem. Is there a way I have to format it in the query design grid?
 
Sara,

<<
I've taken your advice and put [Forms]![FormView]![lstPosting] in the criteria for AnnouncementID in my qryApplicationslstbox.
>>

That's the correct syntax.
1) FormView must be open when you run the query
2) Check and double check spelling

The message "Enter Parameter Value" is usually caused by a small spelling error in the criteria cell.

RichM
 
I have the syntax completely correct, and everything is spelled right... could it be something else? What about control source? My text boxes are unbound...
 
Sara,

What is the exact message ?

When do you get the message ?

It does not matter whether the list boxes are bound or not.

RichM
 
When I open the form, or go from design view into form view, or when I click on another option in lstPosting (my list box) I get an "Enter Parameter Value" message box, asking for: Forms!FormView!lstPosting.

If I click "OK" the lstApplicants (my second list box) comes up completely empty.

The only difference when I click "Cancel" is the faint gray dividing line which appears where a first record would be if there was one there, which there isn't.

I didn't think it would matter if the list boxes were bound, but I am just baffled about what the problem is here.

Is there a possibility that I have the query relationships set up badly? Or the queries themselves? I didn't think so, but I really have no clue what's going on.

--Sara
 
Sara,

The form you open is named "FormView" right ?
And the listbox control is named "lstPosting" ?

Outside of that, I can't think of another explanation.

Can you make, ZIP, and post an MDB with just FormView, the listbox queries, and the tables used by the queries ?

I can look at it tomorrow.

RichM
 
Aha... no, my form was called frmHire. We're good to go now. I guess I was confused about what [FormView] meant... (Insert obligatory explanation of my complete lack of programming knowledge here...)

All right, so I have the form all set up. I haven't set up an onclick event for lstApplicants becuase I am lost there...

Thanks again.

--Sara
 
Sara,

OK, good luck.

If you need help with event handling, post again.

RichM
 
I do need help.. I don't even know where to start. (besides onclick)
 
Sara,

A good first step for you would be to do a search on "cascading combo" boxes. There are many threads on this topic.

You are using cascading list boxes, not combo boxes, but the principle is the same.

I think you have 3 or 4 tables involved here:
tblPostings,
tblPosition (maybe ?)
tblJunction,
tblApplicant

The first list, lstPostings, should be based on a query that selects fields from tblPosting and maybe tblPosition. One of the columns, probably PositionID, is the bound column of the list. Looks like you have already done this.

The second list, lstApplicants, should be based on a query that joins tblJunction to tblApplicant. This query has a criteria for PositionID in tblJunction. This query selects the fields you want to show in the Applicant list.

The magic happens when you click on a row in lstPostings. You can go the the properties box on lstPosting and go to the Events tab. You will find an OnClick event. Select "Event Procedure" from the list in OnClick and an OnClick event sub will be created for you. All you need in body of the sub is:
Me![lstApplicants].Requery

This reruns the query that is the RecordSource of lstApplicant and the query uses the current value of PositionID (the boudn column) from lstPosition.

In other words, the list of applicants is dynamically changed to show those who have applied for the position you selected.

RichM
 
Thanks, Rich,

I've actually done all that... what I meant was that I don't know where to start to do the procedure explained in my original post (moving new employees into the employees table, changing the information of current employees, etc.)

But it was nice to read your post and see that I had done it all right. :)

--Sara
 
Sara,
<<
what I meant was that I don't know where to start to do the procedure explained in my original post (moving new employees into the employees table, changing the information of current employees, etc.)
>>

Here's my "vision" of the hire process.

1) You select a Posting
2) You view all the Applicants for the position.
3) You select the winner from the list of Applicants.
4) You click on a command button that says "Hire this one".

There is an OnClick event sub for the hire button. You put all the code and other stuff in this sub.

I think your next step should be:
make a script of all the detail actions that should be done to complete the hire(use business terms for the actions),
review list with users,
make queries and other things to move data from here to there.

It sounds like some of the actions are:
1) see if Applicant is in tblEmloyee (search by SSN ?) If not, then run an Append query to copy some fields from tblApplicant to tblEmployee.
2) update tblEmployee with PositionID or whatever indicates that this person has that job.
3) delete the winner from tblApplicants.
4) delete current record from tblPosting based on key value from lstPosting.
5) delete all records from tblJunction with matching PositionID
6) maybe delete the "losers" from tblApplicants or keep until later

Each action roughly corresponds to a query with a parameter based on lstPosting or lstApplicant.

Good luck,
RichM
 
You said:

<<I think your next step should be:
make a script of all the detail actions that should be done to complete the hire(use business terms for the actions),
review list with users, make queries and other things to move data from here to there. >>

Okay, here's what happens when a person gets hired:

1) CURRENT EMPLOYEES: Their file is updated to include a change in position # and probation date. Cost Center (office), supervisor, etc may change depending on the position #.
NEW EMPLOYEES: A new file is started, which contains their newly assigned TDNo, Position Number, Hire Date, and Probation Date. (Hire date and probation date will usually be the same.)

2) Application of "winner" goes to State Personnel. A copy is placed in their file.

3) Application of "losers" get filed in a drawer for one year, when they are expunged.

4) Postings also get filed for one year (with applications) when they are also expunged.

(Applications and Postings are kept for one year as a reference. Keeping this in the db for a year will help HR see a person's application history - how many times they've tried to change jobs, etc.)

You also said:

<<There is an OnClick event sub for the hire button. You put all the code and other stuff in this sub. >>

Right... well, I don't know how to do that really...

This is what I'd like to see happen on the db level:

1) User clicks "hire". A message box pops up "Are you sure you would like to hire [applicant] to Position #[PositionNo] ([PositionTitle], [Office])?"

2) The user chooses yes or no. No cancels the action. Yes continues to #3.

3) CURRENT EMPLOYEES: The db looks for a TDNo in tblApplicant for "winner". If the TDNo exists, then the record in tblEmployees with the corresponding TDNo is "tapped". The user is prompted for a date of probation, and the position# is updated to the PositionNo of the posting.
NEW EMPLOYEES: The db looks for corresponding TD no, but doesn't find one. It prompts the user for a TDNo, then opens a new record in tblEmployees, where it plugs in the Position# from the posting. It asks the user for a Hire date and a Probation date.

5) Record of "winning" application get's a value of "yes" in the "Hired" field.

6) Record of all applications for position get "locked" (they become read-only)

7) Record of Posting becomes "locked."

8) Applicant remains in tblApplicant

One year later, the records can be deleted. I think that means I'll have to add a date field for "expungeDate," which is the date one year from the hire.

I don't know how to do this. I've been playing with a copy of the db and append queries and macros (because I'm not good at code...) but they're not doing what I need them to.

Thanks.

--Sara
 
Sara,

The list of things you'd "like to see happen" makes sense. If I was doing this I would start by making a query or two for each update action in your list. Test each update action by running the queries. Get the first to work then do the second. Etc.

Add conditions and parameters next. Test again.

Eventually you will package the queries into a "hire" sub and run them when the user clicks on a button.

So start with a simple query for:
<<
3) CURRENT EMPLOYEES: The db looks for a TDNo in tblApplicant for "winner". If the TDNo exists, then the record in tblEmployees with the corresponding TDNo is "tapped". The user is prompted for a date of probation, and the position# is updated to the PositionNo of the posting.
>>

How would you write the simplest possible query to do this ?

Onward,
RichM
 
UPDATE tblPostings INNER JOIN (((tblApplicants INNER JOIN tblApplication ON (tblApplicants.SSN = tblApplication.SSN) AND (tblApplicants.SSN = tblApplication.SSN)) INNER JOIN tblEmployees ON tblApplicants.TDNo = tblEmployees.TDNo) INNER JOIN tblJunction ON tblApplication.ApplicationID = tblJunction.ApplicationID) ON tblPostings.AnnouncementNo = tblJunction.AnnouncementID SET tblEmployees.PositionNo = [forms]![frmTrackApplication]![PositionNo], tblEmployees.ProbDate = [Enter Probation Date]
WHERE (((tblApplication.ApplicationID)=[forms]![frmTrackApplication]![ApplicationID]) AND ((tblApplicants.TDNo) Is Not Null));

I put my command button in another form because it had all the information I was looking for. I was having a hard time with getting the right information from frmHire.

But... IT WORKS!!

I think I may actually be able to *do* this... :)

--Sara
 
So now I'm working on an append query for if the person is not an employee, and I'm running into a bit of a problem. Because the TDNo is blank for them in tblApplicants, the query won't append the info into tblEmployees (whose PK is TDNo.)

I tried to put [Assign TD Number] in the criteria field, but no luck.

Not sure what to do about this...

Thanks.

--Sara
 
Sara,

I can think of a few options for "hiring" a non-employee:
1) Change tblEmployee so TDNo is not the primary key. If this table is used by other apps, that won't be a popular option.

Or

2) Require the users to add an Applicant to tblEmployee before the Applicant can be hired. I don't remember what you have in the query for a list of Applicants, but you could put TDNo in the list if available. If not available then it would be NULL and you could test for that. If no TDNo, then display an error message.

Or

3) Make a "phony" TDNo (all 9 perhaps) and trust the user to fix in tblEmployee as soon as possible.

I like option 2 the most.

RichM
 
Hi, Rich!

Thanks for the response... I ended up using secret option #4... let me know if this is a bad idea... but I used 2 queries on top of each other...

The first was an update query which asks the user to assign a tdNumber to the current record (in tblApplicant.) The second appended to tblEmployees. This worked well, because it also kept the TDNo in tblApplicant to be referred to later, if that person were to apply to another position.

I just used a macro to link them together for now. It worked well with my test data. Yayy!

How, though, might I lock the applications and posting?

Thanks again. :)

--Sara
 

Users who are viewing this thread

Back
Top Bottom