Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    San Jose, CA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default table or form data (2002 SP2)

    I'm in the process of creating my first database. It is for a day club at my church and I have 250+ paper application forms to enter into the database. What I want to do is set several fields as the default value from a field value entered earlier in the form. For example, after entering the student's last name I would like to have the last name propagated to the last name fields of "parents name", "emergency contact", and the "who can pick up the child", fields. I've tried to use the field name from the database [LastName] in both the table default property and the form property that i'm using for input but Access does not like this. I can't seem to find this info in help or in the books i have. Can someone tell me how to do this?

    Thanks!

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Default table or form data (2002 SP2)

    If I understand you, what you need to do is to put the following code in the AfterUpdate event of the text control of the student's last name:
    [Parents name] = [students last name]
    [emergency contact] = [students last name]
    [who can pick up the child] = [students last name]

    You will have to substitute your forms control names for the ones above.
    eg. If the control name of the student's last name on the form was txtStudentsLastName substitute it for [students last name] above

  3. #3
    New Lounger
    Join Date
    Jan 2001
    Location
    San Jose, CA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default table or form data (2002 SP2)

    Thanks Pat, that points me in the right direction. Since I'm somewhat of a newbie to Access, and not very familiar with expression syntax yet, could you give me a few more specifics? Using the expression builder I created the following expression for the text box "LastName" properties for the form. On the "Event" tab in the "After Update" field is the following expression.

    [Students]![ParentsLastName] = [Students]![LastName]

    which Access then prepended an "=" sign in front of when I exited the builder to get

    =[Students]![ParentsLastName] = [Students]![LastName]

    I'm doing this on the text box properties for the text box "LastName" in the form. Is this correct? I'm get an error. (I've tried to attach the error but have not done this before. Hope it comes through.)

    Thanks!
    Don
    Attached Images Attached Images

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Default table or form data (2002 SP2)

    What you have to do is to click on the AfterUpdate event for the Last Name and select Event Procedure, then just to the right of where you selected event Procedure there are 3 dots, click on these 3 dots. This opens up the VB Editor window and shows:

    Private Sub TextLast12Mths_AfterUpdate()

    End Sub

    After the 1st line (xxx) put that code I posted and change the control names to your control names.

    Choose debug and compile to see if your code compiles ok, and if all ok, then exit from the VB Editor window.

    If you are still stuck after this, please post back.

  5. #5
    New Lounger
    Join Date
    Jan 2001
    Location
    San Jose, CA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default table or form data (2002 SP2)

    That worked! Thank you soooo much!

    Don

  6. #6
    New Lounger
    Join Date
    Nov 2002
    Location
    Miami, Florida, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default table or form data (2002 SP2)

    I want to do the same thing - but my default values are in a separate table and in a query.
    The error message I'm getting is "Object Required"
    Here is the code example where I link to the Query. My link to the "Tables" object doesn't work any better.

    Private Sub WORK_ORDER_NUMBER_AfterUpdate()
    [SELL_VALUE] = Queries![Work Order Default and Summary]![Sell Amount]
    End Sub

    Am I going about this the right way?
    Is there a way to do it with the Table Default field?

    I want the Defaults to be populated from the other table/query, but I still want them to be able to populate the field with any value.

    Thanks
    Chris

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Default table or form data (2002 SP2)

    What do you mean by "but I still want them to be able to populate the field with any value" - is "them" the user(s)?

  8. #8
    New Lounger
    Join Date
    Nov 2002
    Location
    Miami, Florida, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default table or form data (2002 SP2)

    Yes - "them" is the users, sorry
    Maybe I should clarify more...
    One table has employee id and certain defaults
    One table (which is the source of the form I'm asking about) has all of the information about each issue they are logging - I want Access to fill in some of fields in the issue table/form for them based on their user id.
    I do not want the choices made while logging the issue to effect the Employee form

    Thanks
    Chris

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Default table or form data (2002 SP2)

    OK, you can't refer to tables or queries the way you tried. You can use DLookup for this. The general syntax for DLookup is DLookup("fieldname", "tablename", "where-condition") where fieldname is a field name or an expression, tablename is the name of a table or query (but not an SQL statement) and where-condition is a condition like the WHERE clause of an SQL statement without WHERE.

    Your code could look like this (substitute the appropriate names):

    Private Sub WORK_ORDER_NUMBER_AfterUpdate()
    [SELL_VALUE] = DLookup("[Sell Amount]", "[Work Order Default and Summary]", "[Work Order Number]=" & Me.[Work Order Number])
    End Sub

    I have assumed:
    <UL><LI>The control and field are named "Work Order Number".
    <LI>"Work Order Number" is numeric; if it is a string, the end of the instruction becomes
    ..., "[Work Order Number]=" & Chr(34) & Me.[Work Order Number] & Chr(34))
    Chr(34) is a double quote, used to enclose the string value in quotes.[/list]If this doesn't work, check the names carefully. If you don't succeed, post back with more details.

  10. #10
    New Lounger
    Join Date
    Nov 2002
    Location
    Miami, Florida, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default table or form data (2002 SP2)

    Thank you Hans!!
    That worked great.

    Chris

Posting Permissions

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