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

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

akirekabAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Why exactly would you think the record would not be there ?

mx
0
akirekabAuthor 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
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
You might try:

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

assuming TrackingNumber is numberic

mx
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"because I checked the table, and is not there."

How exactly are you 'checking' the table ?

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
akirekabAuthor 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
0
akirekabAuthor 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

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
peter57rCommented:
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
akirekabAuthor 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
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
akirekabAuthor 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
0
akirekabAuthor 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
0
peter57rCommented:
"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
akirekabAuthor Commented:
Gerntlemen, peters strsql, line  when put in the mix, is working perfectly.
Ken
0
akirekabAuthor 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.  
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
akirekabAuthor 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
0
peter57rCommented:
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
akirekabAuthor 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
0
peter57rCommented:
'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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.