Form – Tab Order

[gsandy]

I have a form with 10 textboxes (say txt1, txt2 txt3 etc). The tab order is txt1, txt2 txt3 etc. When the form is opened data is entered into txt1 first then txt2 etc. After entering data in txt8, I want to start a new record rather than tabbing through all the remaining items on the form. How can this be done?
Thanks Sandy

 

[GinaWhipp]

Hmm, thinking out loud... you can change Cycle All Records in the Properties to Cycle Current Record then on the last field you tab to put a Go To New Record command in the Event Procedure of the After-Update event.

 

[gsandy]

Hi Gina thanks for the offer of help. I am working on my first database (all my previous experience has been with Excel/VBA) so I will need some help.
Where do I find the Cycle All Records? I could not find it the form properties?
Can you give me the actual code for the Go To New Record?

Cheers Sandy

 

[GinaWhipp]

That property is located in the Properties window of the Form while in Design Mode. To go to a new Record use...

DoCmd.RunCommand acCmdRecordsGoToNew

 

[gsandy]

Hi Gina, I have looked for the Cycle All Records but cant see it (domestic blindness!), see attached. Is it called something else? Cheers Sandy

 

[GinaWhipp]

You are looking at the Properties window for the Detail section, click the little square next to Form Header.

 

[gsandy]

I found the correct properties window (easy when you know how!) and made the changes.
I copied the code you gave and entered it into the After Update Event using the Expression Builder. But get an Invalid Syntax message. Do I have to change the code to suit my form? The form name is "frmHourEnter" and the textbox before the new record is "HoursWorked".

 

[GinaWhipp]

The After_Update of which Control?

 

[gsandy]

Textbox "HoursWorked"

 

[GinaWhipp]

Hmm, I don't get it, is there anything else there with that line?

 

[gsandy]

This is the code in After Update:

= DoCmd.RunCommand acCmdRecordsGoToNew

Looking in the Properties/Event for the textbox "Hours_Worked" there are no other events that I can see!

 

[GinaWhipp]

Please remove the equal sign and make sure it's in the Event Procedure and not pasted on the line in the Properties window.

 

[gsandy]

Removed the = sign and did not get the Invalid Syntax message. See properties window attached.
But entering data into the Hours_Worked textbox I got another message, see attached.

 

[GinaWhipp]

Hmm, okay, let me try to explain a little clearer...

On the After_Upate line there is a little Ellipse button (it has 3 dots on it). Click that and select Event Procedure and then in between the Private... and End Sub paste that line.

 

[gsandy]

If there is no code present the Ellipse button brings up an Expression builder, Code builder etc options.
I went to Form Design Tools-Design-View Code and entered code, see attached. But then got a run time error, see attached. Thanks for your patience.

 

[GinaWhipp]

Oh, well I guess I never noticed that, will have to amend my instructions next time. Now, the error message... let's move it to the On_Exit Event Procedure of the Control. If that doesn't work then I think it's the cycling being turned off that may be stopping it from going to a new record.

 

[gsandy]

Thanks Gina, I will try that tomorrow. Its home time here in New Zealand. I will let you know how I get on.

 

[GinaWhipp]

No problem, it's late here too!

 

[gsandy]

Hi Gina, On_Exit Event was the way to go! It now works fine. Thanks again for your patience and help.

Another question: How can I put VBA code in a command button on a form to bring up another form, without using the Command Button Wizard? In Excel I would use "frmJob.show". And is there any way to view/alter code behind the Command Button Wizard? Cheers Sandy.

 

[GinaWhipp]

GREAT!

For a specific Form use...

DoCmd.OpenForm "YourFormName"

To go to a specific record in the Form...

If field is numeric…

DoCmd.OpenForm "YourFormName", , , "[NameOfFieldFromDataSource]=" & Me![NameOfControlOnFormOpeningFrom]

If field is text…

DoCmd.OpenForm "YourFormName", , , "[NameOfFieldFromDataSource]='" & Me![NameOfControlOnFormOpeningFrom] & "'"