Insert Into Problem

[StuartDwight]

Dear all,
I am having some problems with an Insert Into command. I am trying to insert data into a SQL Server 2000 database using a Access 2002 front end, over a WAN.
My code is as follows:

DoCmd.RunSQL "INSERT INTO CORS_records_notes(ID,author,title,note_date,note)
VALUES(" & strID & "," & """" & strauthor & """" & "," & """" & strtitle & """" & "," & "#" & strnote_date & "#" & "," & """" & strNote & """" & ")"

The problem is the note field is a memo (I have used similar statements with success for date, text and number fields).

I have used the RunSQL for speed, it works using open recordset and AddNew.

If anyone can help that would be great.
Stuart.

 

[SodaJim]

Stuart,

I had a recent post on this very topic but couldn't get any good results...
I'm just attempting to write a new record to a table and refresh the SubForm that is based on the table. I haven't tried your approach of utilizing DoCmd.RunSQL; I was using Execute(variable here)...
I did notice that your Values are coded slightly different and will give this a try...
If I get things to work, I'll post back with my results...

 

[WayneRyan]

Stuart,

You didn't say what your problem is. The insert works?
Is it just too slow? If the memo field gets too large you might
have problems.

Jim,

Why don't you repost and attach a sample of your db?

Wayne

 

[SodaJim]

Repost w/ DB Attachment

Hello Wayne,

Here's a quick breakdown of what I'm envisioning this small project should do...

Main Form: frmOvertime
Create new Overtime Records

Unbound Subform: frmEmployees
Displays Employees' eligibility or ranking by cumlative OT Number of Hours
Subform: frmEmployeeOvertimes
I would like to record each time an Employee is Called and not contacted, Denied the Overtime or Worked the Overtime.

As it is now, I have removed the code that attempted to add a new record in tblEmployeeOvertimes for the Employee displayed.

I created this DB strictly as an example, the original has a few more error checks, queries and reports which have been removed for compactness.

To sum things up...
What is the most efficient way to create new records in tblEmployeeOvertimes associated by EmployeeID to retain the One --> Many relationship...?

Many Thanks!!!

 

[WayneRyan]

Jim,

I don't really understand your table structure.

I made a crude new form, but it will show you
some of the things that you can do to "hook"
your forms together.

Notice the default values on the subforms and
the data sources for the subforms.

Wayne

 

[SodaJim]

Hello Wayne,

Thanks for your reply; however, as you are confused by my table structure, I'm confused by your forms layout...

Just for clarification and anyone else reading, I need to create a system that can track Overtime occurances, hench the table "tblOvertime". The list of available personnel is in table "tblEmployees". The list needs to be in ascending order by the Total Number of Hours each employee has been accumulating. The last table "tblEmployeeOvertimes" is utilized to track each time a person has been called, denied or worked an Overtime occurance. Tables "tblEmployees" & "tblEmployeeOvertimes" have a one to many relationsship based on EmployeeID which is unique for every employee. This is because any one employee can have mutliple EmployeeOvertimes.

If Stuart is still watching this thread, I would like to see one of his procedures for inserting records utilizing RunSQL.

 

[WayneRyan]

Jim,

Friday was a day off. I just slapped those forms together to
show that if you relate them, you don't have to programatically
insert records.

I think that the only trouble was that you chose O'Brien as a
test case. The single-quote makes it hard for SQL to parse
the insert statement.

Stuart's first post on this thread showed how to use the
double-quotes to specify a new delimiter.

Wayne

 

[SodaJim]

I'm Back...

Hello Wayne,

Thanks again for the reply!

As it is right now I'm receiving an error for the line db.Execute (mySQL) in the following code:
'==========================================
'Adding new record in tblPersonnalOvertime
mySQL = "Insert into tblPersonnelOvertime (EmployeeID, OT_CalledDate, OT_Hours) " & _
"Values ('" & Me.EmployeeID & "', '" & otDate & "', '" & Forms!frmOvertime![txtOT_Hours] & "')"

db.Execute (mySQL)
DoCmd.Save
Me.Requery
'==========================================

The error is:
Runtime Error '91'
Object variable or With block variable not set

mySQL is declared as string

I'm very sorry if this is beating a dead horse into the ground but if I get over this hurdle, all is well for the project...

TIA,
Jim

 

[SodaJim]

Gettin' Close...

Hello Again,

I utilized the following code was able to add a new record like I want; however, I'm having difficulty setting a "Yes/No" field to True or Yes...

The functioning code:
'==========================================
'Adding new record in tblEmployeeOvertimes

mySQL = "Insert into tblEmployeeOvertimes (EmployeeID, OT_CalledDate, OT_Hours) " & _
"Values ('" & Me.EmployeeID & "', '" & otDate & "', '" & Forms!frmOvertime![txtOT_Hours] & "')"

DBEngine(0)(0).Execute mySQL, dbFailOnError

'==========================================

And the following generates Runtime Error '3464'
Data type mismatch in criteria expression:
'==========================================
'Adding new record in tblEmployeeOvertimes
otCalled = True
mySQL = "Insert into tblEmployeeOvertimes (EmployeeID, OT_CalledDate, OT_NoContact, OT_Hours) " & _
"Values ('" & Me.EmployeeID & "', '" & otDate & "', '" & otCalled & "', '" & Forms!frmOvertime![txtOT_Hours] & "')"

DBEngine(0)(0).Execute mySQL, dbFailOnError

'==========================================

Any direction appreciated,
Jim

 

[WayneRyan]

Jim,

Not all fields are delimited by the single-quote ...

Numeric - nothing
Text - single-quotes
Dates - #

I don't have your database, but ...

mySQL = "Insert into tblEmployeeOvertimes (EmployeeID, OT_CalledDate, OT_Hours) " & _
"Values (" & Me.EmployeeID & ", #" & otDate & "#, '" & Forms!frmOvertime![txtOT_Hours] & "')"

That is inserting Number, Date, Text

hth,
Wayne

 

[SodaJim]

Hello Again...

Wayne,

Thanks again!

How can I insert a checkbox to be checked or set to Yes/True...?
The sample code functions and inserts a record but I can't seem to include setting the checkbox to True...
Any ideas... My previous post has the code that includes the checkbox variable and the error it creates... I set a variable as True which I declared as Boolean, but this doesn't work properly...

Any ideas...

 

[WayneRyan]

Jim,

True = -1
False = 0

True
sql = "Insert into MyTable (MyFlag) Values (-1);"

False
sql = "Insert into MyTable (MyFlag) Values (0);"

Just treat it as a number no single-quotes or #s needed.

Wayne

 

[SodaJim]

The Solution ! ! !

Hey Wayne,

That was it!
I included the (-1) and it checked the checkbox in the new record.

Many Thanks again for your time and patience!