INSERT INTO problems

[sleptema]

Hey Guys.

I am having issues with the INSERT INTO function. I have torn through google for the last 3 hours and I cannot find a resolve. Basically everytime I initiate the code, it brings up the table but it hasn't added any details in. I seriously can't figure out what is wrong.

 DoCmd.OpenTable "tblTerrainRepairJobs", acViewNormal, acAdd
            DoCmd.SetWarnings False
            Dim CustomerString, SiteString As String
            CustomerString = cboCustomer.Value
            SiteString = cboSite.Value
            DoCmd.GoToRecord acDataTable, "tblTerrainRepairJobs", acNewRec
            Dim StrSQL As String
            StrSQL = "INSERT INTO tblTerrainRepairJobs (Customer, Site) VALUES ('" & CustomerString & "', '" & SiteString & "')"
            DoCmd.RunSQL StrSQL
            'DoCmd.Close acTable, "tblTerrainRepairJobs", acSaveYes

I've tried changing values so often in so many combinations. I am completely out of ideas.

And yes, I am very new to this.

Cheers for any help.

[ssanfu]

Not sure what you are trying to do, but if are trying to add a new record to table tblTerrainRepairJobs, with the data coming from a form, try this:

            Dim CustomerString As String
            Dim SiteString As String
            Dim StrSQL As String

            CustomerString = Me.cboCustomer
            SiteString = Me.cboSite
 
            StrSQL = "INSERT INTO tblTerrainRepairJobs (Customer, Site) VALUES ('" & CustomerString & "', '" & SiteString & "')"
            CurrentDB.Execute StrSQL, dbfailonerror

[sleptema]

Not sure what you are trying to do, but if are trying to add a new record to table tblTerrainRepairJobs, with the data coming from a form, try this:

Yeah I realise the way I set it out wasn't optimal, but the code I have written shouldn't be wrong?

Still doesn't work.

And yes, I have a few combo boxes and a few text boxes where the data needs to be transferred into the table tblTerrainRepairJobs. The data needs to be on a new line as there are going to be multiple repair jobs, hence it having the gotorecord command.

I am just trying to get the first couple of records inserted, to make sure the function works (customer and site), I have 10 other fields that eventually need to be added to the code.

[sleptema]

I feel like the worlds biggest idiot.

I just closed the table and reopened it with all the results.

I guess I just wasn't refreshing it.

Cheers for all your help anyway haha.

[ssanfu]

Yeah I realise the way I set it out wasn't optimal, but the code I have written shouldn't be wrong?

Well, not wrong, but a lot of unnecessary code. And a couple of minor errors.

---------------------

DoCmd.OpenTable "tblTerrainRepairJobs", acViewNormal, acAdd

You don't have to open the table (and shouldn't) to do the insert (append).

---------------------

Dim CustomerString, SiteString As String

Here you declared CustomerString as a Variant and SiteString as a string

---------------------

DoCmd.GoToRecord acDataTable, "tblTerrainRepairJobs", acNewRec

This line is unnecessary as the SQL insert command creates a new record. You will probably end up with empty records using the above line.

---------------------

DoCmd.RunSQL StrSQL

"DoCmd.RunSQL" is executed through Access and pops up warning messages unless you turn the messages off. I don't like turning messages off.
"Currentdb.Execute" runs through Jet, the database engine. It won't popup error messages unless you use the argument "dbfailonerror".

---------------------

'DoCmd.Close acTable, "tblTerrainRepairJobs", acSaveYes

I have never used this command; I believe it is for saving changes to the table structure, not saving changes to the data.


Good luck with your project....