Results 1 to 3 of 3

Thread: Help on VBA!

  1. #1
    Join Date
    Feb 2004
    Posts
    33

    Red face Unanswered: Help on VBA!

    Expert help!!

    Support I am going to fill in a Form in Access97. There is a number(PhoneNumber) that I would like to check if the record of this number exists in my

    link table? If so, then go ahead to append the record of this number to A table, if not, then just pop-up a message and append to B Table. The following is my code:

    Private Sub PhoneNumber_AfterUpdate()

    Dim rsp
    Dim Cur_DB As DAO.Database, PhoneNumber As DAO.Recordset
    Set Cur_DB = CurrentDb
    Set PhoneNumber = Cur_DB.OpenRecordset("Select * from LinkTable where = ;")
    If WTN.RecordCount <> 0 Then DoCmd.GoToRecord acDataForm, "A Form", acNewRec, 1
    Else: rsp = MsgBox("The Phone Number is not in Table. Do You Still Want to Continue?", vbYesNo)
    If rsp = vbYes Then
    DoCmd.GoToRecord acDataForm, "B Form", acNewRec, 1

    End If
    End If


    End Sub



    I am total newbie in VBA and Access. I know there are somewhere wrong. Please help! Thanks a lot in advanced!

  2. #2
    Join Date
    Feb 2004
    Posts
    106
    Try this code:

    Dim rsp
    Dim strPhoneNumber as String
    Dim Cur_DB As DAO.Database, PhoneNumber As DAO.Recordset
    Set Cur_DB = CurrentDb
    strPhoneNumber = txt_PhoneNumber ' (PhoneNumber on your form)
    Set PhoneNumber = Cur_DB.OpenRecordset("Select * from LinkTable where PhoneNumber = '" & strPhoneNumber &"'")
    If PhoneNumber.RecordCount <> 0 Then DoCmd.GoToRecord acDataForm, "A Form", acNewRec, 1
    Else: rsp = MsgBox("The Phone Number is not in Table. Do You Still Want to Continue?", vbYesNo)
    If rsp = vbYes Then
    DoCmd.GoToRecord acDataForm, "B Form", acNewRec, 1

    End If
    End If


    End Sub

  3. #3
    Join Date
    Feb 2004
    Posts
    33
    Thank you so much for your reply. I am trying your code now but I don't know what to do about the
    strPhoneNumber = txt_PhoneNumber ' (PhoneNumber on your form)

    The phone number is changed everytime when I fill in the form.

    Thank you very much again.


    Originally posted by Yildirim
    Try this code:

    Dim rsp
    Dim strPhoneNumber as String
    Dim Cur_DB As DAO.Database, PhoneNumber As DAO.Recordset
    Set Cur_DB = CurrentDb
    strPhoneNumber = txt_PhoneNumber ' (PhoneNumber on your form)
    Set PhoneNumber = Cur_DB.OpenRecordset("Select * from LinkTable where PhoneNumber = '" & strPhoneNumber &"'")
    If PhoneNumber.RecordCount <> 0 Then DoCmd.GoToRecord acDataForm, "A Form", acNewRec, 1
    Else: rsp = MsgBox("The Phone Number is not in Table. Do You Still Want to Continue?", vbYesNo)
    If rsp = vbYes Then
    DoCmd.GoToRecord acDataForm, "B Form", acNewRec, 1

    End If
    End If


    End Sub

Posting Permissions

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