Fill down and auto tab to next field issues

[RobF]

Greetings Access gurus. First time posting here so be gentle.

While I am not an Access expert, I know enough to be dangerous. I have recently did some updating/upgrading to a database that my company uses and have a "bug".

For a new order our Shipping clerk would scan in a serial number (for our product to be shipped), it would auto tab to the next field, then scan the Order number, auto tab, it would auto populate the "Sold to field" and auto tab to create a new record.

Here's what is now broken.

After the first record is created, our Clerk scans in the next Serial number and auto tabs to the next field > it fills down from the previous record and stays on that field and never auto tabs. It waits for a manual tab.

The embedded macros for the field is super simple.

If [Forms]![Order Entry Form]![SERIAL NUMBERS.OrdNumber] Is Null Then
Sendkeys
Keystrokes ^'
Wait = Yes
End if
If [Forms]![Order Entry Form]![SERIAL NUMBERS.OrdNumber] Is Not Null
Sendkeys
Keystrokes {tab}
Wait = No.
End if.

I thought the initial wait = yes was the problem, changed to know but still sits on that field and waits. It never tabs.

The original database format was 2003 and everything worked fine prior to upgrading to 2013 (we are now on 2019).

Did something change with the macro handling from 2003 to 2013? I will attach screen shot of the form for review.

Thanks in advance for any assistance!

 

[Pat Hartman]

I can't see the attachment yet since it hasn't been approved so I'll start with, do you really have a period in a field name or is that a typo?

 

[RobF]

Thank you to whom ever put my post in the right spot. Sorry about that.

The macro code in my post is exactly the way it has been for many many years. I did not develop the database but now get to fix the breaks.

 

[theDBguy]

Hi. Welcome to AWF! If that code used to work, I am not sure how. But for now, have you tried adding one more Sendkeys to send the {tab} key in the first part of the macro?

 

[RobF]

Thank you.

Yes, added extra Sendkey {tab}.

To be honest, I even made a new DB with three fields to mimic the live and have yet to get it working. I failed to mention the embedded macros are in the On GetFocus properties.

I even tried putting the code in On Enter, no change.

 

[theDBguy]

Thank you.

Yes, added extra Sendkey {tab}.

To be honest, I even made a new DB with three fields to mimic the live and have yet to get it working. I failed to mention the embedded macros are in the On GetFocus properties.

I even tried putting the code in On Enter, no change.

Hi. Just to be safe, can you please post the updated version of the macro with the extra Sendkeys to make sure it's placed where I was thinking it belongs? Thanks.

 

[RobF]

If [Forms]![Order Entry Form]![SERIAL NUMBERS.OrdNumber] Is Null Then
Sendkeys
Keystrokes ^'
Wait = Yes
End if
If [Forms]![Order Entry Form]![SERIAL NUMBERS.OrdNumber] Is Not Null
Sendkeys
Keystrokes {tab}
Wait = No.
Sendkeys
Keystrokes {tab}
wait = No
End if.

I even had the extra {tab} in the Not Null If.

 

[theDBguy]

I even had the extra {tab} in the Not Null If.

Did you mean like this?

If [Forms]![Order Entry Form]![SERIAL NUMBERS.OrdNumber] Is Null Then
Sendkeys
Keystrokes ^'
Wait = Yes
[B]Sendkeys
Keystrokes {tab}
wait = No[/B]
End if
If [Forms]![Order Entry Form]![SERIAL NUMBERS.OrdNumber] Is Not Null
Sendkeys
Keystrokes {tab}
Wait = No
End if

 

[RobF]

Yes, exactly like that. Also changed the wait to No for the ^' Keystrokes.

 

[theDBguy]

Yes, exactly like that. Also changed the wait to No for the ^' Keystrokes.

Okay, if the above change I posted didn't work, then perhaps you could try using GoToControl instead of Sendkeys.

 

[RobF]

If I do this, it skips right past the Order Number field and jumps to the Sold To field.

If [Forms]![Order Entry Form]![SERIAL NUMBERS.OrdNumber] Is Null Then
Sendkeys
Keystrokes ^'
Wait = Yes
[B]GoToControl
Control Name = Sold To[/B]
End if
If [Forms]![Order Entry Form]![SERIAL NUMBERS.OrdNumber] Is Not Null
Sendkeys
Keystrokes {tab}
Wait = No
End if

 

[isladogs]

I've just approved the attachment to post #1

 

[RobF]

I'm not sure where the hick-up is. After the first record for a new order, I would think the If is NOT NULL would just see that the field data from the previous Order Number record has been Filled Down (^') and move on. But when I move the GoToControl down to the Is NOT Null if statement it doesn't "recognize" the field is no longer Null and waits there.

 

[theDBguy]

I'm not sure where the hick-up is. After the first record for a new order, I would think the If is NOT NULL would just see that the field data from the previous Order Number record has been Filled Down (^') and move on. But when I move the GoToControl down to the Is NOT Null if statement it doesn't "recognize" the field is no longer Null and waits there.

Well, normally, you wouldn't check the same value twice. Instead, you would just use an Else branch in your If/Then block.

 

[RobF]

I am using the macro builder option for the On Got Focus property. I see no way of adding an else branch to the If Statement

 

[theDBguy]

I am using the macro builder option for the On Got Focus property. I see no way of adding an else branch to the If Statement

Hi. Did you try clicking on the "Add Else" link?

 

[RobF]

OMFG, I need to go home!! I get sooo focused on one thing I get tunnel vision. Looks like I will catch this up on Monday. Thank you thus far and have a great weekend.

 

[theDBguy]

OMFG, I need to go home!! I get sooo focused on one thing I get tunnel vision. Looks like I will catch this up on Monday. Thank you thus far and have a great weekend.

Hi Rob. No worries. We all do that. Have a good weekend!

 

[RobF]

Sorry for the late response but was able to figure it out....quite simply I might add.

Had to add code to the After Update event for the one field that was pausing waiting for input.
If field is not null then
GotoControl next field

The code still exists in the On Got Focus to copy down data from the previous record using the sendkeys command ^'. For some reason the IF statement that was in the On Got Focus event was not picking up that there was now data in the field and pausing waiting for input. PS: I did remove it since it didn't do anything anyways

Thanks for the assistance! I guess I needed to talk it out lol.

 

[theDBguy]

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.