value in combo box

V

vandy

Hi all,

I have to check if the project no is not in the combo list and if it is not
than the system should pop a msg telling project no not found do you want to
add and than add the data in the list.

if it is a small combo box than one can see the values but if it is an
extended list it is good to have a confirmation msg.

I used 2 events - after update to check for values and not in list to update
the combo. Here is my problem the msg box is not firing as intended and can i
program in the not inlist event to check for both.

I used allenbrow's code as follows

Private Sub cmbpno_AfterUpdate()

Dim rs As DAO.Recordset

If Not IsNull(Me.cmbpno) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[PID] = " & Me.cmbpno

If rs.NoMatch Then
MsgBox "Project DOES NOT EXIST?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

End Sub
(here i get this msg even if there is a match found)

Private Sub cmbpno_NotInList(NewData As String, Response As Integer)

Dim rstproject As ADODB.Recordset
Dim intAnswer As Integer

intAnswer = MsgBox("Do you want to Add " & NewData & " to the list of
projects?", _
vbQuestion + vbYesNo)

If intAnswer = vbYes Then
DoCmd.OpenForm "Add projects", acNormal, "", "", acFormAdd, acDialog
Me.cmbpno.SetFocus
Me.cmbpno.Undo
Response = acDataErrContinue


End If

End Sub

thanks for your help in advance
 
K

Klatuu

The only obvious point of question is the data type of [PID]
If it is numeric, this is not the problem, if it is text, then you need the
proper delimiters:
rs.FindFirst "[PID] = '" & Me.cmbpno & "'"
 
V

vandy

Hi Klatuu,

PID is a number and I have debugged the code and the PID gets displayed.
if rs.nomatch only than should i get the msg displayed. I am getting this
popup msg every time i click on the combo box.

Any help would be greatly appreciated.


Klatuu said:
The only obvious point of question is the data type of [PID]
If it is numeric, this is not the problem, if it is text, then you need the
proper delimiters:
rs.FindFirst "[PID] = '" & Me.cmbpno & "'"

--
Dave Hargis, Microsoft Access MVP


vandy said:
Hi all,

I have to check if the project no is not in the combo list and if it is not
than the system should pop a msg telling project no not found do you want to
add and than add the data in the list.

if it is a small combo box than one can see the values but if it is an
extended list it is good to have a confirmation msg.

I used 2 events - after update to check for values and not in list to update
the combo. Here is my problem the msg box is not firing as intended and can i
program in the not inlist event to check for both.

I used allenbrow's code as follows

Private Sub cmbpno_AfterUpdate()

Dim rs As DAO.Recordset

If Not IsNull(Me.cmbpno) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[PID] = " & Me.cmbpno

If rs.NoMatch Then
MsgBox "Project DOES NOT EXIST?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

End Sub
(here i get this msg even if there is a match found)

Private Sub cmbpno_NotInList(NewData As String, Response As Integer)

Dim rstproject As ADODB.Recordset
Dim intAnswer As Integer

intAnswer = MsgBox("Do you want to Add " & NewData & " to the list of
projects?", _
vbQuestion + vbYesNo)

If intAnswer = vbYes Then
DoCmd.OpenForm "Add projects", acNormal, "", "", acFormAdd, acDialog
Me.cmbpno.SetFocus
Me.cmbpno.Undo
Response = acDataErrContinue


End If

End Sub

thanks for your help in advance
Click to expand...
Click to expand...
 
K

Klatuu

How many columns does your combo have?
Which column is the bound column?
Will the bound column be the one you expect to contain the vaule?
--
Dave Hargis, Microsoft Access MVP


vandy said:
Hi Klatuu,

PID is a number and I have debugged the code and the PID gets displayed.
if rs.nomatch only than should i get the msg displayed. I am getting this
popup msg every time i click on the combo box.

Any help would be greatly appreciated.


Klatuu said:
The only obvious point of question is the data type of [PID]
If it is numeric, this is not the problem, if it is text, then you need the
proper delimiters:
rs.FindFirst "[PID] = '" & Me.cmbpno & "'"

--
Dave Hargis, Microsoft Access MVP


vandy said:
Hi all,

I have to check if the project no is not in the combo list and if it is not
than the system should pop a msg telling project no not found do you want to
add and than add the data in the list.

if it is a small combo box than one can see the values but if it is an
extended list it is good to have a confirmation msg.

I used 2 events - after update to check for values and not in list to update
the combo. Here is my problem the msg box is not firing as intended and can i
program in the not inlist event to check for both.

I used allenbrow's code as follows

Private Sub cmbpno_AfterUpdate()

Dim rs As DAO.Recordset

If Not IsNull(Me.cmbpno) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[PID] = " & Me.cmbpno

If rs.NoMatch Then
MsgBox "Project DOES NOT EXIST?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

End Sub
(here i get this msg even if there is a match found)

Private Sub cmbpno_NotInList(NewData As String, Response As Integer)

Dim rstproject As ADODB.Recordset
Dim intAnswer As Integer

intAnswer = MsgBox("Do you want to Add " & NewData & " to the list of
projects?", _
vbQuestion + vbYesNo)

If intAnswer = vbYes Then
DoCmd.OpenForm "Add projects", acNormal, "", "", acFormAdd, acDialog
Me.cmbpno.SetFocus
Me.cmbpno.Undo
Response = acDataErrContinue


End If

End Sub

thanks for your help in advance
Click to expand...
Click to expand...
Click to expand...
 
V

vandy

I just rechecked my code and this is what I got
I have unbound combo box.
row source is a SELECT tblprojects.ProjectID, tblprojects.Pno,
tblprojects.Pname FROM tblprojects;

I have 3 columns ProjectID,pno and pname
ProjectID -PK autonumber feild.

Column 1 is the bound column

I had the wrong key feild value PID now when i give it the correct ProjectID
it gives me this error.
3070 microsoft jet database engine error. does not recognize ProjectID.

I must be doing something wrong somewhere!!

Klatuu said:
How many columns does your combo have?
Which column is the bound column?
Will the bound column be the one you expect to contain the vaule?
--
Dave Hargis, Microsoft Access MVP


vandy said:
Hi Klatuu,

PID is a number and I have debugged the code and the PID gets displayed.
if rs.nomatch only than should i get the msg displayed. I am getting this
popup msg every time i click on the combo box.

Any help would be greatly appreciated.


Klatuu said:
The only obvious point of question is the data type of [PID]
If it is numeric, this is not the problem, if it is text, then you need the
proper delimiters:
rs.FindFirst "[PID] = '" & Me.cmbpno & "'"

--
Dave Hargis, Microsoft Access MVP


:

Hi all,

I have to check if the project no is not in the combo list and if it is not
than the system should pop a msg telling project no not found do you want to
add and than add the data in the list.

if it is a small combo box than one can see the values but if it is an
extended list it is good to have a confirmation msg.

I used 2 events - after update to check for values and not in list to update
the combo. Here is my problem the msg box is not firing as intended and can i
program in the not inlist event to check for both.

I used allenbrow's code as follows

Private Sub cmbpno_AfterUpdate()

Dim rs As DAO.Recordset

If Not IsNull(Me.cmbpno) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[PID] = " & Me.cmbpno

If rs.NoMatch Then
MsgBox "Project DOES NOT EXIST?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

End Sub
(here i get this msg even if there is a match found)

Private Sub cmbpno_NotInList(NewData As String, Response As Integer)

Dim rstproject As ADODB.Recordset
Dim intAnswer As Integer

intAnswer = MsgBox("Do you want to Add " & NewData & " to the list of
projects?", _
vbQuestion + vbYesNo)

If intAnswer = vbYes Then
DoCmd.OpenForm "Add projects", acNormal, "", "", acFormAdd, acDialog
Me.cmbpno.SetFocus
Me.cmbpno.Undo
Response = acDataErrContinue


End If

End Sub

thanks for your help in advance
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

Post the code you are using now, please
--
Dave Hargis, Microsoft Access MVP


vandy said:
I just rechecked my code and this is what I got
I have unbound combo box.
row source is a SELECT tblprojects.ProjectID, tblprojects.Pno,
tblprojects.Pname FROM tblprojects;

I have 3 columns ProjectID,pno and pname
ProjectID -PK autonumber feild.

Column 1 is the bound column

I had the wrong key feild value PID now when i give it the correct ProjectID
it gives me this error.
3070 microsoft jet database engine error. does not recognize ProjectID.

I must be doing something wrong somewhere!!

Klatuu said:
How many columns does your combo have?
Which column is the bound column?
Will the bound column be the one you expect to contain the vaule?
--
Dave Hargis, Microsoft Access MVP


vandy said:
Hi Klatuu,

PID is a number and I have debugged the code and the PID gets displayed.
if rs.nomatch only than should i get the msg displayed. I am getting this
popup msg every time i click on the combo box.

Any help would be greatly appreciated.


:

The only obvious point of question is the data type of [PID]
If it is numeric, this is not the problem, if it is text, then you need the
proper delimiters:
rs.FindFirst "[PID] = '" & Me.cmbpno & "'"

--
Dave Hargis, Microsoft Access MVP


:

Hi all,

I have to check if the project no is not in the combo list and if it is not
than the system should pop a msg telling project no not found do you want to
add and than add the data in the list.

if it is a small combo box than one can see the values but if it is an
extended list it is good to have a confirmation msg.

I used 2 events - after update to check for values and not in list to update
the combo. Here is my problem the msg box is not firing as intended and can i
program in the not inlist event to check for both.

I used allenbrow's code as follows

Private Sub cmbpno_AfterUpdate()

Dim rs As DAO.Recordset

If Not IsNull(Me.cmbpno) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[PID] = " & Me.cmbpno

If rs.NoMatch Then
MsgBox "Project DOES NOT EXIST?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

End Sub
(here i get this msg even if there is a match found)

Private Sub cmbpno_NotInList(NewData As String, Response As Integer)

Dim rstproject As ADODB.Recordset
Dim intAnswer As Integer

intAnswer = MsgBox("Do you want to Add " & NewData & " to the list of
projects?", _
vbQuestion + vbYesNo)

If intAnswer = vbYes Then
DoCmd.OpenForm "Add projects", acNormal, "", "", acFormAdd, acDialog
Me.cmbpno.SetFocus
Me.cmbpno.Undo
Response = acDataErrContinue


End If

End Sub

thanks for your help in advance
Click to expand...
Click to expand...
Click to expand...
 
V

vandy

This is the current code i am using.

Sub cmbpno_AfterUpdate()

Dim rs As DAO.Recordset

If Not IsNull(Me.cmbpno) Then
'Save before move.
If Me.Dirty Then
' Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[ProjectID] = " & Me.cmbpno

If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If


Klatuu said:
Post the code you are using now, please
--
Dave Hargis, Microsoft Access MVP


vandy said:
I just rechecked my code and this is what I got
I have unbound combo box.
row source is a SELECT tblprojects.ProjectID, tblprojects.Pno,
tblprojects.Pname FROM tblprojects;

I have 3 columns ProjectID,pno and pname
ProjectID -PK autonumber feild.

Column 1 is the bound column

I had the wrong key feild value PID now when i give it the correct ProjectID
it gives me this error.
3070 microsoft jet database engine error. does not recognize ProjectID.

I must be doing something wrong somewhere!!

Klatuu said:
How many columns does your combo have?
Which column is the bound column?
Will the bound column be the one you expect to contain the vaule?
--
Dave Hargis, Microsoft Access MVP


:

Hi Klatuu,

PID is a number and I have debugged the code and the PID gets displayed.
if rs.nomatch only than should i get the msg displayed. I am getting this
popup msg every time i click on the combo box.

Any help would be greatly appreciated.


:

The only obvious point of question is the data type of [PID]
If it is numeric, this is not the problem, if it is text, then you need the
proper delimiters:
rs.FindFirst "[PID] = '" & Me.cmbpno & "'"

--
Dave Hargis, Microsoft Access MVP


:

Hi all,

I have to check if the project no is not in the combo list and if it is not
than the system should pop a msg telling project no not found do you want to
add and than add the data in the list.

if it is a small combo box than one can see the values but if it is an
extended list it is good to have a confirmation msg.

I used 2 events - after update to check for values and not in list to update
the combo. Here is my problem the msg box is not firing as intended and can i
program in the not inlist event to check for both.

I used allenbrow's code as follows

Private Sub cmbpno_AfterUpdate()

Dim rs As DAO.Recordset

If Not IsNull(Me.cmbpno) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[PID] = " & Me.cmbpno

If rs.NoMatch Then
MsgBox "Project DOES NOT EXIST?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

End Sub
(here i get this msg even if there is a match found)

Private Sub cmbpno_NotInList(NewData As String, Response As Integer)

Dim rstproject As ADODB.Recordset
Dim intAnswer As Integer

intAnswer = MsgBox("Do you want to Add " & NewData & " to the list of
projects?", _
vbQuestion + vbYesNo)

If intAnswer = vbYes Then
DoCmd.OpenForm "Add projects", acNormal, "", "", acFormAdd, acDialog
Me.cmbpno.SetFocus
Me.cmbpno.Undo
Response = acDataErrContinue


End If

End Sub

thanks for your help in advance
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

Now I am getting confused.
Before, you were trying to match on PID, now it is ProjectID. What is the
difference? As to your error, is ProjectID a field in the form's recordset?
What you want to do is use either the primary key field or another unique
indexed field to match on, and a value in the combo box that you expect to
match on. the column in the combo you want to match to should be the bound
column. And, you have to use the correct delimiters in the FindFirst for the
data type of the field in the recordset you are trying to find.
--
Dave Hargis, Microsoft Access MVP


vandy said:
This is the current code i am using.

Sub cmbpno_AfterUpdate()

Dim rs As DAO.Recordset

If Not IsNull(Me.cmbpno) Then
'Save before move.
If Me.Dirty Then
' Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[ProjectID] = " & Me.cmbpno

If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If


Klatuu said:
Post the code you are using now, please
--
Dave Hargis, Microsoft Access MVP


vandy said:
I just rechecked my code and this is what I got
I have unbound combo box.
row source is a SELECT tblprojects.ProjectID, tblprojects.Pno,
tblprojects.Pname FROM tblprojects;

I have 3 columns ProjectID,pno and pname
ProjectID -PK autonumber feild.

Column 1 is the bound column

I had the wrong key feild value PID now when i give it the correct ProjectID
it gives me this error.
3070 microsoft jet database engine error. does not recognize ProjectID.

I must be doing something wrong somewhere!!

:

How many columns does your combo have?
Which column is the bound column?
Will the bound column be the one you expect to contain the vaule?
--
Dave Hargis, Microsoft Access MVP


:

Hi Klatuu,

PID is a number and I have debugged the code and the PID gets displayed.
if rs.nomatch only than should i get the msg displayed. I am getting this
popup msg every time i click on the combo box.

Any help would be greatly appreciated.


:

The only obvious point of question is the data type of [PID]
If it is numeric, this is not the problem, if it is text, then you need the
proper delimiters:
rs.FindFirst "[PID] = '" & Me.cmbpno & "'"

--
Dave Hargis, Microsoft Access MVP


:

Hi all,

I have to check if the project no is not in the combo list and if it is not
than the system should pop a msg telling project no not found do you want to
add and than add the data in the list.

if it is a small combo box than one can see the values but if it is an
extended list it is good to have a confirmation msg.

I used 2 events - after update to check for values and not in list to update
the combo. Here is my problem the msg box is not firing as intended and can i
program in the not inlist event to check for both.

I used allenbrow's code as follows

Private Sub cmbpno_AfterUpdate()

Dim rs As DAO.Recordset

If Not IsNull(Me.cmbpno) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[PID] = " & Me.cmbpno

If rs.NoMatch Then
MsgBox "Project DOES NOT EXIST?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

End Sub
(here i get this msg even if there is a match found)

Private Sub cmbpno_NotInList(NewData As String, Response As Integer)

Dim rstproject As ADODB.Recordset
Dim intAnswer As Integer

intAnswer = MsgBox("Do you want to Add " & NewData & " to the list of
projects?", _
vbQuestion + vbYesNo)

If intAnswer = vbYes Then
DoCmd.OpenForm "Add projects", acNormal, "", "", acFormAdd, acDialog
Me.cmbpno.SetFocus
Me.cmbpno.Undo
Response = acDataErrContinue


End If

End Sub

thanks for your help in advance
Click to expand...
Click to expand...
Click to expand...
 
V

vandy

Sorry about the confusion. I am trying various options but still get the 3070
error.

I have a table

ProjectID Pno pname
1 1771 xyz
2 1586 wiring
3 1770 retube

ProjectID - numeric -PK
Pno - numeric
pname - text

my combo has all the 3 column selected in rowsource.
SELECT tblprojects.ProjectID, tblprojects.Pno, tblprojects.Pname FROM
tblprojects ORDER BY tblprojects.ProjectID;
I have hidden the ProjectID so the combo shows Pno and pname

I want the user to search for pno and if not found than say it is not found.


what should be my bound column and what should i check in the Findfirst.

My combo box control source is blank.

my combo box is on a form bound to a different table.

(PID was the foreign key linking the transaction table with the tblprojects)

thanks for your patience.


Klatuu said:
Now I am getting confused.
Before, you were trying to match on PID, now it is ProjectID. What is the
difference? As to your error, is ProjectID a field in the form's recordset?
What you want to do is use either the primary key field or another unique
indexed field to match on, and a value in the combo box that you expect to
match on. the column in the combo you want to match to should be the bound
column. And, you have to use the correct delimiters in the FindFirst for the
data type of the field in the recordset you are trying to find.
--
Dave Hargis, Microsoft Access MVP


vandy said:
This is the current code i am using.

Sub cmbpno_AfterUpdate()

Dim rs As DAO.Recordset

If Not IsNull(Me.cmbpno) Then
'Save before move.
If Me.Dirty Then
' Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[ProjectID] = " & Me.cmbpno

If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If


Klatuu said:
Post the code you are using now, please
--
Dave Hargis, Microsoft Access MVP


:

I just rechecked my code and this is what I got
I have unbound combo box.
row source is a SELECT tblprojects.ProjectID, tblprojects.Pno,
tblprojects.Pname FROM tblprojects;

I have 3 columns ProjectID,pno and pname
ProjectID -PK autonumber feild.

Column 1 is the bound column

I had the wrong key feild value PID now when i give it the correct ProjectID
it gives me this error.
3070 microsoft jet database engine error. does not recognize ProjectID.

I must be doing something wrong somewhere!!

:

How many columns does your combo have?
Which column is the bound column?
Will the bound column be the one you expect to contain the vaule?
--
Dave Hargis, Microsoft Access MVP


:

Hi Klatuu,

PID is a number and I have debugged the code and the PID gets displayed.
if rs.nomatch only than should i get the msg displayed. I am getting this
popup msg every time i click on the combo box.

Any help would be greatly appreciated.


:

The only obvious point of question is the data type of [PID]
If it is numeric, this is not the problem, if it is text, then you need the
proper delimiters:
rs.FindFirst "[PID] = '" & Me.cmbpno & "'"

--
Dave Hargis, Microsoft Access MVP


:

Hi all,

I have to check if the project no is not in the combo list and if it is not
than the system should pop a msg telling project no not found do you want to
add and than add the data in the list.

if it is a small combo box than one can see the values but if it is an
extended list it is good to have a confirmation msg.

I used 2 events - after update to check for values and not in list to update
the combo. Here is my problem the msg box is not firing as intended and can i
program in the not inlist event to check for both.

I used allenbrow's code as follows

Private Sub cmbpno_AfterUpdate()

Dim rs As DAO.Recordset

If Not IsNull(Me.cmbpno) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[PID] = " & Me.cmbpno

If rs.NoMatch Then
MsgBox "Project DOES NOT EXIST?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

End Sub
(here i get this msg even if there is a match found)

Private Sub cmbpno_NotInList(NewData As String, Response As Integer)

Dim rstproject As ADODB.Recordset
Dim intAnswer As Integer

intAnswer = MsgBox("Do you want to Add " & NewData & " to the list of
projects?", _
vbQuestion + vbYesNo)

If intAnswer = vbYes Then
DoCmd.OpenForm "Add projects", acNormal, "", "", acFormAdd, acDialog
Me.cmbpno.SetFocus
Me.cmbpno.Undo
Response = acDataErrContinue


End If

End Sub

thanks for your help in advance
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Clearing combo boxes and a text box 2
Rename Message Box 3
Need help with this NotInList code for a combo box 4
Drop down list control value in vba 2
Limit to List question 11
combobox, NotInList, update form 5
item not found in this collection 1
Word macro that can call cells out of Excel for a find and replace 0

Top