Data type mismatch in criteria expression, Access 2007, INSERT query from cmd button

[sgtpruitt]

Hey guys. Noob Here. I have been searching for a while but just can't come to a conclusion. Need some help.

I am creating a form to add new records from a click event. I think I have the query pinned down but I'm getting "Data type mismatch in criteria expression" when i try to execute.

txtPointName allows a user to enter a point name
txtTPID = the result of a Dlookup which runs the query to return the max(trendedpointid)+1
cboUnitType = a value list of 3 numbers

Fields:
TrendedPointID = int
UnitTypeID = int
TrendedPointName = varchar
ModBy = varchar
ModDt = date/time

I'm sure I need to do a conversion somewhere, somehow, but am unsure of where and what the syntax would be. Any help would be appreciated.

Private Sub cmdAddPoints_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryAddPoint")

strCriteria = Right(strCriteria, Len(strCriteria) - 1)

strSQL = "INSERT INTO dbo_TrendedPointLookup ( TrendedPointId, UnitTypId, TrendedPointName, ModBy, ModDt ) " & _
         "VALUES(' & Me!txtTPID & ' AS Expr1, ' & cboUnitType & ' AS Expr2, '" & txtPointName & "' AS Expr3, 'Admin' AS Expr4, Now() AS Expr5);"


qdf.SQL = strSQL

DoCmd.OpenQuery "qryAddPoint"


Set db = Nothing
Set qdf = Nothing
End Sub

[rzw0wr]

Welcome to AccessForums

I need to ask, why are you doing it this way?

Is the table not bound to this form?

Dale

[sgtpruitt]

Well I'm a network guy trying to do database stuff haha. The reason I tried this method is that the TrendedPointID needs to increment by 1 each time a record is added to keep the IDs unique. There is already a PK in the table TrendedPointPK. The table is not bound to the form.

The one idea I had was to pull the max value and add 1 then store it in a text box.


Basically I was running this query at the server to add points and want to provide an interface so others can do so. There is a sequence object that number TrendedPointID for me, PointMapping.TrendedPointId_SEQ.

insert into trendedpointLookup (trendedpointid, unittypid, trendedpointname, commonname, modby, moddt)
select next value for PointMapping.TrendedPointId_SEQ, 0, 'ISCALC HEATING DD', 'Admin', getdate()

If you have a better solution I would be glad to go a different route.

[boblarson]

In your original query you have quotes going around the integers and integers do not get quotes.

[rzw0wr]

Sorry sgt, I know nothing about Access server.
Very little about Access but nothing about server.

Dale