Declaring from form to VBA (1 Viewer)

[Bigmo2u]

I know this is a newbie question but:

When I pass the information from a form to a function in VBA do I have to reformat dates and change the form information that should be numbers to numbers or is this something access will do on its own?

all fields on the form are unbound fields, does the form and vba consider them all strings or not?

I know newbie question.

 

[Isskint]

You should not have to. The 'value' in a textbox is essentially a Variant type variable. If it is numeric, access will see it as numeric, text as string etc.
However it does not hurt to use INT or DATEVALUE or someother function to ensure the value is passed in the correct way.

 

[Pat Hartman]

For bound controls, Access knows the datatype and acts accordingly. For unbound controls, you need to specify a format in the format property. That way Access will only allow dates in fields you define as "Short Date" and numeric values in fields you define as "Currency" or "Percent" or "Standard", etc.

As long as the control properties are set correctly, you don't need to format them. In fact, you should know that using the Format() function will actually turn a date into a string or a numeric value into a string and from that point on, the variable will act like a string rather than a date or numeric value.

 

[Bigmo2u]

Thank you for the help. I greatly appreciate it.

 

[Bigmo2u]

Okay I have done what you have said but now I am getting an error.

Here is the code that gives me error:

With db
                               
    .Execute "INSERT INTO tblCurrentLocationID ( SerialNumberID, SiteLocationID, ZoneID, SectionID, " & _
             "RowID, BayID, clDate, clTime) " & _
             "VALUES('" & ssn & "', sli, zid, sid, rid, bid, #cld#, #clt#)"

End With

Here is the Function:

Function insertCurrentLocation(ssn As String, sli As Long, zid As Long, sid As Long, rid As Long, _
                               bid As Long, cld As Date, clt As Date)
                               
Dim db As DAO.Database

With db
                               
    .Execute "INSERT INTO tblCurrentLocationID ( SerialNumberID, SiteLocationID, ZoneID, SectionID, " & _
             "RowID, BayID, clDate, clTime) " & _
             "VALUES('" & ssn & "', sli, zid, sid, rid, bid, #cld#, #clt#)"

End With
Set db = Nothing

End Function

This is the Sub that is passing the information from the form:

Private Sub btnSaveRecord_Click()
Dim fssn As String, fsli As Long, fzid As Long, fsid As Long, frid As Long, fbid As Long, fcld As Date, fclt As Date
Dim db As DAO.Database, rs As DAO.Recordset, iSql As String, fcli As Long

fssn = Me.txtsSerialNumberID
fsli = Me.txtSiteLocationID
fzid = Me.txtZoneID
fsid = Me.txtSectionID
frid = Me.txtRowID
fbid = Me.txtBayID
fcld = Format(Me.txtclDate, "m/d/yyyy")
fclt = Format(Me.txtclTime, "hh:mm")

Call insertCurrentLocation(fssn, fsli, fzid, fsid, frid, fbid, fcld, fclt)

End Sub

I went and formated and set the variables because this is the error I am getting:

Object variable or with block variable not set.

I added the With for the db.execute because i thought that was what it was, but still get the error. Not sure if it has to do with the primary key is an autonumber and I need to do something different?

 

[Bigmo2u]

Okay, found my issue with that error, god I forgot to set db = CurrentDb().

but now I get a new error, I knew I was going to get it because I know there is a trick to code for dates.

Error: Syntax error in date in query expression '#dd#'

 

[Isskint]

that is because you are not completing the VALUES string correctly

sli, zid, sid, rid, bid, #cld#, #clt#)"

should be

sli & ", " & zid & ", " & rid & ", " & bid & ", #" & cld & "#, #" & clt & "#);"