Re: Find record based on combo

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" <borkowd@xxxxxxxxxxxxxxxxxxxxx(donotspam)> wrote in message
news:9AD5654D-B191-4E99-95CC-BF18AB22DBF3@xxxxxxxxxxxxxxxx
> 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" wrote:
>
> > 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?
> >
> >
> > "danka" <borkowd@xxxxxxxxxxxxxxxxxxxxx(donotspam)> wrote in message
> > news:D536AEE7-DF1F-4C40-B953-2AAD8F6681CF@xxxxxxxxxxxxxxxx
> > > 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.
> > >
> > >
> > >
> > > --
> > > danka
> >
> >
> >


.

Relevant Pages

  • Re: Find record based on combo
    ... Thanks for fixing my code - ProjectID is a number data type and I used the ... > End Sub ... that has nothing to do with the control being bound or unbound (which is ...
    (microsoft.public.access.formscoding)
  • Re: Simple Insert Into...
    ... You can store unbound control data in a variable, ... in the sql. ... Dim strSQL As String ... In your form's Sub, you'll wind up with something like the following: ...
    (microsoft.public.access.modulesdaovba)
  • Re: Simple Insert Into...
    ... it is in a control on the form; and is unbound (it's actually a combo ... box that is used as a dropdown list, values taken from a different table; SQL ... I need this value to assign a unique ID to the entire recordset being ... In your form's Sub, you'll wind up with something like the following: ...
    (microsoft.public.access.modulesdaovba)
  • Re: stephen lebans tool tip
    ... ListBox control. ... > Private Sub cboFindACustomer_MouseMove(Button As Integer, ... > ' Set the text for the txtCustomerID label. ...
    (microsoft.public.access.forms)
  • Re: Change textbox/combobox colors with a procedure
    ... as I have more that a few variables depending on the measurement type. ... The Form's Current event procedure title is: Private Sub Form_Current ... Private Sub FormatControl(CTRL As Control) ...
    (microsoft.public.access.formscoding)