Insert Into statement in a function 
Author Message
 Insert Into statement in a function

I have created a module, a function within the module and entered the
following code within the function. Once i hit the enter key on the line
containing the insert into tbltest statement I get an end of statement
expected error. Can anyone tell me what I have done wrong here, this is
driving me nuts! Thanks~

-------------------------------------------
Function ConsultantList()
Dim myrecordset As Object
Dim fullname As String
Dim consultantid As Integer

Set myrecordset = Application.CurrentDb.OpenRecordset("dbo_RESRES")
While Not myrecordset.EOF
    fullname = myrecordset.Fields("Name").Value
    consultantid = myrecordset.Fields("Id").Value

    insert into [tbltest(id,consultantname)]
    values [consultantid,fullname]

Wend
End Function
-----------------------------------------------------



Sun, 01 Aug 2004 14:44:45 GMT  
 Insert Into statement in a function

Quote:

> I have created a module, a function within the module and entered the
> following code within the function. Once i hit the enter key on the line
> containing the insert into tbltest statement I get an end of statement
> expected error. Can anyone tell me what I have done wrong here, this is
> driving me nuts! Thanks~

> -------------------------------------------
> Function ConsultantList()
> Dim myrecordset As Object
> Dim fullname As String
> Dim consultantid As Integer

> Set myrecordset = Application.CurrentDb.OpenRecordset("dbo_RESRES")
> While Not myrecordset.EOF
>     fullname = myrecordset.Fields("Name").Value
>     consultantid = myrecordset.Fields("Id").Value

>     insert into [tbltest(id,consultantname)]
>     values [consultantid,fullname]

> Wend
> End Function
> -----------------------------------------------------

You can't just enter a sql statement in VBA like that, you need to tell
Access to execute it, you also seem to be confusing your square brackets
and a couple of other things too. Also, try and avoid using reserved
words such as Name as field names, it can cause all sorts of hard to
find problems. Try the following modified routine:

Dim myDB As DAO.Database
Dim myRecordset As DAO.Recordset
Dim strFullName As String
Dim intConsultantID As Integer

Set myDB = CurrentDb
Set myRecordset = myDB.OpenRecordset("dbo_RESRES")

Do Until myRecordset.EOF

        strFullName = myRecordset![Name]
        strConsultantId = myRecordset!Id
        strSQL = "INSERT INTO tblTest(id, consultantname)  VALUES(consultantid,
fullname)

        myDB.Execute strSQL

        myRecordset.MoveNext

Loop

Set myRecordset = Nothing
Set myDB = Nothing

--
Hope this helps
Tony Oakley (MVP)



Sun, 01 Aug 2004 17:27:29 GMT  
 Insert Into statement in a function
Is there a reference that needs to be made before dao can be used? I am
getting a compilation error and code tips do not come up when typing the
lines containing dao.


Quote:

> > I have created a module, a function within the module and entered the
> > following code within the function. Once i hit the enter key on the line
> > containing the insert into tbltest statement I get an end of statement
> > expected error. Can anyone tell me what I have done wrong here, this is
> > driving me nuts! Thanks~

> > -------------------------------------------
> > Function ConsultantList()
> > Dim myrecordset As Object
> > Dim fullname As String
> > Dim consultantid As Integer

> > Set myrecordset = Application.CurrentDb.OpenRecordset("dbo_RESRES")
> > While Not myrecordset.EOF
> >     fullname = myrecordset.Fields("Name").Value
> >     consultantid = myrecordset.Fields("Id").Value

> >     insert into [tbltest(id,consultantname)]
> >     values [consultantid,fullname]

> > Wend
> > End Function
> > -----------------------------------------------------

> You can't just enter a sql statement in vba like that, you need to tell
> Access to execute it, you also seem to be confusing your square brackets
> and a couple of other things too. Also, try and avoid using reserved
> words such as Name as field names, it can cause all sorts of hard to
> find problems. Try the following modified routine:

> Dim myDB As DAO.Database
> Dim myRecordset As DAO.Recordset
> Dim strFullName As String
> Dim intConsultantID As Integer

> Set myDB = CurrentDb
> Set myRecordset = myDB.OpenRecordset("dbo_RESRES")

> Do Until myRecordset.EOF

> strFullName = myRecordset![Name]
> strConsultantId = myRecordset!Id
> strSQL = "INSERT INTO tblTest(id, consultantname) VALUES(consultantid,
> fullname)

> myDB.Execute strSQL

> myRecordset.MoveNext

> Loop

> Set myRecordset = Nothing
> Set myDB = Nothing

> --
> Hope this helps
> Tony Oakley (MVP)



Mon, 02 Aug 2004 04:26:20 GMT  
 Insert Into statement in a function
disregard, i found the answer and created a reference to the Microsoft DAO
3.6 library.


Quote:
> Is there a reference that needs to be made before dao can be used? I am
> getting a compilation error and code tips do not come up when typing the
> lines containing dao.




> > > I have created a module, a function within the module and entered the
> > > following code within the function. Once i hit the enter key on the
line
> > > containing the insert into tbltest statement I get an end of statement
> > > expected error. Can anyone tell me what I have done wrong here, this
is
> > > driving me nuts! Thanks~

> > > -------------------------------------------
> > > Function ConsultantList()
> > > Dim myrecordset As Object
> > > Dim fullname As String
> > > Dim consultantid As Integer

> > > Set myrecordset = Application.CurrentDb.OpenRecordset("dbo_RESRES")
> > > While Not myrecordset.EOF
> > >     fullname = myrecordset.Fields("Name").Value
> > >     consultantid = myrecordset.Fields("Id").Value

> > >     insert into [tbltest(id,consultantname)]
> > >     values [consultantid,fullname]

> > > Wend
> > > End Function
> > > -----------------------------------------------------

> > You can't just enter a sql statement in vba like that, you need to tell
> > Access to execute it, you also seem to be confusing your square brackets
> > and a couple of other things too. Also, try and avoid using reserved
> > words such as Name as field names, it can cause all sorts of hard to
> > find problems. Try the following modified routine:

> > Dim myDB As DAO.Database
> > Dim myRecordset As DAO.Recordset
> > Dim strFullName As String
> > Dim intConsultantID As Integer

> > Set myDB = CurrentDb
> > Set myRecordset = myDB.OpenRecordset("dbo_RESRES")

> > Do Until myRecordset.EOF

> > strFullName = myRecordset![Name]
> > strConsultantId = myRecordset!Id
> > strSQL = "INSERT INTO tblTest(id, consultantname) VALUES(consultantid,
> > fullname)

> > myDB.Execute strSQL

> > myRecordset.MoveNext

> > Loop

> > Set myRecordset = Nothing
> > Set myDB = Nothing

> > --
> > Hope this helps
> > Tony Oakley (MVP)



Mon, 02 Aug 2004 04:37:05 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. function for string correction for insert statement

2. How? insert data with SQL Insert statement

3. Problem Inserting batch insert statement

4. Insert statement problem - text fields with apostrophes

5. Problem with SQL Statement (INSERT)

6. Need help with syntax of INSERT INTO statement

7. using variables in Insert Into statement

8. Insert statement

9. Execute method w/INSERT INTO Statement

10. Insert Statement

11. Insert Statement

12. insert into statement

 

 
Powered by phpBB® Forum Software