August 12, 2008 at 3:27 am
This is the code i have written so far, however when running it i get an error saying no object selected. The error relates to rstAdd.Fields as this is a table and it's returning a null value, meaning the record isn't in the table so id want it to go to the Else statement and add one...
Not sure how to stop the Debug Error and just move next 🙁
Dim rst As Recordset
Dim rstAdd As Recordset
Set rst = CurrentDb.OpenRecordset("qryMemberConsultation")
Set rstAdd = CurrentDb.OpenRecordset("Consultation")
With rst
Do
With rstAdd
If rst.Fields(0) = .Fields(1) And rst.Fields(1) = .Fields(2) Then
.MoveNext
MsgBox "Record Skipped", , strTitle
Else
.AddNew
.Fields(0) = GetSeq(27)
.Fields(1) = rst.Fields(0)
.Fields(2) = rst.Fields(1)
.Update
End If
End With
.MoveNext
Loop Until .EOF = True
End With
Basically i want to add the records from "rst" to "rstAdd". rst is a Query as you can see and rstAdd is the table i want to add them from. The query is based on multiple queries which will return only 2 Fields. MemberID and ConsultationID. This is the list of Members and Consultations they are interested in.
If i was using SQL i could use a NOT IN (Select * FROM Consulatation WHERE......) but i not sure how to do that in Access so i thought id try the above way!
Dont think it likes comparing rst.Fields to rstAdd.Fields so it fails 🙁 The MsgBox is there just while i was debugging it...
GetSeq is a Function that returns the next value in a Sequence for Primary Key.
August 12, 2008 at 4:50 am
it looks like you're comparing rs to only one row in rsadd and not checking the whole table to see if it's there. you're checking one row, if it doesn't match, add. is that what you want?
i'd play with the query designer a bit more. you could link/relate all the fields you want to compare and check your results with different joins. there's also the 'Find Unmatched Query Wizard'.
August 12, 2008 at 6:17 am
I was hoping to be able to do it in code rather than use more queries, to get to the final query you see in the uses 8 seperate ones so more would just confuse it!
So for Each record in the query, i need to compare it to maybe a new recordset with all values from the table, and if they dont match add the record, if not move to the next record and repeat.
It sounds easy but its just not sitting right :/
Very annoying!
August 12, 2008 at 6:51 am
Hi, I have a Doubt.
Could it be that the error msg be coming from the function "GetSeq()". How are you declaring the function?
As for using the NOT IN (Select * FROM Consulatation WHERE......) works fine in Access.
Also as Wazz says, you are only comparing just one record from de Add table, you'll need to loop the whole table.
As for the Null value returned from your recordset or table, ms access has a neat function called "NZ" which tells the db engine to return a given value when the value is null. If my memory is not failing the function works like this NZ(fieldtovalidate, valuetoreturnwhennull). Keep in mind that the fieldtovalidate value can be a field, a formula a function....
Hope this helps.
Hugo
August 12, 2008 at 7:37 am
Hey
Thanks for the information, the function GetSeq() works fine. Is declared in a module and ive no problems with that side as when it adds the record (wrongly) it adds the PK fine.
I think that's my problem, when comparing it only to one record and i need to cycle through each record in the table to compare it. This seems to be the problem i cant get past.
Kind of like, for each record in the query, go down all the records in the table and compare the two values. If they match, get the next record from the Query and repeat. If they dont match, add the record to the table and then move onto the next record from the query.
Sounds simple when writing it.
I didnt know about that NZ function, and nor that NOT IN works. It might be easier if i can use NOT IN (SELECT Field1, Field 2 FROM Table WHERE rstField1 = Field 1 AND rstField2 = Field2)
Have to give it some more thought, but sounds logical.
For reference this is the GetSeq Function:
Public Function GetSeq(intCounter As Integer) As Integer
Dim rstSeq As DAO.Recordset
Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs![qryCounter]
With qdf
qdf.Parameters.Refresh
.Parameters(0) = intCounter
End With
Set rstSeq = qdf.OpenRecordset()
With rstSeq
GetSeq = .Fields(0)
.Edit
.Fields(0) = .Fields(0) + 1
.Update
.Close
End With
End Function
August 12, 2008 at 9:09 am
i added 3 lines (commented-out). looks pretty close i think ...
With rst
Do
With rstAdd
'do while not rsadd.eof
If rst.Fields(0) = .Fields(1) And rst.Fields(1) = .Fields(2) Then
.MoveNext
MsgBox "Record Skipped", , strTitle
Else
.AddNew
.Fields(0) = GetSeq(27)
.Fields(1) = rst.Fields(0)
.Fields(2) = rst.Fields(1)
.Update
'exit do
End If
'loop
End With
.MoveNext
Loop Until .EOF = True
End With
August 13, 2008 at 1:11 am
Hey
Thanks for that, you are alot closer than i am that's for sure!
I ran it and it tells me it skips 5 records, which is correct because those 5 are already in the table so thats what i want it to do. However, even tho it tells me that it skips them in seems to go onto the Else anyway and adds them 🙁
There are 6 records in the Query, 5 of those exist in the table so it should add only one.
Really cant figure why it is going to the Else statement after the IF has been satisfied.
August 13, 2008 at 1:16 am
I can see what it's doing wrong, the code is right but the thinking is wrong it seems.
It's getting the record from rst, so the first record. It then opens the rstAdd and compares it to the first record. It finds a Match so produces a MsgBox. This part of it is correct.
However, the .MoveNext moves to the next record in rstAdd but keeps the same record in rst, thus they dont match so it adds the record.
Somehow when it finds a match in rstAdd it needs to move to the next record in rst. How we do that when it needs to search possibly hundreds/thousands of records in the rstAdd, is beyond me!
It has to compare each record in rst to every possible record in rstAdd if it doesnt find a match add it, if it does move onto the next record in rst and compare it again with all records in rstAdd, rinse and repeat.
August 13, 2008 at 5:22 pm
I would suggest creating a query using NOT IN - especially if there are large volumes of data. This will allow you to produce a unique set of values to be inserted which won't already exist in the table and don't need to be checked in a code loop.
An iterative loop in the Access code will be fine for small volumes of data, but may be too slow with larger volumes. From a mainenance perspective, it would also be easier to modify the query than the code.
Chris
August 14, 2008 at 1:20 am
You make a fair point, at the moment my test data is only around 50 records or so all in. When the database goes live it will be open to the entire population of my town, so could grow to over 250k person records with unlimited related records!
Query could be the best way, will give it a go. Cant say ive ever tried a NOT IN statement in Access so should be fun 🙂
Thanks for all your help and advice
Scott
August 14, 2008 at 7:36 am
Another item that may add to your code but might make it a lot clearer...
I never use anything but the . commands within a With statement. I see that you do a lot of things there that aren't really related to the With functionality, and I would avoid that kind of code primarily so that there's absolutely no confusion about what you're trying to accomplish.
As an example, just taking your original code, I would have coded it this way:
Dim rst As Recordset
Dim rstAdd As Recordset
Set rst = CurrentDb.OpenRecordset("qryMemberConsultation")
Set rstAdd = CurrentDb.OpenRecordset("Consultation")
Do
If rst.Fields(0) = rstAdd.Fields(1) And rst.Fields(1) = rstAdd.Fields(2) Then
rstAdd.MoveNext
MsgBox "Record Skipped", , strTitle
Else
With rstAdd
.AddNew
.Fields(0) = GetSeq(27)
.Fields(1) = rst.Fields(0)
.Fields(2) = rst.Fields(1)
.Update
End With
End If
rst.MoveNext
Loop Until rst.EOF = True
There just isn't much benefit to a With statement unless you're going to have at least two successive lines with a . command of some kind, so it really only makes sense to use them exclusively within the code section where that's all you have. This also avoids the potential for problems in nested With statements, where one might forget what level one is at, and looks a lot cleaner.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 14, 2008 at 7:46 am
Hi
Thanks for the Input, it sounds logical what you are saying. However, the Do you have there does it need not to be linked to a Rst? I know at the end you say look until rst.EOF = false
Id have thought the Do would have had to have been linked to it also...
Maybe not as i dont use the Do and Loop commands alot!
August 15, 2008 at 7:19 am
"Do" is nothing more than another version of "While". Neither of them require any tie to a recordset - there are no such restrictions on their use.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 15, 2008 at 7:21 am
Ahh cool, its been so long since i did anything like this i seem to have forgotten even the basics 🙂
Going to have a go getting it all working on Monday, will update the thread to tell you how i get on!
August 19, 2008 at 1:48 am
Hey
Well i finally got it working and as suggested above it was alot easier to do it via the query.
All i ended up doing was left joining the table into the query and selecting where the ID field from the Table was null. Therefore only the records that arent it would show up.
Did a simple Do adding those records into the table 🙂
Thanks for all your help!
Scott
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply