Pass Variable to Table Field with VBA

[dgj32784]

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.

 
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

[pbaldy]

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.

[dgj32784]

Thanks Paul. That did the trick. I thought it would be something simple.

David

[pbaldy]

Happy to help David, and welcome to the site!