Re: Data Type mismatch with autonumber

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


Hmmm. Rather than use a msgbox to grab the text that you've built... try
adding a breakpoint (Debug, Toggle Breakpoint) on the next line after your
string building (provided that it's not just a dim statement or similar).
Then run the code by opening the form and doing whatever it is that triggers
it. The code will stop at that line.

Next open the immediate window (View, Immediate Window). In there, type
?cmd2.commandtext and hit enter. That will give you your built string.

Copy that and paste it into the SQL view of a new query (you might need to
lose the opening and closing speech marks). Start a new query, then View,
SQL View, delete whatever is already there and then paste your text. Then
try switching to design view. If that works, try running the query and see
if you get a more meaningful error.

What does that give you?


"Aranyx" <aranyx@xxxxxxxxx> wrote in message
news:1135202386.085610.305910@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> Thanks Rob. That seems to work fine. Only problem was that was that I
> abridged the SQL statement because I update 12 fields and I didn't feel
> like retyping everything in the messagebox. I suppose that was pretty
> dumb of me though, because it would appear the error is somewhere in
> there.
>
> Another thing I tried creating a recordset and selecting using the same
> where clause and that worked fine. It also didn't work when I tried a
> criteria of another unique field from the table. So the error I would
> agree with you Rob not to trust the error message.
>
> I can try posting my VBScript, since the error will probably be in
> there.
>
> cmd2.CommandText = "UPDATE Assets " _
> & "SET Assets.LabStaffUse = 'Disposed', " _
> & "Assets.Status = 'Disposed-Internal', " _
> & "Assets.DateModified = DATE(), " _
> & "Assets.PhysicalInventoryDate = DATE(), " _
> & "Assets.UserUpdated = '" & txtUser.Value & "'," _
> & "Assets.EmployeeNumber = '" & cmbCampus.Value &
> txtRoomNumber.Value & "', " _
> & "Assets.Locked = 'No', " _
> & "Assets.EligibleForRefresh = 'No', " _
> & "Assets.LastName = '" & strLast & "'," _
> & "Assets.FirstName = '" & strFirst & "'," _
> & "Assets.CustodianName = '" & txtCustodian.Value & "', " _
> & "Assets.InventoryStatus = 'Disposed', " _
> & "Assets.RecordStatus = 'No' " _
> & "WHERE Assets.ID = " + strID + ";"
>
> Thanks for the help.
>


.

Relevant Pages

  • Re: How to use a parameter query with multiple values?
    ... You have to built the string made of the concatenation of the selected ... I have gotten it to work where, when the query is run, the parameter asks ... This can work as long as there are no commas within the subject ...
    (microsoft.public.access.queries)
  • RE: LDAP Search for memberOf zero returns
    ... I am still curoius however why this query would work but the one built into ... > and i know the memberof has to be an exact match to work. ... > member of and it return the string.. ...
    (microsoft.public.windows.server.active_directory)
  • Re: unbound subform and record set
    ... want the string to be passed to a form. ... the string is built because there are parameters the user will enter (Date, ... Me!FormControl.SourceOject = sqlstr ... The query will not be updateable. ...
    (microsoft.public.access.modulesdaovba)
  • converting strings to dates in MS Access
    ... I am trying to convert a string such as 071505 in a query in Access to a ... I used all of the date built in functions in Access 2003 but none seem ... Prev by Date: ...
    (microsoft.public.access.queries)
  • Re: OT: Iraqi elections
    ... > length of string from one corner to the opposing corner, ... the ancient pyramids of Egypt could only have been built by aliens were: ... * To join/leave the list, search archives, change list settings, * ...
    (comp.sys.hp.mpe)