Exporting data into a new table

[bloody_football]

I have a form set up so the sales staff can enter quotes into a table that was set up for this. I then have another form that searches through the quotes in case the sales staff need them later (i.e it turns into a sale), from that form I wish to set up a button that exports the quote data into another table (I have my reasons for this). I don't wish to delete the old record, simply create a record in the new table.

 

[Surjer]

Sounds like you want to use an append query. Might try searching for that...

 

[bloody_football]

Okay, I have finally found it all but have another question -

The code below works

Private Sub ConvertHire_Click()
DoCmd.RunSQL "INSERT INTO Hire (ID, Company) VALUES (" & ID & ", '" & Company & "')"
End Sub

When it is excecuted though it asks the user if they are sure, how do I get it not to ask and simply do it?
Edit - I also wish to make the INSERT line incredibly long (15 fields) so how do i get the code to wrap around? Each time I try it I stuff it up

 

[WayneRyan]

bloody,

Continue with "& _"

Private Sub ConvertHire_Click()
DoCmd.RunSQL "INSERT INTO Hire (ID, Company) " & _
             "VALUES (" & ID & ", '" & Company & "')"

Messages:

Tools --> Options --> Edit/Find --> Confirm

Wayne

 

[bloody_football]

Thanks Wayne, all working a treat now

James

 

[WayneRyan]

James,

Glad to hear it.

See ya,
Wayne

 

[Surjer]

To insure the warnings do not get displayed

Private Sub ConvertHire_Click()
docmd.setwarnings = false
DoCmd.RunSQL "INSERT INTO Hire (ID, Company) " & _
"VALUES (" & ID & ", '" & Company & "')"
docmd.setwarnings = true

 

[bloody_football]

docmd.setwarnings = false
&
docmd.setwarnings = True

returns a 'argument not optional'

 

[WayneRyan]

bloody,

docmd.setwarnings = false
&
docmd.setwarnings = True

The second line (with just the "&") is not correct. It should be replaced
with the prior example from surjer.

Wayne

 

[bloody_football]

Oops my fault - the & wasn't supposed to be part of the code. I found the problem anyway, the code is

docmd.setwarnings false

it doesn't have the = signs.

 

[Surjer]

sorry bout that

 

[bloody_football]

sorry bout that

Nothing to be sorry about, you did point me in the right direction

Yet Another question -
I have the INSERT line 15 fields long, now if at least one field is empty none of the information is inserted How can I get around this?

 

[WayneRyan]

bloody,

If you get your table in Design View, you'll see that at least one of the
fields has Required = Yes and/or AllowZeroLength=No.

Either change the attributes, provide some default values, or use something
like the Nz function.

Wayne

 

[bloody_football]

Wayne

Thanks for that, all fixed.

James