INSERT SQL STATEMENT 1

[ParyGoal]

Can anyone see a problem with this insert statement please?
strSQL = "INSERT INTO tblPersonalProgressReport(MngWorkWith,Date, Manager "
'strSQL = strSQL & "MngWorkWith, Date, Manager, "
strSQL = strSQL & "Plant, Week, Department, "
strSQL = strSQL & "WhatDidyouDo, Safety, Quality, "
strSQL = strSQL & "Process, ProblemsSolutions, Improvements, "
strSQL = strSQL & "NextWeek, ManagerComments, MngTrainee, DateStamp) "
strSQL = strSQL & "VALUES ("
strSQL = strSQL & "'" & Me.cboSponsor & "', "
strSQL = strSQL & "'" & Me.txtDate & "', "
strSQL = strSQL & Me.cboManager & ", "
strSQL = strSQL & Me.cboPlant & ", "
strSQL = strSQL & Me.txtWeek & ", "
strSQL = strSQL & "'" & Me.txtDepartment & "', "
strSQL = strSQL & "'" & Me.txtTask & "', "
strSQL = strSQL & "'" & Me.txtSafety & "', "
strSQL = strSQL & "'" & Me.txtQuality & "', "
strSQL = strSQL & "'" & Me.txtProcess & "', "
strSQL = strSQL & "'" & Me.txtProblemsSolutions & "', "
strSQL = strSQL & "'" & Me.txtImprovements & "', "
strSQL = strSQL & "'" & Me.txtNextWeek & "', "
strSQL = strSQL & "'" & Me.txtManagerComments & "', "
strSQL = strSQL & Me.txtMngTrainee & ", "
strSQL = strSQL & "'" & Me.txtDateStamp & "')"

I am getting an error saying that a field, control, or property could not be found by access. Error 91. As soon as it reaches the second last line of the sql statement,an error is generated

I am using ADO

Thank you

ParyGoal

 

[Remou]

You have a field name with spaces and no square brackets, similarly, an unbracketed reserved word and your date field seems to be defined as text.

 

[ParyGoal]

Thank you Remou for the replay.
I don't think I have a field name with spaces.
I do have two date fields defined. I do i write a date field and number field in sql statement?

Thank you

ParyGoal

 

[Remou]

If you look again at your SQL statement, you will see that you have "Manager Plant", no comma, once you eliminated the breaks and the commented line.

[tt]"INSERT INTO tblPersonalProgressReport(MngWorkWith,Date, Manager "
strSQL = strSQL & "Plant, Week, Department, "[/tt]

Date is a reserved word, and ADO will not swallow it without braces, that is, [Date].

The delimiter for a date field is the hash mark, #.

 

[ParyGoal]

Thank you Remou. You were right. I missed it.
One last question if you don;t mind. How do I enable disable the Save and Add Button accordingly. To prevent user from clicking the save button twice.
And Everytime I add a record I want the record counter in the custom navigation bar to update?

Thank you

ParyGoal

 

[belovedcej]

To enable or disable any control, just do Me.ControlName.Enabled = True (or False)

I like to have two or three routines in a form module called "addMode, ReadMode, EditMode", etc. I put all the logic of disabling/enabling controls there, and then in my various events, I just put Call addMode or whatever. Makes it much nicer.

as for the record counter, if the form is bound, it should update automatically. If not, you may need to requery your recordset to make the counter accurate.

 

[ParyGoal]

Thank you so much for you help belovedcej. I appreciate it very much. Do you think you could share an example of how you use the three routines when working with recordset?
Also all the controls on the form are unbound including the form itself

Thank you

ParyGoal

 

[belovedcej]

I typically use bound forms when dealing with an Access back-end. It's using Access's strength instead of fighting it's weaknesses. So my routines are simple. Assuming I have a save button, an add button, and an edit mode button, my routines might be something like the following: (I just add them to the end of the form module)

Private Sub readMode()
Me.AllowAdditions = False
Me.AllowEdits = False
Me.AllowDeletions = False

Me.cmdSave.Enabled = False
Me.cmdAdd.Enabled = True
Me.cmdEdit.Enabled = True
End Sub

Private Sub addMode()
Me.AllowAdditions = True
Me.AllowEdits = False
Me.AllowDeletions = False

Me.DataEntry = True 
'(or some other code for inserting a new record)
Me.cmdSave.Enabled = True
Me.cmdAdd.Enabled = False
Me.cmdEdit.Enabled = False
End Sub

Private Sub editMode()
Me.AllowEdits = True
Me.AllowAdditions = False
Me.AllowDeletions = False

Me.cmdSave.Enabled = True
Me.cmdAdd.Enabled = False
Me.cmdEdit.Enabled = False
End Sub

(Mine are much more elaborate than that simply because there is a lot going on, but that should give you the idea.)

Then on the onClick event of the Add button I would put:

Call addMode

On the onClick event of the Save button I would put:

Call readMode
'(and some other code which which triggers a beforeUpdate  
'event - perhaps a requery of my recordsource.)

On the onClick event of the Edit buttong I would put:

Call editMode

I do this because I may change modes using more than just those three buttons and don't want to retype everything. I use the beforeUpdate event of the form to catch changes and make verifications of the data.

When using unbound forms (which I do when using SQL Server for the back-end) I have similar "modes"; however, it is not that simple. I will use a bound form for the readMode (allowing me to continue using Access's Find and filter features) and then when I go to edit or add mode I actually open a new, unbound form and copy any relevant data from the current record to the newly opened form. Because I often will open an add or edit version of a form from multiple places, instead of storing my three modes as private routines in the form module, I create a new module and put them in there, declaring them as Public.

As for your record counter, I can't be sure how you are updating it with an unbound form, but I'm assuming you created the control yourself. In that case, perhaps something like this would work:

Private Sub setCounter()
Dim rst As New ADODB.Recordset  (or DAO.Recordset, whichever you are using.)

Set rst = currentProject.Connection.Execute("Select Count(your primary key here) As myCount From yourTable")
Me.RecordCounterControl = rst.Fields("myCount")
Set rst = Nothing
End Sub

Then just add the following line after your insert statement:

Call setCounter

I hope that helps.

 

[ParyGoal]

Thank you belovedcej so much for you help and for sharing your methods with me. You deserve a star.
I appreciate it very much

ParyGoal

 

[ParyGoal]

I followed belovedcej advise and the code works perfectly except the record counter i built does refresh.
I am not sure how I can keep it syncronized when records are added and deleted?
Any ideas?

ParyGoal

 

[belovedcej]

Could you post the code you are currently using to update the record counter? What event are you using to trigger it?

 

[ParyGoal]

Hi belovedcej.
I got it to work but I am not sure it's right way.

This code in the Form_Current Event

With rsTimeLine
.MoveFirst
'.MoveLast
lngCount = .RecordCount
End With

'Show the result of the record count in the text box (txtRecordNo)

Me.txtPos = "Record " & rsTimeLine.AbsolutePosition + 1 & " of " & lngCount



Right after the insert statement. I issue a call to the SetCounter routine.

Private Sub SetCounter()
Dim rst As DAO.Recordset
Dim D As DAO.Database
Dim strSql As String
Set D = CurrentDb

strSql = "Select Count(TimeLineID) As RCCount From tblTimeLine where ManagerTraineeID= " & Me.txtManagerTrainee



Set rst = D.OpenRecordset(strSql, DB_OPEN_DYNASET)



lngCount = rst.Fields("RCCount")
rsTimeLine.Requery ' Requery the main recordest

Me.txtPos = "Record " & rsTimeLine.AbsolutePosition + 1 & " of " & lngCount ' update the navigation bar
Set rst = Nothing
End Sub


THat's how I did. I am not sure this is best way to do it.

Thank you

ParyGoal

 

[belovedcej]

If it works, I wouldn't worry about it. If you let it be and come back to it a few months later, you will see how improve it - and by then your improvements would be different than they will be right now, even with advise from others.

All I see is a little bit of redundency, which makes maintainence harder in the long run. But if it works, just wait. After you have moved on, you can come back and see how perhaps this routine is like several others on other forms, and then you might be able to eliminate redundency in multiple places instead of just one.

For example, I set the recordset of forms, reports, combo boxes and list boxes in the code, not in the properties. (so my front end is reusable for different back-ends.) LOTS of repetitive code because I don't set it to a table, but to a stored procedure (I work with SQL Server.). But I couldn't figure out how to improve it, and to meet my deadlines I just finished the work. Now that I am in a maintainence mode, I have found a little technique that works wonders and am revising as I have the time.

I have a public routine:

Public Sub setRst(Object As Object, SourceNoPrefix As String, Optional Parameter As Long)

Dim rst As New ADODB.Recordset

If Not (Parameter = 0) Then
    Set rst = CurrentProject.Connection.Execute(g_tablePrefix & SourceNoPrefix & "(" & Parameter & ")")
Else
    Set rst = CurrentProject.Connection.Execute(g_tablePrefix & SourceNoPrefix)
End If

Set Object.Recordset = rst
Set rst = Nothing

End Sub

I can then call this over and over again, doing nothing more than feeding in the parameters of the routine. (If my stored procedure has more parameters or one that isn't a long, I can still use it, but that's another story. )

You may find later than you can adapt your counter routine to take a form parameter and then use one routine on all your screens. You may also find that you never need to set the counter's value anyway except through the "setCounter" routine. Just call that routine in your onCurrent event or after your deletions or insertions occur. That might eliminate a few lines of code. (Likely not a lot, though.)

Hope that helps - and glad you figured it out. It's always great when you are "improving" instead of just "making it work."

 

[ParyGoal]

Thank you belovedcej for the replay, and for your very helpful insight. I will definitely use the function your provided.
I am also thinking of upgrading the back-end to SQL Server and the have the queries converted to stored procedures. Do you see any performance advantages in doing so? Will it make a difference to keep the front-end in access and the back-end in SQL Server?
I have a form that has several nested unbound sub-forms. I mean at least 40. That's where I noticed the poor performances. I did try to optimize it by loading the fields dynamically, but it did not seem to do the trick.
It can take up to 2 minutes to load on the network.
Will that improve if I use a SQL Server Database instead? Or there are other ways to optimize it?

Thank you

ParyGoal

 

[belovedcej]

I would encourage you to consider an Access project , which is a way of interacting with SQL Server without linked tables. It uses OLE Objects instead of the JET server to interact with the server. Check out this article:

http://office.microsoft.com/en-us/access/CH063667501033.aspx

If you went to sql server and continued using linked tables, you might not see much speed increase. But with an Access project, you would definitely see improvements.

However, it is not as simple as just moving the tables and a few tweaks. Depending on how security is handled at your workplace, there may be several changes that need to take place, so be sure to do your research on it first.

Access Jet SQL is similar to TSQL, but not identical, so you will learn a few things as you move queries over. Most important thing is to study the difference between Functions, Stored Procedures, and Views.

An Access query that simply returns a recordset would probably convert into a view. An access query that returns only one value would likely be very good as a function. A query that takes parameters would have to be either a function or a stored procedure. In some ways the three are interchangeable, but there are distinct differences.

For example: if you use a query as a recordsource for another query, you will need that query to be a view. You cannot use a stored procedure as a source for another query. Also, if you want to filter down a query from your VBA, you would also have to use a view. On the other hand, that's probably not a good idea anyway, for performance reasons. Stored Procs are precompiled and run much faster than views. In our shop we use only stored procs and functions. Also, for security reasons, no user has access to a table at all - only stored procs. But that's another issue.

You'll want to google these and read up on them a bit. The main point is that although I think it is the best way to go, it is not something you can do in a week. Depending on how you choose to structure things (bound/unbound/stored procs/views, etc), you may not change your code much at all or you may extensively increase it.

Also check out this link:

http://www.sql-server-performance.com/access.asp

As for other ways of optimizing without converting, look at these sources for ideas:

http://support.microsoft.com/kb/275085

http://www.blueclaw-db.com/speed_up_access_forms.htm

http://www.blueclaw-db.com/access_consultant_rapid.htm

http://www.granite.ab.ca/access/splitapp/performance.htm

http://www.microsoft.com/office/previous/xp/columns/column05.asp

http://www.dbforums.com/t1076235.html

Also, if you aren't already aware of it, you can google Access MVP and find a list of excellent Access resources - those who are MVPs are among the best when it comes to sharing a wealth of resources.

Good luck!

 

[ParyGoal]

I am trying to update an existing records using sql statement.
Now everytime one of the values is null an error is generated. I should be able to edit which ever value I want? right?
When editing a record, if all the values on the form are filled, it saves the record else an error is generated.
here is my update sql statement I am using.

Private Sub UpdateProgressReport()
'**** Save Changes ****
Dim db As Database
Dim rsTimeLine As Recordset
Set db = CurrentDb
Dim strSql As String



' 'create SQL to update the existing record in the
'database with the values on the form
strSql = "UPDATE tblPersonalProgressReport SET "
strSql = strSql & "MngWorkWith = '" & CheckString(Me.cboSponsor) & "', "
strSql = strSql & "[dtDate] = #" & Me.txtDate & "#, "
strSql = strSql & "Manager = " & Me.cboManager & ", "
strSql = strSql & "Plant = " & Me.cboPlant & ", "
strSql = strSql & "Week = " & Me.txtWeek & ", "
strSql = strSql & "Department = '" & CheckString(Me.txtDepartment) & "', "
strSql = strSql & "WhatDidyouDo = '" & CheckString(Me.txtTask) & "', "
strSql = strSql & "Safety = '" & CheckString(Me.txtSafety) & "', "
strSql = strSql & "Quality = '" & CheckString(Me.txtQuality) & "', "
strSql = strSql & "Process = '" & CheckString(Me.txtProcess) & "', "
strSql = strSql & "ProblemsSolutions = '" & CheckString(Me.txtProblemsSolutions) & "', "
strSql = strSql & "Improvements = '" & CheckString(Me.txtImprovements) & "', "
strSql = strSql & "NextWeek= '" & CheckString(Me.txtNextWeek) & "', "
strSql = strSql & "ManagerComments= '" & CheckString(Me.txtManagerComments) & "', "
strSql = strSql & "DateStamp= #" & Me.txtDateStamp & "# "
strSql = strSql & "WHERE ID = " & Me.txtProgressID




db.Execute strSql
db.Recordsets.Refresh





MsgBox "Changes to your record have been updated."

'**** clear the controls to add more customers ****
'Call ClearControls



'Call editMode
End Sub

Thank you for you help

Parygoal

 

[belovedcej]

Have you tried debugging and stepping through to find the line the error is on?

My guess is this - if a user has not filled in a field, you are getting an error. Or if there are default values and they delete the value and don't fill anything in, you get an error.

The problem is that you are trying to read in a null value, which is not possible. Before building your strSQL variable, have a variable for each value. Then do an if statement that sets that variable to a default value if it is null. For example:

Dim dtDate As Date
dtDate = IIF(IsNull(Me.txtDate), "1/1/1900", Me.txtDate)
strSql = strSql & "[dtDate] = #" & dtDate & "#,

 

[ParyGoal]

Thank you belovedceij for you help. I followed your advice. Everything seem to work perfectly!!!

One question if I may; when using an ADP file, can sharing among multiple users? Or do you the best way, to install a client (ADP) on each user's computer?

Thank you

ParyGoal

 

[ParyGoal]

Good Morning belovedcej

Something strange is happening with sql statement. The code is breaking on dtDate = IIF(IsNull(Me.txtDate), "1/1/1900", Me.txtDate) when editing a existing record. It's returning a type mistmach error. I did work yesterday though. I just don't get it at all.

ParyGoal

 

[ParyGoal]

Now it is working. I am not sure if the changes i made took care of it or not.
I think I was breaking on the second line which is a string variable.
But for performences reasons. I check the len of the variable in the iif statement instead of using IsNull function.
So I changed it back to using Isnull instead.
I tell ya, access can be picky.