Results 1 to 8 of 8
  1. #1
    toddbuckles is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2010
    Location
    Florida, USA
    Posts
    8

    Setting value of a formfield

    Hi,

    I am using VBA to open a from from another form. The Code to open the form is as follows:

    Code:
    Private Sub txtDayBlock04_DblClick(Cancel As Integer) 
        tempDate = CDate(Me![txtDayBlock04].Tag) 
        DoCmd.OpenForm "frmPatients", acNormal, , "[Date] = #" & CDate(Me![txtDayBlock04].Tag) & "#" 
    End Sub

    tempDate is a Public Var Dim'd in a Module

    The Form_Load Event reads:


    Code:
    Private Sub Form_Load() 
        [Date] = tempDate 
    End Sub

    Problem is when the form loads, only the first instance of [Date] is set to the tempDate variable. If no record exists, then the new instance of [Date] is set to tempDate. The desired effect is any time the form loads, only a new instance (therefore a new record in the db) is set to tempDate.

    Any ideas?



    TIA VM
    TB

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    What exactly are you trying to accomplish? The WhereCondition argument puts a Filter on the RecordSource of the next form. Is that what you want? Why are you using the LoadEvent to set the [Date] field in the RecordSource? BTW, Date is a reserved word and can easily give you strange results: http://www.allenbrowne.com/AppIssueBadWord.html#D

  3. #3
    toddbuckles is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2010
    Location
    Florida, USA
    Posts
    8
    Thanks for the quick response Rural_Guy,

    As you are aware, when I load a form based on a table, and add the where clause, it will only display the results in the form where the condition is met. In this case, I am clicking on a day in a calendar and returning, on the form the appointments for that day. Also included is a blank row to add another record.

    What I am trying to do is have the date that I clicked on filled in automatically in the Date field of the blank row when the form opens. That way the user does not accidentally put in an incorrect date.

    If there are no records for that date, the routine works fine when the form opens. If there is one or more records, it only inserts the date in Date field of the first record. I will change the Date name of the field soon as you are 100% correct, that was a poor choice.

    Thanks in advance for any help you can provide.

    TB

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You want that field to have this date as the "Default" value so that you are not dirtying a record with your code. Use the OnLoad event to set the Default value for that control instead.

  5. #5
    toddbuckles is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2010
    Location
    Florida, USA
    Posts
    8
    RuralGuy,

    Your a Genius. I will post this answer on the other two forums I have asked on, and list you as the source, so thank you.

    Incidentally, I have passed the variable to the form, and when I check with a breakpoint it comes across as a string ("12/1/2010"). Even hovering over Me.frmPatients.Date part of the line of code, it still shows "12/1/2010" (as a string). However, when the code completes and the form opens, it shows as 12:08:36 AM in the text box. I have looked at the input mask for the text box and it shows 99/99/0000;;_, the syntax for short date. I know this must be something simple, but I have tried everything I know of.

    Again, thank you for the very simple answer to my overly complex problem earlier. It has been stumping me for three days now.

    TB

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You've got me on that one.

  7. #7
    toddbuckles is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2010
    Location
    Florida, USA
    Posts
    8
    To further close the case, and if anyone else stumbles upon the thread and needs an answer, it was syntactic. You must pass the = sign to default value when using dates apparently.


    Code:
    Private Sub Form_Load()
        Me.Date.DefaultValue = "='" & CDate(tempDate) & "'"
    End Sub
    Thanks

    TB

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Thanks for posting back with your solution!

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

Similar Threads

  1. Re setting form after each search
    By VinceHay in forum Forms
    Replies: 0
    Last Post: 12-08-2010, 03:35 AM
  2. Setting Recordsource for Subforms
    By P5C768 in forum Forms
    Replies: 5
    Last Post: 11-16-2010, 05:01 AM
  3. Setting Value for Each Row in Subform
    By swimmermx in forum Forms
    Replies: 11
    Last Post: 07-21-2010, 05:25 PM
  4. Setting up new database
    By mduplantis in forum Database Design
    Replies: 1
    Last Post: 06-23-2010, 11:07 AM
  5. Setting combo box properties
    By rathfam4 in forum Programming
    Replies: 1
    Last Post: 12-28-2005, 02:27 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