Search a recordset for a value, if not found put it in! 1

[SDS100UK]

Hi,

I would like to know how to get Access to look in a recordset for a list of values.
1,2,3,4,5,6,7,8, etc (literally) If any of them are missing then I would like it to simply put the missing values in.

The data in the table changes daily and I need to have all these numbers in every day, but the volume of data makes it impossible to check for all 32 numbers.

There may be 10,000 number 1's and 1500 number 3 etc etc but no number 7 and all I would want is 1 record that says 7.

Thera are other fields in the table all of which need to be set to 0(zero) when it places the number in.

Hope you can help

I am desperate!!

Many thanks in advance

Steven

 

[JoanneM]

Hello Stephan

There are a few ways of goin about this but just to help me out are you doing this from a form that is bound to this table if you are then the code below will help
Pop this in the module behind the form
Replace the ![missing Value] with the name of your field


sub UpdtMissingValues( MissingValue as varint)
Dim Rs As Object
Set Rs = Me.Recordset.Clone
Rs.FindFirst "[Missing Value] = " & MissingValue
if Rs.nomatch then
Rs.addnew
rs![missing Value] =MissingValue
'add any other updates you need to do here
rs.update
rs.close

end sub

If how ever it is not on a form let me know and I'll jot the code for that down.

Alternatively let me know the range of numbers you need to check for & I'll pop the whole thing down when it's compiled and running.

 

[SDS100UK]

Joanne, This is very kind of you.

I have a form which has buttons that control the import of the data into Access. I noticed this morning that 1 of my numbers was missing which causes untold grief!!

I would like to add the code to one of the existing buttons so the user is completly unaware anything is different.

All my numbers at the moment are (subject to more being added or taken away at any time)
1,4,5,6,7,8,9,11,12,14,15,16,17,18,19,20,21,27,28,29,31,32

Thank you again for your help.

Steven

 

[SDS100UK]

Joanne,

Forgot to mention the form is unbound.

Steven

 

[JoanneM]

Steven

What are you importing from and is it into a table.
the reason I ask is I need to know if my recordset is going to be a table or I am validating coming in from a csv or fixed width . txt file


JO

 

[SDS100UK]

Jo,

I import a text file into a table. It has fields and figures I do not need. I have a couple of action queries that tidy it up. The data then lives in a table called tblProductPerformance.

Each day another query archives data older than 12 weeks into anotherdb, (this stoes data only)
Hope this helps.

 

[JoanneM]

Hi Steven

Below is a snippet which will do the job for you.
One consideration is you don't say how you get the maximum numer or validate if the maximum number is the one that is missing
For now this addresses the problem up to the value of 32

I Called the field 'num' that refers to your field with the numbers in so replace num in the code with your field name


Sub CheckNum()


Dim mydb As Database
Dim rs As Recordset
Dim numval As Integer
Dim Highestval As Integer


Highestval = 32
Set mydb = CurrentDb()

Set rs = mydb.OpenRecordset("tblProductPerformance", DB_OPEN_DYNASET)
'num in rs!num should be replaced with name of the field holding this data in tblProductPerformance


With rs

For numval = 1 To Highestval
.FindFirst "num = " & numval

If .NoMatch Then
.AddNew
!num = numval
.Update
End If
Next
End With
End Sub



regards

JO