Link to home
Create AccountLog in
Avatar of beef137
beef137

asked on

Using "acNewRec" to create a new record in SQL table with an autonumber field. Autonumber doesn't show up :(

I'm using "acNewRec" in my Access application  to create a new record in a SQL table with an autonumber field. The problem is the field isn't showing up in my textbox of the app because the autonumber doesn't appear within the record till you select the row or something like that. What can I do to make the autonumber appear within the textbox of my app right when I click the "add" button?
Private Sub Add_Click()
Application.Echo False
DoCmd.Hourglass True
DoCmd.Close acForm, "Work Order Entry Form"
DoCmd.OpenForm "Work Order Entry Form", , , , acFormEdit
Application.Echo True
DoCmd.Hourglass False
DoCmd.GoToRecord , , acNewRec
DoCmd.GoToControl "Customer Last Name"
[Forms]![Work Order Entry Form]![Add].Visible = False
[Forms]![Work Order Entry Form]![Edit].Visible = False
[Forms]![Work Order Entry Form]![Delete].Visible = False
[Forms]![Work Order Entry Form]![Close].Visible = False
[Forms]![Work Order Entry Form]![Edit Mode].Visible = True
[Forms]![Work Order Entry Form]![Read-Only Mode].Visible = False
[Forms]![Work Order Entry Form]![Save].Visible = True
[Forms]![Work Order Entry Form]![Entered By] = CurrentUser()
[Forms]![Work Order Entry Form]![Status] = "Open"
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Leigh Purvis
Leigh Purvis
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
" till you select the row or something like that."

You can't.  A new record is not actually created until you type a character in a 'bound' control. At that time, you will see the auto number appear.

mx
Avatar of beef137
beef137

ASKER

Well the thing is I didn't create this app, but when you hit the save button it won't work because there is no number in the textbox. I guess it worked fine when linked to Access, but it isn't jiving with SQL. Is there a line of code I can enter to force the number to appear within the record? Like selecting the row, then unselecting it?
You must have some code in place which is preventing the record from proceeding past where it can commit.
You can't get the Identity value without saving the record.
You're being prevented from saving the record...
(See where I'm going with this... catch 22).

Examine the code - or any validation rules on the form.
See what's preventing the progression without a value in that field.
(Though why there would be such validation on a, presumably, auto created primary key is unknown...)

Does your form have a subform?  Are you attempting to enter records in this too?
We need more information at least.
Avatar of beef137

ASKER

The snippet shows what the app does when you click the save button. From what I can tell the problem is [Work Order#] is blank because autonumber isn't in the box yet. Am I thinkin right?
[Forms]![Main Menu]![Temp] = [Work Order#]
Application.Echo False
DoCmd.Hourglass True
DoCmd.Close acForm, "Work Order Entry Form"
DoCmd.OpenForm "Work Order Entry Form", , , , acFormReadOnly
Application.Echo True
DoCmd.Hourglass False
DoCmd.GoToControl "Work Order#"
DoCmd.FindRecord "=[Forms]![Main Menu]![Temp]"
[Forms]![Work Order Entry Form]![Add].Visible = True
[Forms]![Work Order Entry Form]![Edit].Visible = True
[Forms]![Work Order Entry Form]![Delete].Visible = True
[Forms]![Work Order Entry Form]![Close].Visible = True
[Forms]![Work Order Entry Form]![Close Out WO].Visible = True
[Forms]![Work Order Entry Form]![Edit Mode].Visible = False
End If
End Sub

Open in new window

What error message do you get?  (*Exactly*)

What is the overall purpose here with this form?

You're closing a form, only to open it again in Read Only mode.
And then attempt to navigate back to the previous record.
Why?
Avatar of beef137

ASKER

Yea man, this thing is messed up. But I got it working right again (in a very quirky way). I just refreshed the form and now it is able to pick up the number from the textbox. Silly.