Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2006
    Posts
    132
    Provided Answers: 1

    Answered: Setting/Using Login Defaults in Main Menu

    I'm trying to do the following and am having trouble with the MainMenu RecordSource.

    Form fMainMenu has Controls for showing various selections and defaults of the Login person.
    Those selections are stored in a table tLoginNamePreferences, where each person has a record of his last selections.

    When the db opens, there is a dialog form [fLoginDialog] that gets the login name and then remains open but hidden behind the MainMenu, so the name remains available.
    I want the fMainMenu Controls to be populated with preferences from that Login persons record in table tLoginNamePreferences. And when the person changes those selections his record is updated.

    I've tried unsuccessfully to use the RecordSource property of tMainMenu to tie the data to a specific record with SQL:
    SELECT tLoginNamePreferences.* FROM tLoginNamePreferences WHERE [tLoginNamePreferences]![PreferenceLoginName] = [Forms]![fLoginDialog]![cboLoginNameSelect];

    What is the best way to accomplish this. I've searched the forum for similar but didn't find any ideas.
    Thanks,
    PGT

  2. Best Answer
    Posted by Pete Townsend

    "myle ( I like that ) And the answer is - KISS - Keep It Simple, Stupid !

    I was trying to make this whole thing too complicated. I noted that if I just used the table as the RecordSource, without all the fancy SQL, it would populate the form with the data from the first record of the table. I thought I'd go through the RecordSetClone routines to select the proper record, but then remembered that Forms have a Filter property and a FilterOnLoad Boolean.

    Voila! Set the Filter to PreferenceLoginName = [forms]![fLoginDialog]![cboLoginNameSelect] and FilterOnLoad to Yes and the one record that matches is what populates the form. Simple!

    Thanks to all that took time to look at this and Stephan for his responses.
    Pete"


  3. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,482
    Provided Answers: 11
    what about something like

    in the Form_Load

    SQL = "SELECT tLoginNamePreferences.* FROM tLoginNamePreferences WHERE [tLoginNamePreferences]![PreferenceLoginName] = '" & [Forms]![fLoginDialog]![cboLoginNameSelect] &"' ";

    Not the ' Quotes they are easier than the " to use

    then me.RecordSource = SQL

    then me.Requery
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  4. #3
    Join Date
    Aug 2006
    Posts
    132
    Provided Answers: 1
    Stephan,

    Thanks for the input. Rather than using the Form_load I had the SQL actually written in the RecordSource property. (That should be the same thing as using the Form_load you suggested). I tried adding the quotes around the controlname on the right of the = but it doesn't do allow help either.

    I'm wondering if I have to abandon the RecordSource of fMainMenu and instead code every control on fMainMenu as a DLookUp
    e.g. DLookUp( "controlname" , " [tLoginNamePreferences] " , " [tLoginNamePreferences]![PreferenceLoginName] = " & [Forms]![fLoginDialog]![cboLoginNameSelect] ) But then I wonder if I'll have to also add code to save anything that changes during a session.
    PGT

  5. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,482
    Provided Answers: 11
    Quote Originally Posted by Pete Townsend View Post
    Stephan,

    Thanks for the input. Rather than using the Form_load I had the SQL actually written in the RecordSource property. (That should be the same thing as using the Form_load you suggested). I tried adding the quotes around the controlname on the right of the = but it doesn't do allow help either.

    I'm wondering if I have to abandon the RecordSource of fMainMenu and instead code every control on fMainMenu as a DLookUp
    e.g. DLookUp( "controlname" , " [tLoginNamePreferences] " , " [tLoginNamePreferences]![PreferenceLoginName] = " & [Forms]![fLoginDialog]![cboLoginNameSelect] ) But then I wonder if I'll have to also add code to save anything that changes during a session.
    yes the Dlookup will work BUT

    your Dlookup wont cause if the [Forms]![fLoginDialog]![cboLoginNameSelect] is a String

    then you have to use the ' or "

    DLookUp( "controlname" , " [tLoginNamePreferences] " , " [tLoginNamePreferences]![PreferenceLoginName] ='" & [Forms]![fLoginDialog]![cboLoginNameSelect] & "'")
    Last edited by myle; 02-08-17 at 14:15. Reason: spelling
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  6. #5
    Join Date
    Aug 2006
    Posts
    132
    Provided Answers: 1
    myle ( I like that ) And the answer is - KISS - Keep It Simple, Stupid !

    I was trying to make this whole thing too complicated. I noted that if I just used the table as the RecordSource, without all the fancy SQL, it would populate the form with the data from the first record of the table. I thought I'd go through the RecordSetClone routines to select the proper record, but then remembered that Forms have a Filter property and a FilterOnLoad Boolean.

    Voila! Set the Filter to PreferenceLoginName = [forms]![fLoginDialog]![cboLoginNameSelect] and FilterOnLoad to Yes and the one record that matches is what populates the form. Simple!

    Thanks to all that took time to look at this and Stephan for his responses.
    Pete
    PGT

Tags for this Thread

Posting Permissions

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