notinlist event gives error

  • Thread starter Thread starter Fredrated
  • Start date Start date
F

Fredrated

I have read the posts and visited the vb links related to this action, but
still can't get it to work properly in my combo notinlist event. After
inserting the new item into the underlying table, the request to requery the
combo box results in the error (code follows): 'Run time error 2118 You must
save the current field before you run the requery action'

combo box properties are
Row Source Type: Table/Query
Row Source: SELECT tStaff.desc FROM tStaff;
Limit to list: yes
Column Count: 1
Bound Column: 1

not-in-list code is:
------------------------
Private Sub Combo0_NotInList(NewData As String, Response As Integer)
Dim strSQL As String

If MsgBox("The employee '" & NewData & "' is not on the list." & vbCrLf &
"Add them?", vbYesNo) = vbYes Then

strSQL = "INSERT INTO tStaff ( [desc] ) SELECT " & Chr(34) & NewData &
Chr(34) & " AS exp1;"

Call RunSQLquery(strSQL)

Me.Combo0.Requery
Else
Response = acDataErrContinue
End If

ExitCbo:
Exit Sub

End Sub
-----------------------------

RunSQLquery is a public function which creates a temporary querydef out of
the passed string, then executes it. This works and the item is added to the
table.

But, when I ask the combo box to requery itself after the item is added, I
get the error given at the beginning of this post.

Any ideas?

Thanks in advance for any help.

Fred
 
Fredrated said:
I have read the posts and visited the vb links related to this action, but
still can't get it to work properly in my combo notinlist event. After
inserting the new item into the underlying table, the request to requery
the
combo box results in the error (code follows): 'Run time error 2118 You
must
save the current field before you run the requery action'

combo box properties are
Row Source Type: Table/Query
Row Source: SELECT tStaff.desc FROM tStaff;
Limit to list: yes
Column Count: 1
Bound Column: 1

not-in-list code is:
------------------------
Private Sub Combo0_NotInList(NewData As String, Response As Integer)
Dim strSQL As String

If MsgBox("The employee '" & NewData & "' is not on the list." & vbCrLf &
"Add them?", vbYesNo) = vbYes Then

strSQL = "INSERT INTO tStaff ( [desc] ) SELECT " & Chr(34) & NewData &
Chr(34) & " AS exp1;"

Call RunSQLquery(strSQL)

Me.Combo0.Requery
Else
Response = acDataErrContinue
End If

ExitCbo:
Exit Sub

End Sub
-----------------------------

RunSQLquery is a public function which creates a temporary querydef out of
the passed string, then executes it. This works and the item is added to
the
table.

But, when I ask the combo box to requery itself after the item is added, I
get the error given at the beginning of this post.

Any ideas?

Thanks in advance for any help.

Fred
Click to expand...

Replace this line:

Me.Combo0.Requery

with:

Response = acDataErrAdded

to inform Access that you've added the new data to the table.
 
Stuart McCall said:
Fredrated said:
I have read the posts and visited the vb links related to this action, but
still can't get it to work properly in my combo notinlist event. After
inserting the new item into the underlying table, the request to requery
the
combo box results in the error (code follows): 'Run time error 2118 You
must
save the current field before you run the requery action'

combo box properties are
Row Source Type: Table/Query
Row Source: SELECT tStaff.desc FROM tStaff;
Limit to list: yes
Column Count: 1
Bound Column: 1

not-in-list code is:
------------------------
Private Sub Combo0_NotInList(NewData As String, Response As Integer)
Dim strSQL As String

If MsgBox("The employee '" & NewData & "' is not on the list." & vbCrLf &
"Add them?", vbYesNo) = vbYes Then

strSQL = "INSERT INTO tStaff ( [desc] ) SELECT " & Chr(34) & NewData &
Chr(34) & " AS exp1;"

Call RunSQLquery(strSQL)

Me.Combo0.Requery
Else
Response = acDataErrContinue
End If

ExitCbo:
Exit Sub

End Sub
-----------------------------

RunSQLquery is a public function which creates a temporary querydef out of
the passed string, then executes it. This works and the item is added to
the
table.

But, when I ask the combo box to requery itself after the item is added, I
get the error given at the beginning of this post.

Any ideas?

Thanks in advance for any help.

Fred
Click to expand...

Replace this line:

Me.Combo0.Requery

with:

Response = acDataErrAdded

to inform Access that you've added the new data to the table.
Click to expand...

I Commented-out the requery lline and tried this, but then I get the error
message
"The text you entered isn't an item in the list.

Select an item from the list....."

Then I tried adding the requery line back, to execute after the "Response =
acDataErrAdded" line, but that only results in the original error.

Thanks
 
Guess it had to think about it, but now the code you suggested in place of
requery is doing the job, no more errors, thanks!

Fredrated said:
Stuart McCall said:
Fredrated said:
I have read the posts and visited the vb links related to this action, but
still can't get it to work properly in my combo notinlist event. After
inserting the new item into the underlying table, the request to requery
the
combo box results in the error (code follows): 'Run time error 2118 You
must
save the current field before you run the requery action'

combo box properties are
Row Source Type: Table/Query
Row Source: SELECT tStaff.desc FROM tStaff;
Limit to list: yes
Column Count: 1
Bound Column: 1

not-in-list code is:
------------------------
Private Sub Combo0_NotInList(NewData As String, Response As Integer)
Dim strSQL As String

If MsgBox("The employee '" & NewData & "' is not on the list." & vbCrLf &
"Add them?", vbYesNo) = vbYes Then

strSQL = "INSERT INTO tStaff ( [desc] ) SELECT " & Chr(34) & NewData &
Chr(34) & " AS exp1;"

Call RunSQLquery(strSQL)

Me.Combo0.Requery
Else
Response = acDataErrContinue
End If

ExitCbo:
Exit Sub

End Sub
-----------------------------

RunSQLquery is a public function which creates a temporary querydef out of
the passed string, then executes it. This works and the item is added to
the
table.

But, when I ask the combo box to requery itself after the item is added, I
get the error given at the beginning of this post.

Any ideas?

Thanks in advance for any help.

Fred
Click to expand...

Replace this line:

Me.Combo0.Requery

with:

Response = acDataErrAdded

to inform Access that you've added the new data to the table.
Click to expand...

I Commented-out the requery lline and tried this, but then I get the error
message
"The text you entered isn't an item in the list.

Select an item from the list....."

Then I tried adding the requery line back, to execute after the "Response =
acDataErrAdded" line, but that only results in the original error.

Thanks
Click to expand...
 
Back
Top