Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    CarlV is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Sep 2011
    Posts
    23

    Supply data to 'sign out' form from 'sign in' form using common element

    Greetings,



    It's been years since I did anything 'technical' in Access and now I'm trying to help out our Service Desk folks by updating their Access Database. I moved all the 'data' into an SQL database. I have 4 databases for 4 different functions. The entire group of databases makes up for the equipment sign-in/sign-out database.

    DB-1 = Customer_Sign_IN
    DB-2 = Customer_Sign_OUT
    DB-3 = Switch_Sign_IN
    DB-4 = Switch_Sign_OUT

    In DB-1 and DB-2 there are fields that are identical. The common field between them will always be "Ticket #". A customer comes in and filled out the SIGN IN with all the required data. That data is now stored in that database. When the customer returns to pickup their equipment, they go to the SIGN OUT form and fill in the data.

    My issue is this.... I have made the "Ticket #" field a DROP DOWN that pulls from DB-1. I would like for the action of selecting your ticket number from that drop down to auto-populate the other fields in the "SIGN OUT" form using the data that was input into the "SIGN IN" form.

    I've read quite a few posts on this, but honestly none of it makes sense to me right now. It could be that I've been zoned into this thing for the past 8 hours and now my brain is fried.

    I would greatly appreciate any assistance anyone can provide. I know it will be a query, but damned if I can figure out how to get the rest of the query to work.

    Thanks in advance,
    Carl
    Kandahar, Afghanistan

  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
    It can all be accomplished with the AfterUpdate event of the ComboBox assuming the RowSource query of the cbo pulls all of the information you need. A cbo has a Column() property that allows you to access *all* of the fields of the RowSource that is zero based so .column(1) is the 2nd column.

  3. #3
    CarlV is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Sep 2011
    Posts
    23
    I hate to say this....but that sounds greek to me....I don't even like admitting that.

  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
    I have made the "Ticket #" field a DROP DOWN that pulls from DB-1
    How did you do this? Is it a "Lookup Field" or just a ComboBox on the form? Hopefully the latter.

  5. #5
    CarlV is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Sep 2011
    Posts
    23
    It's a combo box, used a Query as the Row Source. The query is using the 'Customer_Sign_In' database to provide the drop down of ticket numbers, which is the unique identifier shared in both databases.

  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
    Add the rest of the fields you need to the RowSource query so they are available to copy into the controls of the form. You can set the Column Widths to 0" for all of the other fields so the user is not confused with the display. Be sure and change the Number of Columns to what is appropriate.

  7. #7
    CarlV is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Sep 2011
    Posts
    23
    So in the Query I created for the 'Ticket #' I could add all of the other fields that I want to auto-fill in that query? When I open it in the edit view, I can place all those at the bottom in the grid layout "Field, Table, Sort, Show, Criteria, or:"???? If I add them all in there, in my actual form, how do I make it so once I select the Ticket # in the combo box the rest of the form? This is new territory for me here.....
    I also want to say how much I appreciate your help here.... Being over here in Afghanistan our resources are pretty much limited to the internet.

  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
    We will create some code for the AfterUpdate event of the ComboBox that copys the rest of the cbo fields to the proper control on your form. Let's get the cbo to pull all of the fields first. Change the query as you described and also change the Number of columns on the cbo so we can see the new fields. You are goping to want to change the ColumnWidths property to .5";.5";.5";...untill all of the fields are visible when you drop down the cbo. Come to think of it the ComboBox wizard can probably do all of this for you. I rarely use then so forgot about that option.

  9. #9
    CarlV is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Sep 2011
    Posts
    23
    RuralGuy,

    In my TicketQuery I have used the 'sign-in' table of data to query the 'Ticket #' for the dropdown combo box. Now, On my 'Sign_Out" form if I click the dropdown I get the list of ticket number. The TicketQuery also has "First Name", "Last Name", "Unit", "DSN/Roshan" and "Service Tag/ Serial Number" listed in the spreadsheet layout next to "Ticket #". I have placed the check box in "Show" for all of these fields in the actual Query. If I run the Query by itself, I get all the requested information returned. When I tie that query to the dropdown box for "Ticket #" in my form for "Sign_Out" I only get returned ticket numbers that are currently listed in the "Sign_In" database. I get no other fields of data. I did change the ColumnWidth per the directions.

    I understand the part about creating an "AfterUpdate" event, however I went into it and I'm not quite sure exactly what I'm doing in that part of Access. I'm 100% willing to learn, if you're willing to be the teacher!

    Thanks again in advance!!!!

  10. #10
    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
    We'll get it done together. Did you set the ColumnCount to 6? Post back with what you have in the ColumnWidths property.

  11. #11
    CarlV is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Sep 2011
    Posts
    23
    You're awesome!!!!

    The ColumnCount is set to 6 now. It wasn't before....probably why I need people like you around! HAHAHA!!!

    ColumnWidths is set to 0.5";0.5";0.5";0.5";0.5";0.5".

    I now see all the fields of data when I hit the dropdown box.

    I suppose this is where we start the EventProcedure.

  12. #12
    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
    I'll get you started and you can finish the code. When we're done, you can hide all but the Ticket# in the cbo dropdown. Press the "..." button in the AfterUpdate event row ov the cbo and let it build the event stub. Then between the lines put:
    Me.[FirstNameControlName] = Me.CBOName.Column(1)
    ...replacing my names with the your actual names.

  13. #13
    CarlV is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Sep 2011
    Posts
    23
    Private Sub Ticket__AfterUpdate()
    Me.[RANK/CIV]=Me.[cboTicket #].Column(1)

    Is this how it has to be formatted since "Ticket #" has a space in the name?

  14. #14
    CarlV is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Sep 2011
    Posts
    23
    Okay...wrote this all out.....

    Private Sub Ticket__AfterUpdate()
    Me.[RANK/CIV]=Me.[cboTicket #].Column(1)
    Me.[First Name]=Me.[cboTicket #].Column(1)
    Me.[Last Name]=Me.[cboTicket #].Column(1)
    Me.[Unit]=Me.[cboTicket #].Column(1)
    Me.[DSN/ROSHAN]=Me.[cboTicket #].Column(1)
    Me.[Service Tag/ Serial Number]=Me.[cboTicket #].Column(1)
    Me.[Make/ Model]=Me.[cboTicket #].Column(1)
    End Sub

    This is exactly how it looks in the Visual Basic editor. When I save it all, and then go back to the form and click the drop down and select a Ticket # it gives me a Run-Time error '2465
    Microsoft Office Access can't find the field '|' referred to in your expression.

    Which strangely enough...the fields are all there....
    I click on Debug and it takes me to the first line of the VB.

  15. #15
    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 are correct and that is why it is strongle recommended that special characters and embedded spaces *not* be used in any names. I personally use a CamelFontName.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Student Sign in/out system
    By torpid in forum Programming
    Replies: 12
    Last Post: 08-25-2012, 08:04 AM
  2. Pound sign in text field
    By eww in forum Programming
    Replies: 3
    Last Post: 09-29-2010, 10:30 AM
  3. Replies: 2
    Last Post: 08-19-2010, 09:33 AM
  4. Daily Sign In form
    By JHansford in forum Access
    Replies: 2
    Last Post: 12-09-2009, 08:41 AM
  5. The ampersand sign (&) and Access reports
    By tigers in forum Reports
    Replies: 2
    Last Post: 09-28-2009, 08:23 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