We help IT Professionals succeed at work.

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

akirekab
akirekab asked
on
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

Comment
Watch Question

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Why exactly would you think the record would not be there ?

mx

Author

Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
You might try:

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

assuming TrackingNumber is numberic

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"because I checked the table, and is not there."

How exactly are you 'checking' the table ?

mx
Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:
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

Author

Commented:
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

Author

Commented:
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

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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
CERTIFIED EXPERT
Commented:
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
......

Author

Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Peter .... re "You cannot open a recordset based on a saved parameter query"

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

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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

Author

Commented:
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

Author

Commented:
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
CERTIFIED EXPERT

Commented:
"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.

Author

Commented:
Gerntlemen, peters strsql, line  when put in the mix, is working perfectly.
Ken

Author

Commented:
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.  
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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

Author

Commented:
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
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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
CERTIFIED EXPERT

Commented:
'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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.