Results 1 to 9 of 9

Thread: How to link a field to another, depending on information filled in one field

  1. #1
    LionelSpratt is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2013
    Posts
    11

    How to link a field to another, depending on information filled in one field

    Hello guys.
    I don't know if the question is clear,
    But let me explain

    I have a couple of fields in Access. To explain I will show here the fields I have and their datatype

    ID: Autonummer
    Amendment: Text
    Basis info: Yes/No
    Sex: Yes/No
    Period: Yes/No
    Salary:Yes/No
    Shift:Yes/No

    Now, in the amendment field, I've inserted a combo box, with 3 options in it. Let's say option A,B and C

    The thing I want access to do, is, when option C is selected, I want fields Period, Salary and Shift to be automatically "Yes"
    But if option A or B is selected, the the user must choose what the other fields are going to be either Yes or No

    Anybody can help me with this ??

  2. #2
    rzw0wr is offline I will always be a newbie
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Indiana
    Posts
    479
    Private MyCombo_Click()
    If me.Mycombo = "C" then
    Me.thischeckbox=true
    .........
    end if

  3. #3
    LionelSpratt is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2013
    Posts
    11
    Quote Originally Posted by rzw0wr View Post
    Private MyCombo_Click()
    If me.Mycombo = "C" then
    Me.thischeckbox=true
    .........
    end if
    What is that ???

    I believe I must copy and paste that into VBA
    But.....how exactly does it work ?
    What is "thischeckbox".....what checkbox is it refering to ?

    I have more combo box in the table, I just wrote a few fields, just to make my point

    Please explain solution so I can learn from it.

  4. #4
    rzw0wr is offline I will always be a newbie
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Indiana
    Posts
    479
    Sorry,
    My answers tend to be a little terse.
    open the form in design view.
    Select the combo box you are using. Open the property sheet and go to events tab.
    Select the ON Click event, click the drop down arrow and select Event procedure then click the 3 dots on the right of that line.
    The VBA editor will open and a sub will be there named Private Sub "Whatever your combo name is"_Click().

    Insert in this sub the following.
    if Me.Your combo box name goes here = "C" then ' "Your combo box name" = Whatever you have your combo box named as.
    me.Your check box name goes here = true
    Do this for all the check boxes you want to change.
    end if ' end the if statement.

    For the other selections in your combo box use the same code but change the check box names and either true or false.

    If you need more help please ask.

    Dale

  5. #5
    LionelSpratt is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2013
    Posts
    11
    Quote Originally Posted by rzw0wr View Post
    Sorry,
    My answers tend to be a little terse.
    open the form in design view.
    Select the combo box you are using. Open the property sheet and go to events tab.
    Select the ON Click event, click the drop down arrow and select Event procedure then click the 3 dots on the right of that line.
    The VBA editor will open and a sub will be there named Private Sub "Whatever your combo name is"_Click().

    Insert in this sub the following.
    if Me.Your combo box name goes here = "C" then ' "Your combo box name" = Whatever you have your combo box named as.
    me.Your check box name goes here = true
    Do this for all the check boxes you want to change.
    end if ' end the if statement.

    For the other selections in your combo box use the same code but change the check box names and either true or false.

    If you need more help please ask.

    Dale
    So it must look like this:

    Private Sub Amendment_Click()
    If Me.Amendment = "C" Then
    Me.Period = True
    Me.Shift=True
    Me.Salary=True
    End If
    End Sub

    Let me know if it's correct.......cause it's not working till now
    I dont see described what the code must do if the statement is false. Isn't this missing ?

  6. #6
    rzw0wr is offline I will always be a newbie
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Indiana
    Posts
    479
    Sorry I made a mistake.
    try :
    If Me.Amendment.Column(1) = "C" then.

    A combo box will show the columns that are not hidden.
    Usually in a combo box the source looks something like this YourID, Some Text.
    The "Your ID" is usually hidden from view and the box only shows the text part.
    The numbering on a combo box starts at 0 not 1.
    so, when you check a combo box you need to check the proper column. If you are checking column 0 no need to call column(0).
    This is why for you, you need to check column(1) of your combo box.
    I am also assuming that there is only 2 columns in your text box and the "C" you are wanting is the 2nd in the list.

    Sorry to be confusing, I have a terrible explaining combo boxes even in person.

    You can put in an ELSE statement if Me.Amendment is false.
    Let's get the if working first or I will confuse you more.

    Dale

  7. #7
    LionelSpratt is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2013
    Posts
    11
    So it's

    Private Sub Amendment_Click()
    If Me.Amendment.Column(3) = "C" Then
    Me.Period = True
    Me.Shift=True
    Me.Salary=True
    End If
    End Sub

    ??

    Still not working
    I have 3 options in my combo box......A, B and C

    When I select C, the fields Period,Salary and Shift, wont get checked automatically

    What am I doing wrong ??

  8. #8
    rzw0wr is offline I will always be a newbie
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Indiana
    Posts
    479
    Copy and paste your record source for your combo box, Lionel.

    I may be thinking one thing and you have it setup another way.

    Dale

  9. #9
    LionelSpratt is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2013
    Posts
    11
    Record source ???
    You mean the row source ??? This one is set to Value List

    (I don't know what's record source)

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

Similar Threads

  1. Replies: 1
    Last Post: 02-20-2012, 11:02 AM
  2. Replies: 1
    Last Post: 07-26-2011, 04:18 PM
  3. Field automatically filled
    By Douglasrac in forum Forms
    Replies: 3
    Last Post: 02-15-2011, 09:33 AM
  4. Link Master Field and Link Child Field
    By evander in forum Forms
    Replies: 2
    Last Post: 05-25-2010, 08:13 PM
  5. Replies: 4
    Last Post: 01-19-2010, 03:36 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
  •  
Tech Forums: Microsoft Office Forums