well, the ProjectID field is probably a Number data type (you'd have to
check the table that field is in, to be sure). in that case, the syntax of
your criteria is incorrect, because it is forcing the value from the combo
box to be text. try
strCriteria = "[ProjectID] = " & Me.cmbProjects.Column(0)
btw, when you *do* need a text value, you don't need to add the leading
quote mark separately. instead, you can use
strCriteria = "[ProjectID] = '" & Me.cmbProjects.Column(0) & "'"
also, i understand why you're opening a recordset, but i'm not getting why
you're looping through it; at that point in the code, you've already
determined that there is a match to the ProjectID from the combo box. the
following might be easier, as
Private Sub findprojectid_Click()
Dim strCriteria As String
strCriteria = "[ProjectID] = " & Me!cmbProjects.Column(0)
If DCount(1, "Projects", strCriteria) < 1 Then
MsgBox "Project # " & strCriteria & " does not exist."
Else
DoCmd.OpenForm "AddProject", acNormal, , strCriteria
End If
End Sub
note: in my previous post, i asked what the BoundColumn of your combo box
is. that has nothing to do with the control being bound or unbound (which is
determined by the ControlSource property). every combo box control has a
BoundColumn property. the default value is 1, which is equivalent to
Column(0). i know, it's confusing. suggest you read up on the BoundColumn
property by selecting the combo box control in form design view, placing
your cursor in that field in the Properties box, and pressing F1. takes you
straight to the appropriate topic in Access Help. at any rate, if the
BoundColumn property of cmbProjects is 1, then you don't need to refer to
Column(0) in your code. just refer to the control, as
Me!cmbProjects
hth
danka said:
Hello Tina:
Yes the combo box has 2 columns (ProjectId and ProjectName) and is not bound.
When I use the code to search on project name (column1) it works ok. When I
use the same code to search by project id (column0) I get" Data Type mismatch
in criteria expression"
Private Sub findprojectid_Click()
On Error GoTo Err_Command50_Click
Dim dbDatabase As Database
Dim rstRecordset As Recordset
Dim strCriteria As String
Set dbDatabase = CurrentDb()
Set rstRecordset = dbDatabase.OpenRecordset("Projects", dbOpenDynaset)
strCriteria = "[ProjectID] = " & "'" & Me.cmbProjects.Column(0) & "'"
rstRecordset.FindFirst strCriteria
If rstRecordset.NoMatch Then
MsgBox "Project # " & Me.cmbProjects.Column(0) & " does not exist."
Exit Sub
Else
Do While Not rstRecordset.NoMatch
rstRecordset.FindNext strCriteria
Loop
DoCmd.Close
DoCmd.OpenForm "AddProject", acNormal, , strCriteria
End If
Exit_Find_Click:
rstRecordset.Close
Set dbDatabase = Nothing
Exit Sub
Err_Find_Click:
MsgBox Err.Description
Resume Exit_Find_Click
Exit_Command50_Click:
Exit Sub
Err_Command50_Click:
MsgBox Err.Description
Resume Exit_Command50_Click
End Sub
tina said:
the problem is probably in your code, not in the fact of the data type in
the combo box itself. please post the code you're using to open the form.
also, is your combo box more than one column? if so, which column is bound,
and what data does it display?
Hello:
I hope you can help me with this.
I have two forms: Form 1 (to select project ID from the combo box) and
Form
2 (AddProject). I want users to select Project Id from the combo box and
then open Add Project form displaying selected project id.
My Project ID is a number field. I tried different methods and I am
getting
data mismatch error message (I guess, my project ID in numeric and combo
is
text type).
Can you assist? Thanks.
