Not in list event

[SAC]

I'm running code in the not in list event of a combobox to add a row and
then I am attempting to lookup a record based on the added row. The adding
of the row works fine, but not the looking up of the record. Here's the
code I'm using:

Response = acDataErrContinue

Dim intAnswer As Integer
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strOldValue As String
Dim strName As String
Dim varCost As Variant

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblOrg", dbOpenDynaset) 'Open the recordset
for the combobox table


intAnswer = MsgBox("Add " & NewData & " to Associations?", vbQuestion +
vbYesNo) 'Caption of ComboBox

If intAnswer = vbYes Then
With rst
.AddNew
!Organization = NewData
!Type = "Association"
'ID is autonumbered and a primary key field
.Update
End With

Response = acDataErrAdded ' Requery the combo box list.
************** Thought the new record would be in the combo box at this
point ******************

'************* Here's the line that doesn't work:
Me.frmBookingShowsAssociations.Form.Recordset.FindFirst "ID = " &
Me.cboAssociationLookup
'******************************************************************************
Else
Me.Undo
Response = acDataErrContinue ' Require the user to select
' an existing item
End If



Thanks for your help.

 

[Pieter Wijnen]

It won't, but you can get it from the recordset
With a Jet back-end you can reference it direct
ie
use ID = Rst.Fields("ID").Value

For other back-ends
you need to add

Rst.Bookmark = Rst.LastModified

First

HtH

Pieter

 

[SAC]

I tried that and it stays on the same ID as when I started the code so I
thought I could do rst.findfirst and then rst.findlast to go to the
lastrecord I just made and attempt a find based on that.

It does find the correct record (the last one) but this line doesn't find
the record on the form:

Me.frmBookingShowsAssociations.Form.Recordset.FindFirst "ID = " & rst!ID

Thought it should.

Thanks for your help.


"Pieter Wijnen"

 

[Pieter Wijnen]

because it's not "there"
you'll have to do a manual requery first

Pieter

 

[SAC]

rst!ID does have the correct number in it but the find still doesn't work.

I'll try requerying but since it's already "there" shouldn't the find work?

Thanks for your help.


"Pieter Wijnen"

 

[SAC]

I added a line cboAssociationLookup.Requery and received an error 2118 - You
must save the current field before you run the Requery action. So I'm at a
loss as to what to do.

Here's the code as it stands now:

Private Sub cboAssociationLookup_NotInList(NewData As String, Response As
Integer)
Response = acDataErrContinue

Dim intAnswer As Integer
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strOldValue As String
Dim strName As String
Dim varCost As Variant

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblOrg", dbOpenDynaset) 'Open the recordset
for the combobox table


intAnswer = MsgBox("Add " & NewData & " to Associations?", vbQuestion +
vbYesNo) 'Caption of ComboBox

If intAnswer = vbYes Then
With rst
.AddNew
!Organization = NewData
!Type = "Association"
'ID is autonumbered and a primary key field
.Update
End With

Response = acDataErrAdded ' Requery the combo box list.
MsgBox (Me!cboAssociationLookup)
cboAssociationLookup.Requery
Me.frmBookingShowsAssociations.Form.Recordset.FindFirst "ID = " &
Me.cboAssociationLookup
Else
Me.Undo
Response = acDataErrContinue ' Require the user to select
' an existing item
End If
End Sub


Thanks for your help...I'm stuck!!


"Pieter Wijnen"

 

[Pieter Wijnen]

You *still* cannot use the value from the combo as the criteria & it's the
recordsetclone you need to requery

Pieter

 

[SAC]

OK, I'm not sure how to do that.

Also, could you please help me understand why the combo box won't
work...when I check it's value, it's got the right one in it so I thought I
could find using it.

Or I also tried finding using the rst!ID after I moved to the last record...

I think I just lack understanding.

Thanks for your help.


"Pieter Wijnen"

 

[SAC]

I'll check out recordsetclone and see if I can figure it out.

Thanks.
"Pieter Wijnen"

 

[SAC]

OK, I tried the recordsetclone property, but obviously I don't know how to
use it. The code just recycles when I requery it.

Then I tried adding a record with the rst.addnew...rst.update and then did a
movefirst and a move last in with the rst. The rst!id is correct at that
point so I thought I could use it in the find, but it still didn't work.
Seems like this should work!!

Any more ideas or what do I need to change?

I appreciate your help.

"Pieter Wijnen"

 

[SAC]

Got it!!

Thanks for pointing me in the right direction!!


"Pieter Wijnen"