NotInList issue

[PMC1]

Hi,

I have a combo box in a form which is used to select records from a
table for the purpose of editing or changing. I also want the user to
be able to create a new record by inputing a new reference number in
the combobox. For this purpose I'm using the NotInList event which
activates the following code:

Private Sub cmbRecIncNo_NotInList(NewData As String, Response As
Integer)

Response = acDataErrContinue
If MsgBox("Record " & NewData & " does not exist. Create new
Record?", vbYesNo) = vbYes Then
Dim db As Database
Dim rst As Recordset
Dim sqlRecIncNo As String


Set db = CurrentDb()
sqlRecInNo= "SELECT [tblInvoices].[RecIncNo]FROM [tblInvoices]
Order by [RecIncNo] Desc"
Set rst = db.OpenRecordset(sqlRecIncNo, dbOpenDynaset)
rst.AddNew
rst!RecIncNo = NewData
rst.Update
rst.Close

Response = acDataErrAdded

End If

Me.cmbRecIncNo = Null
Me.Refresh
Me.cmbRecIncNo.Requery
Me.Requery

DoCmd.FindRecord NewData, , True, , True

end sub


This works to a point in that the record is added, the form refreshes
and requeries ok as does the combobox but the problem is with the last
section i.e. "Docmd.FindRecord..." This doesn't bring the form to the
Reference number just created but if the user selects the new entry
from the combobox the form will display the entry and allow the user to
enter the details.

How do I get the form to go to the new record?

Note: I'm using Access 2003

Any help on this would be appreciated

...pc

 

[Keith Wilby]

Hi,


This works to a point in that the record is added, the form refreshes
and requeries ok as does the combobox but the problem is with the last
section i.e. "Docmd.FindRecord..." This doesn't bring the form to the
Reference number just created but if the user selects the new entry
from the combobox the form will display the entry and allow the user to
enter the details.

How do I get the form to go to the new record?

Just off the top of my head, have you tried using the "Bookmark" property?

Regards,
Keith.
www.keithwilby.com

 

[PMC1]

Hi Keith,

I haven't tried Bookmark. What did you have in mind?

Paul

 

[Klatuu]

Private Sub cmbRecIncNo_NotInList(NewData As String, Response As
Integer)
Dim rst As Recordset

Response = acDataErrContinue
If MsgBox("Record " & NewData & _
" does not exist. Create new Record?", vbYesNo) = vbYes Then
Set db = CurrentDb()
CurrentDb.Execoute("INSERT INTO tblInvoices (RecIncNo), VALUES(" & _
NewData & ");"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[RecIncNo] = " & NewData
Me.Bookmakr = rst.Bookmark
Response = acDataErrAdded

End If

end sub

 

[PMC1]

Thanks Klatuu / Dave,

That did the trick!

Regards

Paul

Private Sub cmbRecIncNo_NotInList(NewData As String, Response As
Integer)
Dim rst As Recordset

Response = acDataErrContinue
If MsgBox("Record " & NewData & _
" does not exist. Create new Record?", vbYesNo) = vbYes Then
Set db = CurrentDb()
CurrentDb.Execoute("INSERT INTO tblInvoices (RecIncNo), VALUES(" & _
NewData & ");"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[RecIncNo] = " & NewData
Me.Bookmakr = rst.Bookmark
Response = acDataErrAdded

End If

end sub

Hi Keith,

I haven't tried Bookmark. What did you have in mind?

Paul

 

[Keith Wilby]

Thanks for jumping in Klatuu, I've been away for the weekend.

Keith.