Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50

    Unanswered: Msgbox for null report & update table

    Hey all, two problems to tackle.

    1) i have this command button to open a report based on the current date. it will show all patient appointments for today's date or any other day whena date is selected (using a date picker). question is, the report that is opening is based on a query and when appointments are scheduled for the current date it works fine. the problem is when an appointment is not scheduled for the current date the report still opens but with blank fields. i would like to implement a msgbox telling the user that 'there are no appointments for that day'. below is the code i'm currently using. i tried using an if statement but i don't know if the syntax for it was correct, because i either got an debug error or it wouldn't register and show the report with the empty fields.


    Private Sub cmdGo_Click()
    Dim strWhere

    strWhere = "[DateOfAppointment]=#" & FormatDateTime(Me.dtPickDate, vbShortDate) & "#"

    'MsgBox "No appointments have been scheduled for the selected date.", vbOKOnly, "No Appointments"

    On Error Resume Next
    DoCmd.Close
    DoCmd.OpenReport "rptCurrentAppointments", acViewPreview, , strWhere

    End Sub


    Second question is, i'm doing an inventory type 'thing'. i tried following the Northwind database but that doesn't help. i also tried creating a dbase through the wizard - one of those templates... but that didn't really help me. i used update queries but i got an error about conversion type.

    I have a table call tblMedication which has the fields units in stock and units on order. I have created a form for this table called frmMedication and has both those fields in it.

    I have a medication order form (frmMedOrders - orderNo, dateOrdered) and a medication order detail subform (subfrmMedOrderDetails - medicationName, quantity, delivered (checkbox), dateDelivered).

    I would like to put in the delivered (checkbox) some code that will add the quantity from the subform to the units in stock field in frmMedication, when it is checked.

    I also have another form called frmAppointments which contains the medication prescribed. there is no quantity field here because only one type of medication can be sold to one customer per visit. so i would like the units in stock from the frmMedication to also subtract medication every time one is sold to a patient.

    i would gladly go again with the update queries, but it's just that error i was getting. any suggestions on any of the questions would be greatly appreciated.


    bajan_elf
    "The extreme always make an impression." - Jeff Hardy

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by bajan_elf
    Hey all, two problems to tackle.

    1) i have this command button to open a report based on the current date. it will show all patient appointments for today's date or any other day whena date is selected (using a date picker). question is, the report that is opening is based on a query and when appointments are scheduled for the current date it works fine. the problem is when an appointment is not scheduled for the current date the report still opens but with blank fields. i would like to implement a msgbox telling the user that 'there are no appointments for that day'. below is the code i'm currently using. i tried using an if statement but i don't know if the syntax for it was correct, because i either got an debug error or it wouldn't register and show the report with the empty fields.


    Private Sub cmdGo_Click()
    Dim strWhere

    strWhere = "[DateOfAppointment]=#" & FormatDateTime(Me.dtPickDate, vbShortDate) & "#"

    'MsgBox "No appointments have been scheduled for the selected date.", vbOKOnly, "No Appointments"

    On Error Resume Next
    DoCmd.Close
    DoCmd.OpenReport "rptCurrentAppointments", acViewPreview, , strWhere

    End Sub


    Second question is, i'm doing an inventory type 'thing'. i tried following the Northwind database but that doesn't help. i also tried creating a dbase through the wizard - one of those templates... but that didn't really help me. i used update queries but i got an error about conversion type.

    I have a table call tblMedication which has the fields units in stock and units on order. I have created a form for this table called frmMedication and has both those fields in it.

    I have a medication order form (frmMedOrders - orderNo, dateOrdered) and a medication order detail subform (subfrmMedOrderDetails - medicationName, quantity, delivered (checkbox), dateDelivered).

    I would like to put in the delivered (checkbox) some code that will add the quantity from the subform to the units in stock field in frmMedication, when it is checked.

    I also have another form called frmAppointments which contains the medication prescribed. there is no quantity field here because only one type of medication can be sold to one customer per visit. so i would like the units in stock from the frmMedication to also subtract medication every time one is sold to a patient.

    i would gladly go again with the update queries, but it's just that error i was getting. any suggestions on any of the questions would be greatly appreciated.


    bajan_elf
    As for your 1st question: Trap the NoData event of the report and put your message box there ...

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Alternately you could use DCount to see if any records are returned and avoid launching the report alltogether.

  4. #4
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50
    The msgbox is working fyne now, so thanks for that little tidbit but the report is still opening. i tried putting in a DoCmd.Close i nthe same NoData event but i got a debug error.
    "The extreme always make an impression." - Jeff Hardy

  5. #5
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50
    OK i got that msgbox working and the report no longer pops up... so thanks alot for answering.

    now it's just the inventory problem.
    "The extreme always make an impression." - Jeff Hardy

Posting Permissions

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