Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2008

    Unanswered: How to write Visual Basic If... Else statement

    How do I write an IF, ELSE statement in Visual Basic. I have a button which when pressed I want to show previous results if previous results exist but if there is nothing to show would instead create a new record. In order to establish whether there is already a record for a person, it would need to search my 'tblSrvRspns' table for the URN chosen in a combo box. If the URN is present it would show the record from this table, i.e. response 12, but if not would create a new record.

    How would I create this statement? It would need to be IF frmMonitoringResponses.URN = any URN in 'tblSrvRspns', and would then call up the 'ID' of that record. I will then work out a way to display all the record based on that 'ID'. Then there needs to be an ELSE statement, which will be followed by this statement:

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qry_NewResponses"
    DoCmd.SetWarnings True

    Which is how I create a new record. If anyone could help me a little with how to phrase this I would be really grateful. I know how to do this in SQL but Visual Basic as per usual is my stumbling block!


  2. #2
    Join Date
    Oct 2004
    Melbourne, Australia
    Something like this should work:

    If (Nz(DLookup("URN", "tblSrvRspns", "URN = " & frmMonitoringResponses.URN), 0) = 0) Then
    DoCmd.MenuItem acFormBar, acRecordsMenu, acSaveRecord, acMenuVer70
    'Code to display existng record
    End If
    I assumed URN is numeric. If text, replace the last two 0s in the If clause with "".
    Last edited by Jim Wright; 05-11-09 at 23:01.

  3. #3
    Join Date
    Nov 2008
    A couple of slight adjustments but that works perfectly cheers.

    Now I'm trying to sort out the second half of this module. What do people think is the best way of doing this?

    I basically want to search for the survey that has the URN and SrvID specified in the combo boxes cboURN and cboSrvID respectively. The RecordSource of the Form is tblSrvRspns which is the correct form, with fields RspID, URN and SrvID. What is the best way of bringing up the record from the data in these two combo boxes?

    I was thinking some kind of FindRecord function could be used but I only know how to do this when finding one particular field and not two. Does anyone have a better idea or how it is possible to search for two fields using FindRecord?


Posting Permissions

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