Results 1 to 4 of 4
  1. #1
    dgj32784 is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    22

    Pass Variable to Table Field with VBA


    First off, I'm an Access and VBA novice. So please excuse if there is a much easier way to get what I want. I'm working with Access07.

    I would like my unique id for a survey to be based on the current date/time and a three letter name acronym for the person conducting the survey. Example unique id format: 201103241300ABC. I've got the following code concatenating these "strings" and displaying the result in a message box for me to verify that it is working when the surveyor selects there name from a drop-down on a form. Now, how do I get the concatenated string to be "passed" to my survey id - either directly to the table or into the control on the form? The surveyor has no need to see the survey id, so I'd prefer if once they select their name from the combo box drop down that the code generates the survey id and puts it in the table. There's probably a simple way to do this but I'm at a loss.

    Code:
     
    Private Sub cboPrimarySurveyor_AfterUpdate()
    'Declare variables
    Dim strSurveyYear As String
    Dim strSurveyMonth As String
    Dim strSurveyDay As String
    Dim strSurveyTime As String
    Dim strSurveyor As String
    Dim strCalcSurveyID As String
     
    'Assign values to declared variables
    strSurveyYear = Format(Now, "yyyy")
    strSurveyMonth = Format(Now, "mm")
    strSurveyDay = Format(Now, "dd")
    strSurveyTime = Format(Time, "hhmm")
    strSurveyor = strSurveyorAbbrev
    strCalcSurveyID = strSurveyYear & strSurveyMonth & strSurveyDay & strSurveyor
    MsgBox strCalcSurveyID
    End Sub

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,431
    If it's part of the data on the form:

    Me.TextboxName = strCalcSurveyID

    If you want to go straight to the table, you can open a recordset on the table or execute SQL.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    dgj32784 is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    22
    Thanks Paul. That did the trick. I thought it would be something simple.

    David

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,431
    Happy to help David, and welcome to the site!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Pass Variable Values From One Form to Another
    By Nokia N93 in forum Forms
    Replies: 3
    Last Post: 03-07-2011, 11:47 AM
  2. VBA Pass-through Query with Variable
    By smaumau in forum Programming
    Replies: 0
    Last Post: 12-06-2010, 09:10 AM
  3. Replies: 2
    Last Post: 05-09-2010, 04:10 AM
  4. Replies: 5
    Last Post: 04-05-2010, 11:43 AM
  5. pass a variable to the MsgBox function
    By 3dmgirl in forum Programming
    Replies: 2
    Last Post: 04-19-2007, 07:14 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 - Senior Forums