[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Help in retrieving and updating DAO.recordset as dynaset with saved query

Posted on 2008-01-26
23
Medium Priority
?
654 Views
Last Modified: 2010-04-21
I have to be sure a table, tblServiceSolution , is updated with a new record each time a new work item is entered and saved on main work form if it doesnt currently exist! .  I thought I would as part of the save event when new item is entered, open a DAO.recordset, as dynaset, based on saved query and test to see if the input was there, and if not add the record.

After clicking on the save button I got error,  Too few parameters, Expected 1.
Note 1. The query SQL was created by Access.
         2. I checked the  [Forms]![WorkEntryForm]![Text33])) in the immediate window, and it returned the correct input.  
         3. I would have thought that was the only parameter I would need.

I thought this save area, indicating a new record, would be the best place to test the new input against the needed table.

I will stop here and  know I cant really redesign the original items at this point just want to get the general idea of opening the recordset as a dynaset with this criterion to work.
Thank you for any help.
I am in Windows XP, and this is MS Access 2002.
Ken

(CODE)
SELECT tblServiceSolution.*
FROM tblServiceSolution
WHERE (((tblServiceSolution.TrackingNumber)=[Forms]![WorkEntryForm]![Text33]));
(/CODE)
 
And then the recordset creation
(CODE)
Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click
    Dim db As DAO.Database
    Dim rstSolution As DAO.Recordset
    Set db = CurrentDb()
    Set rstSolution = db.OpenRecordset("qryBillingStatus", dbOpenDynaset)
With rstSolution
  If Not rstSolution.BOF And Not rstSolution.EOF Then
         'continue
   Else
            .AddNew
                  !TrackingNumber = Me.Text33
                  !DateStarted = Me.EnteredDate
            .Update
   End If
    
End With
(/CODE)

Open in new window

0
Comment
Question by:akirekab
  • 10
  • 9
  • 4
23 Comments
 
LVL 75
ID: 20752904
Why exactly would you think the record would not be there ?

mx
0
 

Author Comment

by:akirekab
ID: 20752921
because I checked the table, and is not there.
If all worked, a new entry would be in the tblServiceSolution.  And although the form is flowing thru to the save and updating the original table properly, because of this error, about parameter, apparently the flow is jumping over the inserts in my if statement.
Thanks for asking
Ken
0
 
LVL 75
ID: 20752942
You might try:

WHERE tblServiceSolution.TrackingNumber=CLng([Forms]![WorkEntryForm]![Text33])

assuming TrackingNumber is numberic

mx
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 75
ID: 20752948
Typically, that error occurs because Access of a spelling error, ie ... Access cannot find what you are referring to.  Is   WorkEntryForm   the exact name of the form?

mx
0
 
LVL 75
ID: 20752951
"because I checked the table, and is not there."

How exactly are you 'checking' the table ?

mx
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1000 total points
ID: 20752956
OK ... I'm not really seeing where you are Saving the record.  Try this:


Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click
    Me.Dirty = False  ' *********************** ADD THIS

    Dim db As DAO.Database
    Dim rstSolution As DAO.Recordset
    Set db = CurrentDb()
    Set rstSolution = db.OpenRecordset("qryBillingStatus", dbOpenDynaset)
With rstSolution
  If Not rstSolution.BOF And Not rstSolution.EOF Then
         'continue
   Else
            .AddNew
                  !TrackingNumber = Me.Text33
                  !DateStarted = Me.EnteredDate
            .Update
   End If
   
End With
0
 

Author Comment

by:akirekab
ID: 20752962
databaseMX
it is not numeric.
and I checked the spelling just now, and it is exactly that.
I also tested that phrase in immediate window right after error and it returns correct info.
Thanks for asking
Ken
0
 

Author Comment

by:akirekab
ID: 20752972
First of all I added the whole sub procedure here so you could see the save line that is in and it is working fine.  
So would I add the me.dirty still?
Ken
Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click
    Dim db As DAO.Database
    Dim rstSolution As DAO.Recordset
    Set db = CurrentDb()
 
    Set rstSolution = db.OpenRecordset("qryBillingStatus", dbOpenDynaset)
With rstSolution
  If Not rstSolution.BOF And Not rstSolution.EOF Then
         'continue
   Else
            .AddNew
                  !TrackingNumber = Me.Text33
                  !DateStarted = Me.EnteredDate
            .Update
   End If
    
End With
 
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 
Exit_cmdSave_Click:
    Exit Sub
 
Err_cmdSave_Click:
    MsgBox Err.Description
    Resume Exit_cmdSave_Click
    
End Sub

Open in new window

0
 
LVL 75
ID: 20752979
Is this a Bound form?

Sorry, I'm a bit confused.  Your code is not 'saving' a record per se, it's Adding a record ... IF ... the BOF and EOF  fail.  But ... if you were to add the Me.Dirty = False (assuming this is a Bound form) ... THAT ... will Save the record ... and no testing would be necessary.

mx
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 1000 total points
ID: 20752981
You cannot open a recordset based on a saved parameter query without taking actions to evaluate the parameter.

You need to base the recordset on a querydef object as follows:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As QueryDef
Dim prm as dao.parameter

Set db = currentdb
Set qdf = db.QueryDefs("qryBillingStatus")

for each prm in qdf.Parameters
prm.value = Eval(prm.name)
next
Set rst = qdf.OpenRecordset()
...etc

An alternative is not to use a saved query but just a query string

dim strssql as string
strsql = "SELECT tblServiceSolution.* FROM tblServiceSolution "
strsql = strsql & " WHERE tblServiceSolution.TrackingNumber='" & [Forms]![WorkEntryForm]![Text33] & "'"
.....
Set rstSolution = db.OpenRecordset(strsql, dbOpenDynaset
......
0
 

Author Comment

by:akirekab
ID: 20752985
The entry is on the form which is bound to the Work table,  the tblServiceSolution is a different table that I want to enter a record or part of one, each time a new record is introduced on this form, so I thought prior to or while doing the save I could retrieve the recordset of the tblServiceSolution, and if one existed there would be no need to add, but if not, I would add the pertinent data at that time.
To further clarify, the table for the insert is not bound to the active form.
Ken
0
 
LVL 75
ID: 20752990
Peter .... re "You cannot open a recordset based on a saved parameter query"

Are you sure?   I've been doing that for years.

mx
0
 
LVL 75
ID: 20753016
Sooo ... you just trying to see if the tblSS already has an entry related to the Work table?  When and how do records get added to tblSS (abbreviated) ...?

mx
0
 

Author Comment

by:akirekab
ID: 20753021
My opinion which is way lower class than either of you, is that my code to open a record set with a saved query is fine.  But of course both of the alternatives listed by Peter are to.
Ken
I will test with all and be back
0
 

Author Comment

by:akirekab
ID: 20753031
DatabaseMX
Normally a row would be adde by a returning service man on a form for that.  But they are too lazy to do it in a timely manner, so I was asked to be sure the table was started as soon as a work item was started, because a lot of other things depend on that.
Ken
0
 
LVL 77

Expert Comment

by:peter57r
ID: 20753047
"Peter .... re "You cannot open a recordset based on a saved parameter query"

Are you sure?   I've been doing that for years."

I am sure.
0
 

Author Comment

by:akirekab
ID: 20753054
Gerntlemen, peters strsql, line  when put in the mix, is working perfectly.
Ken
0
 

Author Closing Comment

by:akirekab
ID: 31425365
I used several sources to originally set this up and they all said the saved qry was fine, as did all my text books.  But the fact is it didnt produce, and using the strSQL, (which I had tried before, but did not code properly), is producing an entry on the table and also properly skipping the entry when modifications are made after it is on the table.
Thank you both so much.  I would like to award 250 points each , but I am not sure how to do that.  
0
 
LVL 75
ID: 20753090
Well, Peter is right and I am wrong. So, the pts should go to Peter.    

However, I have a library function I've used for years which will open a recordset on a table or *saved* query ... wherein the query may or may not reference a text box on a from (for example to get a date for a date range) ... and it clearly works with no problem - I have it in 100's of apps.  But I just emulated your exact code, and I do get the parameter error.  Thus ... I am REALLY confused at the moment.

mx

0
 

Author Comment

by:akirekab
ID: 20753114
Hi DatabaseMX, and Peter
Well you are probably right, but you took a lot of time to look at this and got me thinking, so I am going with my original point distribution, I am only confused by Peters first line, which says
"You cannot open a recordset based on a saved parameter query without taking actions to evaluate the parameter."
I think the issue must be with the words, taking actions to evaluate, It will be a little bit before I understand exactly why, but fact is, it did perform as needed .
Ken
0
 
LVL 77

Expert Comment

by:peter57r
ID: 20757514
THis is my take on the issue...

All VBA does to run a query is to take the sql string is pass it to the Jet database engine for actioning.   Jet has no knowledge of Access forms - it is just a database engine.  SO the query string it receives must already be complete and any lookups to forms must already be done.  WHen you run a query in the database window, the Access UI takes care of the lookups.  But if the query is in VBA, VBA doesn't do that unless it is told to. So  you have to force that to happen and include code to do the lookups.  The two methods I gave in my post are the most common ways of getting the form lookups evaluated but there are others.

0
 

Author Comment

by:akirekab
ID: 20759655
Hi Peter,
I understand pretty much now.  The fact is , even though I had given the qryname in quotes, it had not actually been opened or ran, so it hadn't resolved the tracking number yet.

Now if the form was open which it is, and the query was run, say DoCmd.openQuery.... prior  to the open recordset in its original form, it probably would have worked.  I will test that, but as it turns out,
My main issue was being inexperienced in properly coding the variable in a string, which you did so very well, and believe it or not, that is the exact way I tried to do it in the first place., just to ignorant to get the quotes, and apostrophes where they needed to be, so this is very good lesson for me.

Thanks again for all you help, I appreciated it.

Ken
0
 
LVL 77

Expert Comment

by:peter57r
ID: 20767845
'Now if the form was open which it is, and the query was run, say DoCmd.openQuery.... prior  to the open recordset in its original form, it probably would have worked. '

No. I'm afraid not.  Each run of the query is a new instance in memory - opening a form based on the same query does not help if you then open the same query as a recordset.

To use the form recordset you can use the recordsetclone  property but if you want to add records then you would probably want to add them directly to the table rather than via another object.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Implementing simple internal controls in the Microsoft Access application.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question