Results 1 to 3 of 3
  1. #1
    Join Date
    May 2012
    Posts
    64

    Unanswered: VBA stopped working when I changed field to number instead of text?

    I have code that opens a different form for each record depending on what the combobox says after I double click the record. this worked great until I realized my diagnosisID, which is a foreign key, was set to text instead of a number field. When I switched it to a number, it stopped working! How would I change the code below to recognize that it is a number now instead of text?

    Code:
    Private Sub PDx_DblClick(Cancel As Integer)
    If Me.PDx = "Acute Ischemic Stroke" Then
      DoCmd.OpenForm "frm_stroke", , , "[diagnosisID]='" & Me!diagnosisID & "'"
    ElseIf Me.PDx = "Aneurysm (acute ruptured)" Then
      DoCmd.OpenForm "frm_aneurysm", , , "[diagnosisID]='" & Me!diagnosisID & "'"
    ElseIf Me.PDx = "Aneurysm (hx ruptured)" Then
      DoCmd.OpenForm "frm_aneurysmNew", , , "[diagnosisID]='" & Me!diagnosisID & "'"
    ElseIf Me.PDx = "Aneurysm (unruptured)" Then
      DoCmd.OpenForm "frm_aneurysm", , , "[diagnosisID]='" & Me!diagnosisID & "'"
    ElseIf Me.PDx = "AVM (hx hemorrhage)" Then
      DoCmd.OpenForm "frm_avm", , , "[diagnosisID]='" & Me!diagnosisID & "'"
    ElseIf Me.PDx = "AVM (no hemorrhage)" Then
      DoCmd.OpenForm "frm_avm", , , "[diagnosisID]='" & Me!diagnosisID & "'"
    ElseIf Me.PDx = "Pituitary Adenoma" Then
      DoCmd.OpenForm "frm_pituitary", , , "[diagnosisID]='" & Me!diagnosisID & "'"
    End If
    End Sub

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    "[diagnosisID]='" & Me!diagnosisID & "'"

    is the correct syntax for a Text Datatype Field. For a Number Datatype Field you have to use

    "[diagnosisID]= " & Me!diagnosisID

    BTW, a Field composed entirely of digits does not necessarily have to be defined as a Number Datatype, if it is not used for math operations. I normally define such things as phone numbers, SSNs, etc, as Text, even when nothing but digits are entered, but that's just my personal choice.

    The only time when I'd define such a Field as Number Datatype would be if I were planning to sort on the Field.
    Sorts work differently on all-digit Fields, depending on the Datatype.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    If Me.PDx = "Acute Ischemic Stroke" Then
    DoCmd.OpenForm "frm_stroke", , , "[diagnosisID]='" & Me!diagnosisID & "'"
    ElseIf Me.PDx = "Aneurysm (acute ruptured)" Then
    DoCmd.OpenForm "frm_aneurysm", , , "[diagnosisID'" & Me!diagnosisID & "'"
    ElseIf Me.PDx = "Aneurysm (hx ruptured)" Then
    DoCmd.OpenForm "frm_aneurysmNew", , , "[diagnosisID]='" & Me!diagnosisID & "'"
    ElseIf Me.PDx = "Aneurysm (unruptured)" Then
    DoCmd.OpenForm "frm_aneurysm", , , "[diagnosisID]='" & Me!diagnosisID & "'"
    ElseIf Me.PDx = "AVM (hx hemorrhage)" Then
    DoCmd.OpenForm "frm_avm", , , "[diagnosisID]='" & Me!diagnosisID & "'"
    ElseIf Me.PDx = "AVM (no hemorrhage)" Then
    DoCmd.OpenForm "frm_avm", , , "[diagnosisID]='" & Me!diagnosisID & "'"
    ElseIf Me.PDx = "Pituitary Adenoma" Then
    DoCmd.OpenForm "frm_pituitary", , , "[diagnosisID]='" & Me!diagnosisID & "'"
    End If
    I'll answer your question first. Remove all the single quotes. I highlighted the first statement where they should be deleted.

    Secondly, though, why are you using a complex If statement? You should be using the Select Case statement which is much simpler. If you're unfamiliar with it, look it up in the Help file.

    Thirdly, why are you using the entire text names (Me.PDx = "Pituitary Adenoma")? If you use the diagnosis codes instead you're less likely to make an error in the data entry.

    Sam

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •