Insert query does something weird 1

[JoeriMJ]

Hi,

I have a form with a few controls, simple button to add some information to a tabel in Access 2003.
I'm using a string "query" for the INSERT statement.
So when i wish to execute the query I just do
docmd.runSQL query.

Now the problem:
Adding the record works fine. It gets a new ID and is added at the bottom of the table. Now here's the catch, The first record in my table is somehow pasted over with the data I inserted from the form.
This ofcourse is very annoying and will surely give me incorrect information in the future if this problem is not fixed.

I've tried executing the statement as a query in Access itself, It works perfectly fine here.

Anyone got any ideas?

kind regards

JoeriJM

 

[TheAceMan1]

how are ya JoeriMJ . . .

Post the SQL! . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[JoeriMJ]

query = "INSERT INTO deliveries (quantity, deliveryDate, pbrID) VALUES ( " & quantity & ",'" & deliverDate & "'," & pbrID & ");"

CurrentDb.Execute query

that's the query and the execution of it.

 

[TheAceMan1]

JoeriMJ . . .

A first shot at this:

[blue]   Dim SQL As String
   
   SQL = "INSERT INTO deliveries (quantity, deliveryDate, pbrID) " & _
         "VALUES (" & Me!quantity & ",[purple][b]#[/b][/purple]" & Me!deliverDate & "[purple][b]#[/b][/purple]," & Me!pbrID & ");"
   CurrentDb.Execute SQL[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[JoeAtWork]

I have a form with a few controls

Umm, by any chance is that form bound to the table, and the controls are bound to fields in the table?

If this form is only used for adding (and you are using code), I suggest you make sure the form is unbound (i.e. set it's RecordSource property to nothing).

 

[Darrylles]

Hi,

Hmm, problem obviously not important enough to reply to your responses. Surprises me how important the problem is BEFORE the solution.

It's almost like experts are here 'at their service'.

Pig-ignorant people everywhere.

ATB

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[JoeriMJ]

Hi, the date is saved in the table as a string, so the # shouldn't be the problem. And the controls on the form have a source to the table. I'm going to try and unbind them from the table. I'll let you know if it worked.

@Darrylles: I don't know about you, but when i get off work at five o' clock on a friday night, I sometimes don't turn on my computer for a while, and therefore I don't respond that fast.
Though this is the problem that is holding me back for finishing this project, I think I care enough about it. Asking in this forum was the last thing I tried. After googling it for an afternoon and trying to debug it, I gave up, so sorry if I offended you or anyone else by not replying fast enough.

 

[JoeriMJ]

Indeed, after unbinding the form it works. I see now that when the form was loaded, the first record was inserted. Only thing is, when the form loaded I did a emptyFields() method so that's why it wasn't clear at first.

Thanks for your help