Trouble Adding employees to a form

[Lou_Reed]

In the attached file, I am trying to add personnel using the frmAddPersonnel, In it I will put in the employee's name, first and last, the preferred or nickname and the branch and whether or not they are a branchhead.

Two things happened.

1. When I was trying to indicate if they were a branchhead it gave me the choice of yes/no-on/off-true/false. I do not like any of them I just want a box that


you can put a checkmark in if they are a branchhead and leave blank if they are not.It does not give that choice.

2. The second it started to number them, employees : PersonnelID, in the 50's. What is going on? It could just add them sequentially. Why make this big jump in numbering?

I just found out by running frmAddPersonnel a box opens up see attached file that I think is looking for a PersonnelID number when it opens. I gave it one, the PersonnelID number
was 60. It was just a random choice. Why does it not just number sequentially?

I just need to correct these two things.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[ranman256]

True/false fields CANNOT be null by nature.
if you want nulls ( blanks) then you must use a String. T,F,or null.

[Lou_Reed]

Is the checkbox option not applicable here?

Respectfully,

Lou Reed

[ssanfu]

<snip>Two things happened.

1. When I was trying to indicate if they were a branchhead it gave me the choice of yes/no-on/off-true/false. I do not like any of them I just want a box that
you can put a checkmark in if they are a branchhead and leave blank if they are not.It does not give that choice.

Is the checkbox option not applicable here?

Yes, but on the form, the control is a text box, not a check box. Delete the control and add the "BranchHead" field again.

2. The second it started to number them, employees : PersonnelID, in the 50's. What is going on? It could just add them sequentially. Why make this big jump in numbering?

The "PersonnelID" field is an autonumber type field. It is guaranteed to ALWAYS provide a unique number, but not a sequential number.
It is not even guaranteed to be a positive number.
This is one reason that an autonumber should NEVER be displayed!

See:
Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/Autonumbers

I just found out by running frmAddPersonnel a box opens up see attached file that I think is looking for a PersonnelID number when it opens. I gave it one, the PersonnelID number
was 60. It was just a random choice. Why does it not just number sequentially?

There are several things going on here.

Why was the parameter dialog box displayed?? It was caused by your code. You tried to reference (requery) a form that was CLOSED! (And by using incorrect syntax at that!)
The line is

Form_frmPersonnel.Requery

So, form "frmPersonnel" is open. You click on the ADD button and this code executes

    DoCmd.OpenForm "frmAddPersonnel"
    DoCmd.Close acForm, "frmPersonnel"

Form "frmAddPersonnel" is open and you add the personnel information. You click the ADD button, some code executes and the last line is

    Form_frmPersonnel.Requery

But "frmPersonnel" is not open -remember? It was closed.... but because you used the internal Access name of the form, (bad) magic happens and something on the form needs the PersonnelID value causing the request for the parameter value of PersonnelID.
So instead of re-querying "frmPersonnel" in the btnAdd code, you should open "frmPersonnel", but in the close event of form "frmAddPersonnel".. (I would add a close button)

I said you used invalid syntax because "Form_frmPersonnel" is how ACCESS refers to a form.
You should NEVER use this syntax; you need to/must use the form collections name: "Forms!frmPersonnel".



A side note: on form "frmAddPersonnel", the tab order of the controls is messed up....
Also, why is the field "Branch" a Long??? Is there going to be a table of Branch names?

[Lou_Reed]

Why was the parameter dialog box displayed?? It was caused by your code. You tried to reference (requery) a form that was CLOSED! (And by using incorrect syntax at that!)
The line is

Form_frmPersonnel.Requery

Where is this code I pointed out above? I cannot easily find it or I just cannot find it.

I have some questions to ask on it. Once I find it.

Respectfully,

Lou Reed

[Lou_Reed]

OK, I have found the code. Everything is ok there.

Why was the parameter dialog box displayed?? It was caused by your code. You tried to reference (requery) a form that was CLOSED! (And by using incorrect syntax at that!)

But I have a few questions and the first is : how that form frmPersonnel is closed by just inspecting code? It is not apparent to me. I understand that
you cannot or you should not requery a closed form. But how do you know just by the seat of your pants?

So, form "frmPersonnel" is open. You click on the ADD button and this code executes[

In here you just assume that the form is now open? Is this for pedagogical purposes here?
Sort of like let's assume frmPersonnel is open? I just do not follow the steam of logic.

So I am guessing that I change the line

Form_frmPersonnel.Requery

to what? What do I add?

You also said add a close button and then requery frmPersonnel?

Is this the way to do things?

Any help appreciated. Thanks in advance.

Respectfully,


Lou Reed

[Lou_Reed]

So instead of re-querying "frmPersonnel" in the btnAdd code, you should open "frmPersonnel", but in the close event of form "frmAddPersonnel".. (I would add a close button)

Explain this line, especially the "I would add a close button". I am not following that logic.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

So, form "frmPersonnel" is open. You click on the ADD button and this code executes

    DoCmd.OpenForm "frmAddPersonnel"
    DoCmd.Close acForm, "frmPersonnel"

At this point frmPersonnel is closed? That makes sense, it is the last executed of the two statements immediately above and that closes frmPersonnel.

My question is where do these two statements go in the overall code? Where are they placed? It is in the Sub btnAdd_Click(), but just where does it go?

Finally, where should the close button go? Why do I need it?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[ssanfu]

Apologies... your thread disappeared for a while.

I'll see if I can catch up.....

I posted (post #4)

I said you used invalid syntax because "Form_frmPersonnel" is how ACCESS refers to a form.
You should NEVER use this syntax; you need to/must use the form collections name: "Forms!frmPersonnel".

Post #5

<snip>
(And by using incorrect syntax at that!)
The line is

Form_frmPersonnel.Requery

Where is this code I pointed out above? I cannot easily find it or I just cannot find it.

I have some questions to ask on it. Once I find it.

How I found all of the instances of that syntax is:
Open the IDE.
Close all of the code modules.
Open one code module (any module).
Press <ctl-F> (Find)
Enter "Form_" (NO quotes)
Under "Search", click on "Current Project"
Click on the button "Find Next".
Keep clicking on "Find Next" until the error message "The specified region has been searched" appears.

OK, I have found the code. Everything is ok there.

Does that mean you have corrected all 30 instances to the correct syntax???

-------------------------------------------------------------------------------------------

Post #6

But I have a few questions and the first is : how that form frmPersonnel is closed by just inspecting code? Inspecting code does not close forms
It is not apparent to me. I understand that you cannot or you should not requery a closed form. But how do you know just by the seat of your pants?
Not by the seat of my pants... I read the code:

DoCmd.Close acForm, "frmPersonnel

Code that YOU wrote...

So, form "frmPersonnel" is open. You click on the ADD button and this code executes[

In here you just assume that the form is now open? Is this for pedagogical purposes here?
Sort of like let's assume frmPersonnel is open? I just do not follow the steam of logic.

So I am guessing that I change the line

Form_frmPersonnel.Requery

to what? What do I add?

The proper syntax is

Forms!frmPersonnel.Requery

-------------------------------------------------------------------------------------------

Post #6

<snip>
You also said add a close button and then requery frmPersonnel?

Is this the way to do things?

I suggested adding/having a close button for the form. I have a close form button on every form except the main form. The main form has a "Quit" button.
This is one of my forms




Looking at your dB, it seems that form management was an afterthought.
For instance:
the dB opens and the "DivisionDashboard" form opens. (I never use the switchboard manager -too limiting and it is not even available on my ribbons).
"DivisionDashboard" is set to Popup and Modal. (2 things I also never use)
You click on "Personnel Management" and form "frmPersonnel" opens - also Popup and Modal. The dashboard is still visible behind "frmPersonnel".
Clicking on the ADD button, "frmPersonnel" disappears and only "DivisionDashboard" is visible. If I move "DivisionDashboard", "frmAddPersonnel" becomes visible.
There are 2 buttons - "Add" and "Done". The name of the button with the caption "Done" is "btnCancel"?? And no control tip text to say "Close form".
Does "Done" mean go back to the previous or Cancel the current operation??

On "frmPersonnel", I finally saw a button towards the bottom of the form that says "Close Form".
All of my forms have a close button in the same location - top right corner of the header. (see image above)
Consistency - of object placement, colors and names.

My thought process would be:
"DivisionDashboard" opens
Click on a menu option. Click on "Personnel Management", form "frmPersonnel" opens. "DivisionDashboard" closes because it is just a menu.
Need to add personnel... click in the Add button and "frmAddPersonnel" opens. I would hide (not close) "frmPersonnel". Only "frmAddPersonnel" is visible (maybe might need info off of "frmPersonnel".)
When done adding personnel, close "frmAddPersonnel" and make "frmPersonnel" visible.
Clicking on the close button would close "frmPersonnel" and open "DivisionDashboard" -tada! back to the beginning.
Only one form visible at a time.



And speaking of "frmPersonnel", why is there a subform of "frmAddPersonnel" at the bottom of the form???

[Lou_Reed]

Okay, thanks I will give it a try. Thanks for your feedback. Please understand, I was quite new to forms requiring opening, closing and requerying so that I why I asked the dumb questions.

You showed me a new method of find (and replace?) on MS Access 2010. That is very helpful.

Respectfully,

Lou Reed

[Lou_Reed]

I will try your suggestions, but first let me ask a dumb question.

Where do the lines of code;

DoCmd.OpenForm "frmAddPersonnel"
DoCmd.Close acForm, "frmPersonnel"

go? My guess is that they are the first executable lines in Sub btnAdd_Click.

I would put them there, but any other suggestion I would consider.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

Does that mean you have corrected all 30 instances to the correct syntax???

I counted far more than 30. It was morel likely 47 instances.

Also, can I just use find and replace to replace all instances of Form_, with Forms!. That would be grate if it would work.


It would save a lot of time.


Any help appreciated. Thanks in advance.

Respectfully,


Lou Reed

[Lou_Reed]

I went ahead and used the find and replace command for VBA code on my project. There are actually 46 instances of Form_. Now I just ran find/replace and
changed all instances of Form_ to Forms!. That was a bad idea.

It failed on compile on the following line:


Private Sub Forms!Load()

The line was originally :

Private Sub Form_Load().

Now that old version works and the new version does not. I am unsure as to why.

I admit I have not seen online the command


Forms!Load()

anywhere, but as you said the old version is not to be used. So what is going on here.

Any help appreciated.

Thanks in advance.

Respectfully,

Lou Reed

[ssanfu]

That is why I said 30 instances!!

So what is going on here.

You should not REFER to FORMS using "Form_"..... This does NOT include FORM events.

"Form_Open" and "Form_Close" are FORM EVENTs and SHOULD NOT be changed.



BAD:
Form_frmPersonnel.Requery

GOOD:
Forms!frmPersonnel.Requery


Required:

Private Sub Form_Open()
blah  blah
End Sub

Private Sub Form_Close()
blah  blah
End Sub

[Lou_Reed]

I see. I was too quick to jump on what you said. Thanks for he update.

Respectfully,

Lou Reed