position record when opening form

[Guest]

I am trying to open a form and position to the first record in the table and
it's not working correctly. I am first deleting the table and then adding a
record to the table and inserting the userID and authority. Then I want to
open my form to that record. Anyone have any ideas?


Private Sub Accept_Click()
On Error GoTo Err_Accept_Click
Dim strSQL As String
DoCmd.SetWarnings False

DoCmd.Close

strSQL = "DELETE [WorkTable].* FROM [WorkTable];"

DoCmd.RunSQL strSQL

strSQL = "INSERT INTO [WorkTable] ( UserName, Authority )" & _
"SELECT UserID.UserID, UserID.Authority FROM UserID;"

DoCmd.RunSQL strSQL

DoCmd.GoToRecord , "Assign Policy", acLast
DoCmd.OpenForm "Assign Policy", acNormal

Exit_Accept_Click:
Exit Sub

Err_Accept_Click:
MsgBox Err.Description
Resume Exit_Accept_Click

End Sub

 

[Stefan Hoffmann]

hi Leslie,

I am trying to open a form and position to the first record in the table and
it's not working correctly. I am first deleting the table and then adding a
record to the table and inserting the userID and authority. Then I want to
open my form to that record. Anyone have any ideas?

I don't understand your problem. As you have only one record in your
table, you'r always positioned on that record.


mfG
--> stefan <--

 

[Guest]

After "Policy Assigned" closes I am returned to my main form "Assign Policy"
with the fields all #Deleted and it's not opened to the record I just added
in the code.

 

[Stefan Hoffmann]

hi Leslie,

After "Policy Assigned" closes I am returned to my main form "Assign Policy"
with the fields all #Deleted and it's not opened to the record I just added
in the code.

Okay. Try a Requery in the on activate event:

Private Sub Form_Activate()

Requery

End Sub


mfG
--> stefan <--

 

[Guest]

It still shows #deleted when I placed the requery on activate event.

 

[Stefan Hoffmann]

hi Leslie,

It still shows #deleted when I placed the requery on activate event.

Is your form a endless (continous) form or are you displaying the data
in a subform?


mfG
--> stefan <--

 

[Guest]

it's default view is Single Form
--
Leslie

hi Leslie,

Is your form a endless (continous) form or are you displaying the data
in a subform?


mfG
--> stefan <--

 

[Guest]

I am still looking for a solution to this if anyone else has a suggestion. I
have tried just about everything I can think of.

 

[BruceM]

In your original post you said you deleted the table, then added a record to
it. Did you re-create the table, or what exactly? Why did you delete it in
the first place?

 

[Guest]

It's just a work table that holds one record. When the user accepts the new
policy number i want to clear the table and then add a new record with the
userID and user authority to the record. The user will then fillin the rest
of the fields.

code is:

Private Sub Accept_Click()
On Error GoTo Err_Accept_Click
Dim strSQL As String
DoCmd.SetWarnings False

DoCmd.Close

'clear worktable
strSQL = "DELETE [WorkTable].* FROM [WorkTable];"
DoCmd.RunSQL strSQL

'add userID and authority to next record
strSQL = "INSERT INTO [WorkTable] ( UserName, Authority )" & _
"SELECT UserID.UserID, UserID.Authority FROM UserID;"
DoCmd.RunSQL strSQL

Exit_Accept_Click:
Exit Sub

Err_Accept_Click:
MsgBox Err.Description
Resume Exit_Accept_Click

End Sub

 

[Stefan Hoffmann]

hi Leslie,

It's just a work table that holds one record. When the user accepts the new
policy number i want to clear the table and then add a new record with the
userID and user authority to the record. The user will then fillin the rest
of the fields.

The problem seems to be somthing different. Can you post the entire form
code?


mfG
--> stefan <--