NotInList issue

P

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
 
K

Keith Wilby

PMC1 said:
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
 
K

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
 
P

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


PMC1 said:
Hi Keith,

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

Paul
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top