By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,501 Members | 2,797 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,501 IT Pros & Developers. It's quick & easy.

Data Entry Form Problem

P: n/a
I have a data entry form with a combo box to look up an entrant's name.
If the name is already in the table then it pulls up the record and
that part of the form works fine. If the name isn't in the table then
I want to be able to add it but this part is not working. I enter a
name that is not in the table and press Enter. I step through the code
and it appears to work until I get to "Exit Sub". At this point a
warning message box appears with the message "Characters found after
end of SQL statement." and an OK button. When I click OK another
message box pops up with the message "The text you entered isn't an
item in the list. Select an item from the list, or enter text that
matches one of the listed items."; also, with an OK button. I click on
it and the yellow hi-lite goes away and nothing else occurs. I go to
the form itself and the combo box has the focus but the form is not on
a new record. If I press Enter the process will repeat itself. I know
I could already be finished if I had made it an unbound form and used
code but from some of the posts I have read on the bound vs. unbound
issue I wanted to learn how to do this the bound way. That way I could
have flexibility in what approach to take in the future. Below is my
code for the "beforeupdate" and "notinlist" events (once I had it
working I was going to rename my controls). I would appreciate it if
someone could tell me what I am doing wrong and how to correct it.

Private Sub Combo23_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Routine
'variable captures procedure name that error occurs in
strProcName = "Combo23_BeforeUpdate"

Me.RecordsetClone.FindFirst "[EntryName] = '" & Me![Combo23] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark

Exit_Routine:
Exit Sub
Err_Routine:
Select Case Err
Case Else
Err_General
End Select
Resume Exit_Routine
End Sub
Private Sub Combo23_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Routine
'variable captures procedure name that error occurs in
strProcName = "Combo23_NotInList"

Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!Combo23
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContinue
ctl.Undo
End If

Exit_Routine:
Exit Sub
Err_Routine:
Select Case Err
Case Else
Err_General
End Select
Resume Exit_Routine
End Sub
Thanks for any assistance,
Alex

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a

Alex,

Your issue is with the line:

ctl.RowSource = ctl.RowSource & ";" & NewData

If the RowSourceType property of your combobox is 'Table/Query', the
combobox is getting its list from a Table, Query or SQL statement. If
it is 'Value List', the rowsource is a string of elements separated by
semicolons (;).

It sounds like you are trying to add a element using the Value List
style to a SQL statement, wherein lies the problem. Jet interprets the
; as the end of a SQL statement, so it throws an error when it finds
text after the ;.

You really should be adding your new record to the underlying table.
There are many ways of doing this. Here is a simple form-based method:

DoCmd.GoToRecord acDataForm, "YourEntryForm", acNewRec
Me!EntryName = NewData
Me.Refresh ' Save the record
Combo23.Requery ' Update the combo

That should get you closer to your goal.

-Ken

Al***********@ncmail.net wrote:
I have a data entry form with a combo box to look up an entrant's name. If the name is already in the table then it pulls up the record and
that part of the form works fine. If the name isn't in the table then I want to be able to add it but this part is not working. I enter a
name that is not in the table and press Enter. I step through the code and it appears to work until I get to "Exit Sub". At this point a
warning message box appears with the message "Characters found after
end of SQL statement." and an OK button. When I click OK another
message box pops up with the message "The text you entered isn't an
item in the list. Select an item from the list, or enter text that
matches one of the listed items."; also, with an OK button. I click on it and the yellow hi-lite goes away and nothing else occurs. I go to
the form itself and the combo box has the focus but the form is not on a new record. If I press Enter the process will repeat itself. I know I could already be finished if I had made it an unbound form and used
code but from some of the posts I have read on the bound vs. unbound
issue I wanted to learn how to do this the bound way. That way I could have flexibility in what approach to take in the future. Below is my
code for the "beforeupdate" and "notinlist" events (once I had it
working I was going to rename my controls). I would appreciate it if
someone could tell me what I am doing wrong and how to correct it.

Private Sub Combo23_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Routine
'variable captures procedure name that error occurs in
strProcName = "Combo23_BeforeUpdate"

Me.RecordsetClone.FindFirst "[EntryName] = '" & Me![Combo23] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark

Exit_Routine:
Exit Sub
Err_Routine:
Select Case Err
Case Else
Err_General
End Select
Resume Exit_Routine
End Sub
Private Sub Combo23_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Routine
'variable captures procedure name that error occurs in
strProcName = "Combo23_NotInList"

Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!Combo23
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContinue
ctl.Undo
End If

Exit_Routine:
Exit Sub
Err_Routine:
Select Case Err
Case Else
Err_General
End Select
Resume Exit_Routine
End Sub
Thanks for any assistance,
Alex


Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.