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.

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

 

[Khalid_Afridi]

Welcome to AWF!
you can add the generated ID to the control/Field on the form:

me.YourControlOnForm.Value=strCalcSurveyID

with your code add this line in your 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

me.YourControlOnForm.Value=strCalcSurveyID

End Sub

 

[dgj32784]

Khalid,

Thanks. Worked like a charm. I actually had a missing component to the value for strCalcSurveyID. So I've re-posted what worked.

Here's and ignorant question for you: What is "Me." in this case? The form? I'm still learning what components are called in VBA.

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(Now, "hhnn")
    strSurveyor = strSurveyorAbbrev
    strCalcSurveyID = strSurveyYear & strSurveyMonth & strSurveyDay & strSurveyTime & strSurveyor
    Me.strSurveyID.Value = strCalcSurveyID
 
End Sub

Thanks again!

 

[Khalid_Afridi]

Here's and ignorant question for you: What is "Me." in this case? The form? I'm still learning what components are called in VBA.

Glad that it works for your actually in VBA Me. is the current object/field on the form:

If you are in the VBA section of a form and want to refer to a control on that form then the quickest most efficient way of achieving a connection to this control would be the most direct Me.ControlName

Me.[Field1] for exemple makes reference to the current form and Field1 in the form.

Regards,

 

[dgj32784]

Thanks for the explanation. So is there no way to "pass" the strCalcSurveyID straight to the table without first sending it to a control on the form? I have a control on the form now of course, but I'd prefer it was not there taking up space since the user is not supposed to enter anything. I could make invisible on the form but was wondering if there was a different way.

Thanks,
David

 

[Khalid_Afridi]

David!
If your form has the underline table (the DataSource of the form) is directly your table/query, then you can refer your generated id directly to that field.

Me.YourFieldOnTherForm.Value = strCalcSurveyID

 

[dgj32784]

Okay. I think I understand. I believe that is what is happening now. I guess I was just curious if I could drop the field on the form and have the calculated value go straight to the table, without have a field/control on the form. I can cope with having it on the form, just might confuse the users as to whether they are supposed to do something or not. I've added text to try and clarify that the field/control is autogenerated and they are not supposed to type there.

 

[Khalid_Afridi]

If you don't want to show your field to your users then you can hide it on the form. In design view of the form, you can goto the format property of the field/control and set it to Visible Yes/No.

You also can directly put your generated id to your desire table by inserting it through INSERT INTO table Statement with WHERE clause.

 

[dgj32784]

Okay. Changing visible to no will work fine for me. Particularly since I did not quickly figure out the syntax to make the INSERT INTO do what I want in VBA

Thanks for all your help.