Position Form on the Newly inserted Record with VBA

[sesproul]

Hello,

I haven't been able to locate this by searching, but I imagine it is easy to do:

The User clicks a button that inserts a new record into the Database through SQL.

I need to show this new record in the form.



I think I need to do this, but am missing the specific code to position the record correctly

1: insert record into DB with VBA function, return the Primary Key
2: Refresh the Form so that it has visibility to the new record
3: Position the form on the returned Primary key.

I can do 1,2 only how do I perform 3?

Private Sub cmdAddNewPart_Click()
Dim lNewID As Long
    '---Add Part
    lNewID = CreateNewPart(txtPartFilter)
    '---Refresh Form
    Me.Refresh
    '---Position new record
    '???
End Sub

thanks.

Steve

[sesproul]

This seems to work: but I wonder if it is the best Way? Because it forces the filter instead of just positioning the record:

Private Sub cmdAddNewPart_Click()
Dim lNewID As Long
    '---Add Part
    lNewID = CreateNewPart(txtFilter)
    '---Refresh Form
    Me.Refresh
    '---Position new record
    Me.Filter = "[PartID] = " & lNewID
    Me.FilterOn = True

End Sub

[maximus]

This is what I assume:
1) You have a unbound form
2) You are using SQL to insert Data.

What have I Done:

Simple setup. 1 Table Member. A startup form will open. You will have two options. Using RecordSets and SQL. Select any one.

RecordSets:

The Code used in the command Buttons:

Add Record:
Private Sub Command8_Click()
On Error GoTo Err_Command8_Click
Set rs = CurrentDb.OpenRecordset("member")
rs.AddNew
rs!MemName = Me.Text12
rs!Skill = Me.Text14
rs!MemDesignation = Me.Text16
rs.Update
rs.Close
Set rs = Nothing
Me.Text12 = ""
Me.Text14 = ""
Me.Text16 = ""

Exit_Command8_Click:
Exit Sub

Err_Command8_Click:
MsgBox Err.Description
Resume Exit_Command8_Click

End Sub

View Last Entry:
Private Sub Command18_Click()
On Error GoTo Err_Command18_Click
Set rs = CurrentDb.OpenRecordset("member")
rs.MoveLast
Me.Text0 = rs!MemID
Me.Text2 = rs!MemName
Me.Text4 = rs!Skill
Me.Text6 = rs!MemDesignation
rs.Close
Set rs = Nothing

Exit_Command18_Click:
Exit Sub

Err_Command18_Click:
MsgBox Err.Description
Resume Exit_Command18_Click

End Sub

Clear data:
Private Sub Command21_Click()
On Error GoTo Err_Command21_Click

Me.Text0 = ""
Me.Text2 = ""
Me.Text4 = ""
Me.Text6 = ""

Exit_Command21_Click:
Exit Sub

Err_Command21_Click:
MsgBox Err.Description
Resume Exit_Command21_Click

End Sub

Explanation:

I have used a record set to both enter and find out the last entry. The Codes themselves are explanatory:


SQL:

This method I have used SQL to enter data and as well as find the last record.

Codes used:

Add Record
Private Sub Command8_Click()
On Error GoTo Err_Command8_Click
Dim strSQL As String
strSQL = "Insert Into Member(MemName,Skill,MemDesignation) Values('" & Me.Text12 & "','" & Me.Text14 & "','" & Me.Text16 & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Me.Text12 = ""
Me.Text14 = ""
Me.Text16 = ""

Exit_Command8_Click:
Exit Sub

Err_Command8_Click:
MsgBox Err.Description
Resume Exit_Command8_Click

End Sub

View Last Record:
Private Sub Command18_Click()
On Error GoTo Err_Command18_Click
Dim strSQL As String
Dim intCondition As String
intCondition = DMax("[MemID]", "Member")
strSQL = "Select * From member Where MemID=" & intCondition
Me.RecordSource = strSQL
Me.Text0.ControlSource = "MemID"
Me.Text2.ControlSource = "MemName"
Me.Text4.ControlSource = "Skill"
Me.Text6.ControlSource = "MemDesignation"

Exit_Command18_Click:
Exit Sub

Err_Command18_Click:
MsgBox Err.Description
Resume Exit_Command18_Click

End Sub

Clear Data:
Private Sub Command21_Click()
On Error GoTo Err_Command21_Click
Me.RecordSource = ""
Me.Text0.ControlSource = ""
Me.Text2.ControlSource = ""
Me.Text4.ControlSource = ""
Me.Text6.ControlSource = ""

Exit_Command21_Click:
Exit Sub

Err_Command21_Click:
MsgBox Err.Description
Resume Exit_Command21_Click

End Sub


Explanation: I have used an insert query to enter data and the used another select query as the record source of the form and the control source of the text boxes. I have used DMax function to locate the last entry.

I have tried my best hope this will help you. If this solves your problem mark the thread solved.

Refer to attached mdb.

[maximus]

is your problem solved was my post any help.

[sesproul]

Maximus, Sorry - I was pulled back into management the last week. I'll review your post today to see if it applies to my condition.


I'll get a chance to review this afternoon.

Thanks,

Steve

[sesproul]

Maximus does this permanently set the control on the unbound (or Bound) form.

intCondition = DMax("[MemID]", "Member")
strSQL = "Select * From member Where MemID=" & intCondition
Me.RecordSource = strSQL
Me.Text0.ControlSource = "MemID"
Me.Text2.ControlSource = "MemName"
Me.Text4.ControlSource = "Skill"
Me.Text6.ControlSource = "MemDesignation"

I actually use mostly bound forms.

Steve

[sesproul]

Maximus,

I actually use Bound forms and then use SQL to update certain fields, because I have a strange Lock error message that appears when I manually enter information into fields. "Something about another user has changed the data"

I will definitely look to integrate some of this code as it applies. thanks

Steve

[maximus]

Well this is quite interesting that you use sql to insert data but you say you form is bound to a recordsource. Do you use sql to enter data in the same table as you your record source.

Steve if it is not a problem for you can you elaborate on your problem coz I am very curious of about uncommon problems.

Basically unbound forms gives you a lot more flexibility than bound forms. What is your setting for your Record Locking (Nothing, All Records, Edited Record)

As you have seen right at the beginning of my post I had mentioned my assumptions. This codes work very well with unbound forms.

I think for your problem you might want to look at
DoCmd.GoToRecord , , acLast