Open new record in form for editing after addind using NotInList

[Paul]

Hi,

I'm useing the following code to allow users to enter a new record number in
a combobox and have that number added to the list:


Private Sub cmbRefID_NotInList(NewData As String, Response As Integer)
Response = acDataErrContinue
Dim db As Database
Dim rst As Recordset
Dim sqlTransRef As String


Set db = CurrentDb()
sqlTransRef = "SELECT [tbl46GHeader].[FormID], [tbl46GHeader].[RefID]
FROM [tbl46GHeader] Order by RefID Desc"
Set rst = db.OpenRecordset(sqlTransRef, dbOpenDynaset)
rst.AddNew
rst!RefID = NewData
rst.Update
rst.Requery
rst.Close

Response = acDataErrAdded

End If

End Sub

This works fine in that it adds the new record (RefID). The problem is that
this combobox also has an Afterupdate event which is used to update the form
with the record selected in the combobox. Whats happening is after the above
procedure runs the first record in the database is selected and the form is
populated with this data. How do I get the form to requery the table and
allow me to fill in the fields for the record create by the code above?

Thanks

 

[RuralGuy]

Assuming your form is bound to the tbl46GHeader table/query then something like
the following in the AfterUpdate event of the ComboBox should do it!

DoCmd.Requery ' Get any changes to the table first.

' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[RefID] = " & Me.ComboBox.Column(0)
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

Using the name of your ComboBox of course *and* this assumes the [RefID] field
is the 1st fields in the underlying query/table of the ComboBox.

Hi,

I'm useing the following code to allow users to enter a new record number in
a combobox and have that number added to the list:


Private Sub cmbRefID_NotInList(NewData As String, Response As Integer)
Response = acDataErrContinue
Dim db As Database
Dim rst As Recordset
Dim sqlTransRef As String


Set db = CurrentDb()
sqlTransRef = "SELECT [tbl46GHeader].[FormID], [tbl46GHeader].[RefID]
FROM [tbl46GHeader] Order by RefID Desc"
Set rst = db.OpenRecordset(sqlTransRef, dbOpenDynaset)
rst.AddNew
rst!RefID = NewData
rst.Update
rst.Requery
rst.Close

Response = acDataErrAdded

End If

End Sub

This works fine in that it adds the new record (RefID). The problem is that
this combobox also has an Afterupdate event which is used to update the form
with the record selected in the combobox. Whats happening is after the above
procedure runs the first record in the database is selected and the form is
populated with this data. How do I get the form to requery the table and
allow me to fill in the fields for the record create by the code above?

Thanks

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[Paul]

Thanks for that. Thats got it working!

Assuming your form is bound to the tbl46GHeader table/query then something
like
the following in the AfterUpdate event of the ComboBox should do it!

DoCmd.Requery ' Get any changes to the table first.

' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[RefID] = " & Me.ComboBox.Column(0)
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

Using the name of your ComboBox of course *and* this assumes the [RefID]
field
is the 1st fields in the underlying query/table of the ComboBox.

Hi,

I'm useing the following code to allow users to enter a new record number
in
a combobox and have that number added to the list:


Private Sub cmbRefID_NotInList(NewData As String, Response As Integer)
Response = acDataErrContinue
Dim db As Database
Dim rst As Recordset
Dim sqlTransRef As String


Set db = CurrentDb()
sqlTransRef = "SELECT [tbl46GHeader].[FormID], [tbl46GHeader].[RefID]
FROM [tbl46GHeader] Order by RefID Desc"
Set rst = db.OpenRecordset(sqlTransRef, dbOpenDynaset)
rst.AddNew
rst!RefID = NewData
rst.Update
rst.Requery
rst.Close

Response = acDataErrAdded

End If

End Sub

This works fine in that it adds the new record (RefID). The problem is
that
this combobox also has an Afterupdate event which is used to update the
form
with the record selected in the combobox. Whats happening is after the
above
procedure runs the first record in the database is selected and the form
is
populated with this data. How do I get the form to requery the table and
allow me to fill in the fields for the record create by the code above?

Thanks

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[RuralGuy]

Glad I could help.

Thanks for that. Thats got it working!

Assuming your form is bound to the tbl46GHeader table/query then something
like
the following in the AfterUpdate event of the ComboBox should do it!

DoCmd.Requery ' Get any changes to the table first.

' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[RefID] = " & Me.ComboBox.Column(0)
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

Using the name of your ComboBox of course *and* this assumes the [RefID]
field
is the 1st fields in the underlying query/table of the ComboBox.

Hi,

I'm useing the following code to allow users to enter a new record number
in
a combobox and have that number added to the list:


Private Sub cmbRefID_NotInList(NewData As String, Response As Integer)
Response = acDataErrContinue
Dim db As Database
Dim rst As Recordset
Dim sqlTransRef As String


Set db = CurrentDb()
sqlTransRef = "SELECT [tbl46GHeader].[FormID], [tbl46GHeader].[RefID]
FROM [tbl46GHeader] Order by RefID Desc"
Set rst = db.OpenRecordset(sqlTransRef, dbOpenDynaset)
rst.AddNew
rst!RefID = NewData
rst.Update
rst.Requery
rst.Close

Response = acDataErrAdded

End If

End Sub

This works fine in that it adds the new record (RefID). The problem is
that
this combobox also has an Afterupdate event which is used to update the
form
with the record selected in the combobox. Whats happening is after the
above
procedure runs the first record in the database is selected and the form
is
populated with this data. How do I get the form to requery the table and
allow me to fill in the fields for the record create by the code above?

Thanks

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.