Results 1 to 11 of 11
  1. #1
    Ramun_Flame is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    19

    Variable taking input from pop-up, Multiple Where contitions, and other problem.

    1. I am making a form for taking in appointments, and when you press the "Cancel Appointment" button it asks if you are sure, then add the information from the appointment to another table, and deletes it from the current table. I want it to pop up another box, to ask for an initial, so I can add information about who canceled it to the cancellations table.



    So, basically, I need code for taking user input through a pop-up, then storing it in a variable.


    2. I currently use this code to filter a form when it opens:

    Private Sub Command643_Click()
    DoCmd.OpenForm "Blank Tutor", , , "First_Name = 'Bob'"
    End Sub

    I want to put multiple filters, but I haven't been able to figure it out. I also want to filter by Last_Name. How would I do that?

    3. This one is a bit more tricky. I have a button on a form, the shows the cancellations for that tutor, but it shows all the cancellations on the form. I want that button to apply a filter the the form, similar to #2. I just want the filter to be dynamic, in that it will show based on the field currently selected.

    I tried this:

    Private Sub Command534_Click()
    DoCmd.OpenForm "Cancellations", , , "First_Name = First_Name"
    End Sub

    This just shows all of the cancellation table. Basically, I am in a filtered form, and it is only displaying appointments for one tutor. I have a button for displaying the cancellations table(code above) but I want it to only show cancellations from the tutor that is currently filtered. Maybe something to reference the specific value of the currently selected record?

    Thank you so much in advance guys.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    1. Why move appointment between tables? One table can serve. Have another field for 'Disposition' (Canceled, Fulfilled, whatever). Can use intrinsic InputBox function for user input of initials. Is this a split db with backend on a central server? Another method is to get the user network login ID and save that to the table or use their network ID to lookup initials from a Users table. Enforces consistency of initials and the action can be invisible to users.

    2. Why filter by name parts (what if you have two John Smith) - use a unique ID as criteria.

    3. Concatenate variables. Reference to form data is a variable.

    If the form is already open, use code to apply criteria to form Filter property:
    Me.FilterOn = False
    Me.Filter = "First_Name = '" & Me!First_Name & "'"
    Me.FilterOn = True

    To open filtered form:
    DoCmd.OpenForm "Cancellations", , , "First_Name = '" & Me!First_Name & "'"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Ramun_Flame is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    19
    1. I don't have any users implemented, since this will just be on one computer, and replace existing paper logs. The InputBox works, I don't need the cancel button, but it serves it's purpose, so I'm okay with it. I'm trying to keep it as open as i can user wise, so I can't make people log in, to keep track of initials automatically.

    2. Basically, after an appointment is cancelled, it is open to be scheduled again, but I need to keep track of cancellations, so I need to keep them in another table. Your right, we now only have less than 30 tutors, but to make database more future proof, I should use unique Ids.

    I would still like to know how to apply multiple filters in the DoCmd.OpenForm though, for future things I would like to implement, is this possible?

    3. This works perfectly.

    Thanks so much for the help.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can have as many criteria as needed, just keep building string, example:

    "First_Name = '" & Me!First_Name & "' AND Disposition='Cancelled'"

    "Tutor=" & Me.textboxTutorID & " AND AppointDate=#" & Me.textboxDate & "#"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Ramun_Flame is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    19
    That works great, thank you.

    Now, I'm trying to apply multiple filters, using the same format as I would use in a query, but it's not cutting it. I tried this:

    DoCmd.OpenForm "Blank Tutor", , , "First_Name = 'Bob' AND Last_Name = 'Smith'" AND Date >=DateAdd("d",-1,Date$()) And <DateAdd("d",8,Date$())

    This gives syntax errors, since it doesn't like what I did with Date. Do I have to make another query for this, or can I apply a query like filter through the open form command?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Need to repeat the field name for both date criteria. Misplaced quote mark after 'Smith'. Date criteria need # delimiters. For adding day units don't need DateAdd, day is default.

    DoCmd.OpenForm "Blank Tutor", , , "First_Name = 'Bob' AND Last_Name = 'Smith' AND [Date] >= # Date() - 1 # And [Date] < # Date() + 8 #"

    If that doesn't work, try concatenating the Date() function.
    DoCmd.OpenForm "Blank Tutor", , , "First_Name = 'Bob' AND Last_Name = 'Smith' AND [Date] >= #" & Date() - 1 & "# And [Date] < #" & Date() + 8 & "#"

    Use BETWEEN AND if you want inclusive for range ends.
    DoCmd.OpenForm "Blank Tutor", , , "First_Name = 'Bob' AND Last_Name = 'Smith' AND [Date] BETWEEN # Date() - 1 AND # Date() + 8 #"

    Date is a reserved, should not use reserved words as names.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Ramun_Flame is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    19
    I changed date to day and that worked. Since I can do this with a buton, I'm trying to make a button that will navigate through weeks. IF I press it, I want it to filter it by one week ahead, and so on.

    This is what I have:

    Dim D As Date
    If (IsNull(D)) Then
    D = Me.Day
    End If
    D = D + 7


    DoCmd.ApplyFilter , "First_Name = '" & Me!First_Name & "' AND Last_Name = '" & Me!Last_Name & "'" & " AND [Day] BETWEEN '" & D & "' AND '" & D & "'"

    It's been giving me errors with everything I've tried though. How to you use a variable as a date in this way?

    Also I have this one problem with my filter for times. I have it showing the current day, through a week correctly, but I have another field for time, and I don't want it to show a time that has passed. I can't filter out times that have passes though, since it removes future days as well. I've been trying to think of some solution for this, but have been coming up blank, any suggestions?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Date variable must be delimited with #, not apostrophe.

    The criteria BETWEEN D AND D doesn't make sense. D is the same value in both references.

    Maybe you want:

    " AND [Day] BETWEEN #" & Date() & "# AND #" & D & "#"

    I don't understand the issue about time.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Ramun_Flame is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    19
    Alright, the issue for time is I have two fields, Day, and Time. I also have a query for only finind open appointments within 7 days, including that day. So, if there is an 10:00 appointment, it will show it whether it's past 10:00 or not, since it is still the same day. Is there a way to make it only show times that have not passed?

    I tried some type if If statement to only filter if Day = Date(), and StartTime < Time(), but when it filters, it filters everything. I'm stumped on how i would do this without making date and time the same field.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Try:
    " AND CDate([Day] & ' ' & [Time]) BETWEEN #" & Now() & "# AND #" & Now() + 7 & "#"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Ramun_Flame is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    19
    From what I can tell, that's working. Thanks so much for all the help. This is my first time working on something like this, so I'm learning ton of things.

    I'll make another thread if I have any other questions, but for now everything seems to be going well.

    Thanks again.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 05-18-2012, 07:31 AM
  2. Replies: 3
    Last Post: 07-13-2011, 08:01 AM
  3. Replies: 4
    Last Post: 08-05-2010, 01:26 PM
  4. Can I have an input variable in a field formula
    By FeatherDust in forum Queries
    Replies: 3
    Last Post: 09-20-2009, 06:40 PM
  5. Replies: 1
    Last Post: 05-20-2009, 06:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums