cboBox NotInList Problem

[Gina]

Hi all.

I have created a table
TextID autonumber
Description text

on my form a cboBox takes its data directly from the table
SELECT [tblText].[TextID], [tblText].[Description] FROM tblText;

both columns are shown, only data from list: NO

What I would like to do is when a description is not already in the table,
to add the new one as new record and requery the cboBox to reflect the newly
done adding.
This is my code and when I try to show the new record in the cboBox I get a
message that I first have to save the field ????? line ***

__________________________________________________________
Private Sub cboText_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim rs As DAO.Recordset

strSQL = "SELECT COUNT(tblText.Description) FROM tblText WHERE
tblText.Description = '" & NewData & "' "
Set rs = CurrentDb.OpenRecordset(strSQL)

If rs.AbsolutePosition = 0 Then

strSQL = "INSERT INTO tblText(Description) VALUES ( '" & NewData &
"' )"
DoCmd.RunSQL (strSQL)

End If

Response = acDataErrContinue
*** 'cboText.Requery
End Sub
_____________________________________________________________

Many Thanks
Gina

 

[RuralGuy]

Hi all.

I have created a table
TextID autonumber
Description text

on my form a cboBox takes its data directly from the table
SELECT [tblText].[TextID], [tblText].[Description] FROM tblText;

both columns are shown, only data from list: NO

What I would like to do is when a description is not already in the
table, to add the new one as new record and requery the cboBox to
reflect the newly done adding.
This is my code and when I try to show the new record in the cboBox I
get a message that I first have to save the field ????? line ***

__________________________________________________________
Private Sub cboText_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim rs As DAO.Recordset

strSQL = "SELECT COUNT(tblText.Description) FROM tblText WHERE
tblText.Description = '" & NewData & "' "
Set rs = CurrentDb.OpenRecordset(strSQL)

If rs.AbsolutePosition = 0 Then

strSQL = "INSERT INTO tblText(Description) VALUES ( '" &
NewData &
"' )"
DoCmd.RunSQL (strSQL)

End If

Response = acDataErrContinue
*** 'cboText.Requery
End Sub
_____________________________________________________________

Many Thanks
Gina

Hi Gina,

The only reason you are in the "cboText_NotInList" code is because
well the "NewData" was not in the list! I think you can eliminate the
first check and assume this Description "as entered" is not there.

I usually put a double check question just in case...like:

--- Warning, air code (Not tested) ---

Private Sub cboText_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ cboText _NotInList

'-- We may need to add another Description
Response = MsgBox("[" & NewData & "] " & _
"is not a current Description..." & vbCr & vbCr & _
"Would you like to add this New Description to the DataBase?", vbYesNo)

If Response = vbYes Then
'-- Create a new Description record
Dim db As DAO.Database
Dim MySql As String
Set db = CurrentDb()
MySql = "Insert Into tblText(Description) " & _
"Values(""" & NewData & """)"
db.Execute MySql, dbFailOnError
' The next line will tell Access to requery the cbo!!
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Exit_cboSupplier_NotInList:
Resume Next
Set db = Nothing
Exit Sub

Err_cboSupplier_NotInList:
' Add error handling code here
Resume Exit_cboSupplier_NotInList

End Sub

I think this code addresses all of your concerns.

HTH

 

[Gina]

Hi RuralGuy.



Many Thanks for your very detailed 'air code' .....

The problem I have is that I would like to show the newly added record
(description) in the cboBox ....

the *** line cboText.Requery

That didn't work for some reason it always jumped back into the NotInList
code ..... and didn't come out again .... then I experimented with

Only values from list - YES
Only values from list - NO

I do not know where and when to do the requery of that cboBox ....

you suggested:

' The next line will tell Access to requery the cbo!!

what exactly do you mean by that ??

Gina

Hi all.

I have created a table
TextID autonumber
Description text

on my form a cboBox takes its data directly from the table
SELECT [tblText].[TextID], [tblText].[Description] FROM tblText;

both columns are shown, only data from list: NO

What I would like to do is when a description is not already in the
table, to add the new one as new record and requery the cboBox to
reflect the newly done adding.
This is my code and when I try to show the new record in the cboBox I
get a message that I first have to save the field ????? line ***

__________________________________________________________
Private Sub cboText_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim rs As DAO.Recordset

strSQL = "SELECT COUNT(tblText.Description) FROM tblText WHERE
tblText.Description = '" & NewData & "' "
Set rs = CurrentDb.OpenRecordset(strSQL)

If rs.AbsolutePosition = 0 Then

strSQL = "INSERT INTO tblText(Description) VALUES ( '" &
NewData &
"' )"
DoCmd.RunSQL (strSQL)

End If

Response = acDataErrContinue
*** 'cboText.Requery
End Sub
_____________________________________________________________

Many Thanks
Gina

Hi Gina,

The only reason you are in the "cboText_NotInList" code is because
well the "NewData" was not in the list! I think you can eliminate the
first check and assume this Description "as entered" is not there.

I usually put a double check question just in case...like:

--- Warning, air code (Not tested) ---

Private Sub cboText_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ cboText _NotInList

'-- We may need to add another Description
Response = MsgBox("[" & NewData & "] " & _
"is not a current Description..." & vbCr & vbCr & _
"Would you like to add this New Description to the DataBase?", vbYesNo)

If Response = vbYes Then
'-- Create a new Description record
Dim db As DAO.Database
Dim MySql As String
Set db = CurrentDb()
MySql = "Insert Into tblText(Description) " & _
"Values(""" & NewData & """)"
db.Execute MySql, dbFailOnError
' The next line will tell Access to requery the cbo!!
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Exit_cboSupplier_NotInList:
Resume Next
Set db = Nothing
Exit Sub

Err_cboSupplier_NotInList:
' Add error handling code here
Resume Exit_cboSupplier_NotInList

End Sub

I think this code addresses all of your concerns.

HTH

 

[Douglas J. Steele]

As RuralGuy said in his sample, Response = acDataErrAdded tells Access to
requery the combobox. You shouldn't have your own explicit Requery in that
routine.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi RuralGuy.



Many Thanks for your very detailed 'air code' .....

The problem I have is that I would like to show the newly added record
(description) in the cboBox ....

the *** line cboText.Requery

That didn't work for some reason it always jumped back into the NotInList
code ..... and didn't come out again .... then I experimented with

Only values from list - YES
Only values from list - NO

I do not know where and when to do the requery of that cboBox ....

you suggested:

' The next line will tell Access to requery the cbo!!

what exactly do you mean by that ??

Gina

Hi all.

I have created a table
TextID autonumber
Description text

on my form a cboBox takes its data directly from the table
SELECT [tblText].[TextID], [tblText].[Description] FROM tblText;

both columns are shown, only data from list: NO

What I would like to do is when a description is not already in the
table, to add the new one as new record and requery the cboBox to
reflect the newly done adding.
This is my code and when I try to show the new record in the cboBox I
get a message that I first have to save the field ????? line ***

__________________________________________________________
Private Sub cboText_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim rs As DAO.Recordset

strSQL = "SELECT COUNT(tblText.Description) FROM tblText WHERE
tblText.Description = '" & NewData & "' "
Set rs = CurrentDb.OpenRecordset(strSQL)

If rs.AbsolutePosition = 0 Then

strSQL = "INSERT INTO tblText(Description) VALUES ( '" &
NewData &
"' )"
DoCmd.RunSQL (strSQL)

End If

Response = acDataErrContinue
*** 'cboText.Requery
End Sub
_____________________________________________________________

Many Thanks
Gina

Hi Gina,

The only reason you are in the "cboText_NotInList" code is because
well the "NewData" was not in the list! I think you can eliminate the
first check and assume this Description "as entered" is not there.

I usually put a double check question just in case...like:

--- Warning, air code (Not tested) ---

Private Sub cboText_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ cboText _NotInList

'-- We may need to add another Description
Response = MsgBox("[" & NewData & "] " & _
"is not a current Description..." & vbCr & vbCr & _
"Would you like to add this New Description to the DataBase?", vbYesNo)

If Response = vbYes Then
'-- Create a new Description record
Dim db As DAO.Database
Dim MySql As String
Set db = CurrentDb()
MySql = "Insert Into tblText(Description) " & _
"Values(""" & NewData & """)"
db.Execute MySql, dbFailOnError
' The next line will tell Access to requery the cbo!!
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Exit_cboSupplier_NotInList:
Resume Next
Set db = Nothing
Exit Sub

Err_cboSupplier_NotInList:
' Add error handling code here
Resume Exit_cboSupplier_NotInList

End Sub

I think this code addresses all of your concerns.

HTH

 

[RuralGuy]

Hi RuralGuy.



Many Thanks for your very detailed 'air code' .....

The problem I have is that I would like to show the newly added record
(description) in the cboBox ....

the *** line cboText.Requery

That didn't work for some reason it always jumped back into the
NotInList code ..... and didn't come out again .... then I
experimented with

Only values from list - YES
Only values from list - NO

I do not know where and when to do the requery of that cboBox ....

you suggested:

what exactly do you mean by that ??

Gina

<snip>

Hi Gina,

Did you try the "air code" I listed? Where does the cboBox end up?
As Doug said, your additional Requery should not be used, nor
is it necessary.

Post back with your results.

 

[Gina]

Thanks Guys ....

now I understand !!!!

gonna try it with a fresh morning brain ;-))

Gina