Error In Statement

[klynch0803]

I hae attached a database. If you open form fdatrecinventory and fill in the date, select item, enter Qty in first field and selct Liter for example it fills in all fields.

In the VDCode it should insert the Date, Item, QtyInserted, Cost into table tdatInventoryRec.

Problem is I get an error Invalid datatype in Insert Satatement. I can't figure out what the error is I have checked that all fields are correct datatypes and can't figure out if its in the code. I'm not good at writing code by far so it could very well be in the code.

Could someone please help me find the issue and fix it and upload the file back or show me where it is and how to fix it pls?

 

[ted.martin]

I think the ' after the second # maybe the problem

 

[klynch0803]

That didnt seem to change nothing.. Still got the same error..

 

[ted.martin]

I think you will find this works. Not being an expert on SQL I prefer this method. By the way - your close button has the wrong form name.

Private Sub Command7_Click()
On Error GoTo Err_Command7_Click
Dim strsql As String

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("tdatinventoryrec", dbOpenDynaset)

With rst
.AddNew
!RecDate = RecDate.Value
!Item = Item.Value
!Qty = QtyInserted.Value
!Cost = Cost.Value
.Update
End With

Set db = Nothing: Set rst = Nothing


' DoCmd.RunSQL "INSERT INTO tdatmeasurments (RecDate,Item,QtyInserted,Cost) VALUES(#" & RecDate & "'#,'" _
' & Item & ", QtyInserted & ", Cost & "')"



'DoCmd.GoToRecord , , acNewRec
RecDate.SetFocus

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description & Err.Number
Resume Exit_Command7_Click

End Sub

 

[klynch0803]

TY very much it works very well.

Any idea how come the "docmd.gotorecord, ,acnew" doesnt work when you click the save to clear the form to be ready for the new entry?

 

[ted.martin]

Yes - because essentially you have all the text boxes as 'unbound' and therefore there is no record to set to new. Infact looking at the form again, the entire form is unbound as the SQL you have in the record source is not required - delete it as it is not doing anything.

If you want to blank everything, I suggest you manually make the boxes = "" or null after the update.

 

[klynch0803]

I figured it out.. Thanks for your help..

You cant use docmd.gotnewrecord, , acnew in a form not bound to a table duh....

You have to use after the insert code "me.textboxname = "" "" when a simple text box on a form is used.

Also ty for the heads up on the close button being attached to wrong form but I actually imported this out of another database for the posting versus sendng the complete database to save server space for the forum.

 

[klynch0803]

OK So the Receiving ABove works fine. I then used the same code changing the table it writes to but now it doesnt work in this form.. It is the same setup basicaly. It gives an error "Item Not Found In Collection" I have checked everything and dont seem to see anything wrong here either.. It seems to happen though when it gets the VB to insert the Price...

What is wrong with me I can find any errors today LOL..

I have attached just the tables and form that are in reference to each other below..

 

[ted.martin]

The fields with the ! MUST match the name of the field in the table you are writing too. Your Line !SaleTotal = Price.Value is wrong as there is no field called SaleTotal in the Usage table.

You correctly discovered the location of the error.