Some help, please with code

[mtrimpin24]

Would someone please help me turn the following psuedo code into visual basic?
It involves two tables. One table will be imported and named by the person
using the form. The other table is named Tableappend and it has a query
attached to it to do its thing.
The involved fields on the table, that will be used during this code's
runtime are:
Random.postedamount
Random.reportname
Random.sapcompanycode

The code shoudl do two passes, each loop looking for a specific set of
information. in order to build tableappend with the proper records.

The user imports a spreadsheet from excel, and creates a table with the
spreadsheet. Some of the records are being discarded. it should go
something like this:

Button press
{
deleteallrecordsfromtableappend( )
gettablenamefromuser( )
opentable( )
for(int counter = 0;counter<=.recordcount;counter++)
{
if random.currentrecord.postedamount>=$3500.00 appendrecord(Tableappend)
else if (random.currentrecord.sapcompanycode == 110 or 118 or 185 or 514
&& random.currentrecord.postedamount>=$1500.00 then appendrecord(Tableappend)

random.nextrecord
}

for(int counter2 = 0;counter2 <=.recordcount;counter2++)
{

if(random.currentrecord.postedamount >= $3500.00 OR
(random.currentrecord.sapcompanycode == 110 or 118 or 185 or 514 &&
random.currentrecord.postedamount >=$1500.00) OR
random.currentrecord.reportname == "mileage" "mile" "mlg")
then next record;
else appendrecord(tableappend);
counter2+10;
}

help, pretty please i've written this in c++ psuedocode. if anyone has
trouble understanding it, please ask. i really need help with this.

 

[Wolfgang Kais]

Hello "mtrimpin24".

Would someone please help me turn the following psuedo code into
visual basic? It involves two tables. One table will be imported
and named by the person using the form. The other table is named
Tableappend and it has a query attached to it to do its thing.
The involved fields on the table, that will be used during this
code's runtime are:
Random.postedamount
Random.reportname
Random.sapcompanycode

The code should do two passes, each loop looking for a specific
set of information. In order to build tableappend with the proper
records.

The user imports a spreadsheet from excel, and creates a table
with the spreadsheet. Some of the records are being discarded.
It should go something like this:

Button press
{
deleteallrecordsfromtableappend( )
gettablenamefromuser( )
opentable( )
for(int counter = 0;counter<=.recordcount;counter++)
{
if random.currentrecord.postedamount>=$3500.00
appendrecord(Tableappend)
else if (random.currentrecord.sapcompanycode == 110 or 118 or 185
or 514
&& random.currentrecord.postedamount>=$1500.00 then
appendrecord(Tableappend)

random.nextrecord
}

for(int counter2 = 0;counter2 <=.recordcount;counter2++)
{

if(random.currentrecord.postedamount >= $3500.00 OR
(random.currentrecord.sapcompanycode == 110 or 118 or 185 or 514 &&
random.currentrecord.postedamount >=$1500.00) OR
random.currentrecord.reportname == "mileage" "mile" "mlg")
then next record;
else appendrecord(tableappend);
counter2+10;
}

help, pretty please i've written this in c++ psuedocode.
if anyone has trouble understanding it, please ask. i really need
help with this.

Here's what I did (using InputBox to get the table name):

Private Sub Button_Click()
On Error GoTo Err_Button_Click

Dim strTable As String, strCriteria As String
Dim strInsert As String, strSelect As String, strSQL As String
Dim rstAppend As DAO.Recordset

strInsert = _
"INSERT INTO TableAppend (postedamount,reportname,sapcompanycode) "
strSelect = "SELECT postedamount,reportname,sapcompanycode FROM "
strCriteria = _
"(postedamount>=3500) Or " & _
"(sapcompanycode IN (110,118,185,514) And postedamount>=1500)"

With CurrentDb
.Execute "DELETE FROM tableappend"
strTable = InputBox("Enter the table name", "process table")

strSQL = strInsert & strSelect & strTable & _
" WHERE " & strCriteria
.Execute strSQL
strCriteria = "NOT (" & strCriteria & _
" Or (reportname IN ('mileage','mile','mlg')))"
strSQL = strSelect & strTable & " WHERE " & strCriteria
Set rstAppend = .OpenRecordset("TableAppend", dbOpenDynaset,
dbAppendOnly)
With .OpenRecordset(strSQL, dbOpenDynaset)
Do Until .EOF
rstAppend.AddNew
rstAppend!postedamount = !postedamount
rstAppend!reportname = !reportname
rstAppend!sapcompanycode = !sapcompanycode
rstAppend.Update
.Move 10
Loop
.Close
rstAppend.Close
End With
Set rstAppend = Nothing
End With

Exit_Button_Click:
Exit Sub

Err_Button_Click:
MsgBox Err.Description, vbExclamation
Resume Exit_Button_Click

End Sub

 

[Albert D. Kallal]

Would someone please help me turn the following psuedo code into visual
basic?
It involves two tables. One table will be imported and named by the
person
using the form. The other table is named Tableappend and it has a query
attached to it to do its thing.

I going to assume that we don't bother asking the user the table name to
import, as that just a temp working area anyway.
I will assume we have a table called TableExcel that receives the Excel
data.

You code would look like:

Sub MyExcelImport()

Dim strFile As String
Dim strSql As String

CurrentDb.Execute "delete * from tableAppend"
CurrentDB.Execute "delete * from tableExcel"

'strFile = GetExcelFile()
strFile = InputBox("Enter excel filename")
If strFile = "" Then
Exit Sub
End If

' import excel file...

DoCmd.TransferSpreadsheet acImport, , "tableExcel", strFile, True

strSql = "INSERT INTO tableAppend ([postedamount], [reportname],
[sapcompanycode])" & _
" select [postedamount], [reportname], [sapcompanycode] from
tableExcel " & _
" where (postedAmount >= 3500) or " & _
" ( (sapcompanycode in (110,118,185,514)) and (postedamount >=
1500) )"

CurrentDb.Execute strSql


End Sub

I don't understand why you have "two" loops, and the code repeated a 2nd
time??? Is this accidental, or juts a cut/paste mistake? (they look the same
to me).

I also commented out the "getExcelFile()" routine, and simply used the
inputbox command. This just means for the time being we have to type in the
full file name and path to the excel sheet.

Once we get the above code working, then I suppose we could, and should
write some code to pop open the windows file browse dialog.

To pop open the file dialog, I suggest you call the windows api to do this.

You can find example code here:

http://www.mvps.org/access/api/api0001.htm

The file browse dialog is a secondary question here. The above code is "air"
code, but is a close match here. Looking at your example code, I don't see
the need to "loop" through each records, as we can use an sql append query
to select those records (and, sql is less code).

 

[Albert D. Kallal]

Ah, in reading your post again, I just realized that tableAppend is our temp
table, and we import excel into that...

The, we do a append query to table Random....

So, the code posted would be changed a bit.....

We just change tableExcel to out tableAppend, and tableAppend becomes table
Random in my example...

 

[Albert D. Kallal]

Sorry about the newsgroup wrapping

this should be better:

Sub MyExcelImport()

Dim strFile As String
Dim strSql As String

CurrentDb.Execute "delete * from tableAppend"

'strFile = GetExcelFile()
strFile = InputBox("Enter excel filename")
If strFile = "" Then
Exit Sub
End If

' import excel file...

DoCmd.TransferSpreadsheet acImport, , "tableAppend", strFile, True

strSql = "INSERT INTO Random " & _
" ([postedamount], [reportname],[sapcompanycode])" & _
" select [postedamount], [reportname], [sapcompanycode]" & _
" from tableAppend " & _
" where ( (postedAmount >= 3500) or " & _
" ( (sapcompanycode in (110,118,185,514)) and (postedamount >= 1500) ) )"

CurrentDb.Execute strSql

End Sub

 

[mtrimpin24]

All right. i'm going to reply to Wolfgangs. -- the other code is essentially
correct, though you were right the first time. "Random" is the name of the
table i'm important, and the work/temp table that the user creates on
importing the spreadsheet. Table append has a different query attached to
it, that isn't involved here, and sorts the data and performs some other
functions.

-- when i run this code, after putting it on the correct lines because of
weird carriage returns in this posting thing, and run it i get a msg box and
a field asking for the table name -- Exactly what i wanted. After typing in
the table name though,
i'm getting "Too foo parameteres. Expected 3." it doesn't give a line
number or highlight the area of "wrongness" in the visual basic mini-app
though.
What have i done wrong?

 

[mtrimpin24]

Could we change the api so that instead it allows the user to point it to the
spreadsheet they wish to import? (ie, give them a browse dialog. i can't
guarantee the spreadsheet will always be in the same place).

 

[Albert D. Kallal]

Could we change the api so that instead it allows the user to point it to
the
spreadsheet they wish to import? (ie, give them a browse dialog. i can't
guarantee the spreadsheet will always be in the same place).

That is what I posted that link for. You can use the windows api as follows:

Paste the following code into a standard code module:

Public Function GetExcel(Optional strStartDir As String) As String

Dim strFilter As String
Dim lngFlags As Long

If strStartDir = "" Then
strStartDir = CurrentProject.Path
End If

strFilter = ahtAddFilterItem(strFilter, "Excel file (*.xls)", "*.xls")

GetExcel = ahtCommonFileOpenSave(InitialDir:=strStartDir, _
Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
DialogTitle:="Select Excel sheet")


End Function


You then need to grab the actual api code for the browse dialog here:

http://www.mvps.org/access/api/api0001.htm

(in fact, I suggest you paste in the code from the above link first, and
then add my 2nd example).

You can then in use use the above fucntion.

strFileName = GetExcel

make sure the code compiles before you try and run it (debug->compile from
the code menu).

 

[mtrimpin24]

I'm pasting the whole thing below the first sub, yes? all 100 lines or so?
When i try compiling that, it tells me " Compile error, only comments may
appear after end sub, end type."
am i putting these in the wrong order?
i went into "modules" on the database design.
opened a "new" module"
pasted the api code, then yours.
changed the first line that says strFileName=(left, 0, 0 )
to StrFileName=GetExcel

 

[Albert D. Kallal]

I'm pasting the whole thing below the first sub, yes? all 100 lines or
so?

No, you want to create a new separate code module for this purpose.

From the main access menu go insert->module

(you can also use the modules tab, and then hit new.....).

You then paste in the api code.

You then can paste in my sample code...

now, hit the save button, you going to give this code module a name.

basApi

You can give the code module any name you want..but, it just can't conflict
name wise with the name of subs, or functions contained in the code in the
module.

The first few lines of the module code should look like;


Option Compare Database
Option Explicit

'***************** Code Start **************
'This code was originally written by Ken Getz.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
' Code courtesy of:
' Microsoft Access 95 How-To
' Ken Getz and Paul Litwin
' Waite Group Press, 1996

Type tagOPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
strFilter As String
strCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long

etc etc. etc............

Note you *should* as a rule place the "option explicit" in all of your code
modules (this will fore you to declare all variables). In fact, while in the
code editor, go tools->options->"editor tab", check the box

[x] Requite variable declaration

It's one of those great mysteries as to why this option is never checked by
default. you don't have to check the check box, but I think for any self
worth developer this would likely be the first thing you do when you start a
new project in access.

anyway, so paste in the code from the api. Then, after the api code, paste
in my example code. Now, as mentioned, hit save....

Adding code to a module simply means that you're adding functions and
subroutines that can be used by any form code, or other "models of code that
you add.... thus, act you added this above code, then you can place a button
on a form to run your other code example, and, you should at this point then
be able to use the GATT excel function that just added.

 

[mtrimpin24]

All right. made a new module, added in your code after. made no changes to
strFilename or anything like that.
attached a macro to a button that does runcode
basApi

i get the following:
The object doesn't contain the Automation object '<<OFN>>.'
You tried to run a Visual Basic procedure to set a property or method for
an object. However, the component
doesnt' make the property or method available for Automation operations.

and so on.

I'm pasting the whole thing below the first sub, yes? all 100 lines or
so?

No, you want to create a new separate code module for this purpose.

From the main access menu go insert->module

(you can also use the modules tab, and then hit new.....).

You then paste in the api code.

You then can paste in my sample code...

now, hit the save button, you going to give this code module a name.

basApi

You can give the code module any name you want..but, it just can't conflict
name wise with the name of subs, or functions contained in the code in the
module.

The first few lines of the module code should look like;


Option Compare Database
Option Explicit

'***************** Code Start **************
'This code was originally written by Ken Getz.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
' Code courtesy of:
' Microsoft Access 95 How-To
' Ken Getz and Paul Litwin
' Waite Group Press, 1996

Type tagOPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
strFilter As String
strCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long

etc etc. etc............

Note you *should* as a rule place the "option explicit" in all of your code
modules (this will fore you to declare all variables). In fact, while in the
code editor, go tools->options->"editor tab", check the box

[x] Requite variable declaration

It's one of those great mysteries as to why this option is never checked by
default. you don't have to check the check box, but I think for any self
worth developer this would likely be the first thing you do when you start a
new project in access.

anyway, so paste in the code from the api. Then, after the api code, paste
in my example code. Now, as mentioned, hit save....

Adding code to a module simply means that you're adding functions and
subroutines that can be used by any form code, or other "models of code that
you add.... thus, act you added this above code, then you can place a button
on a form to run your other code example, and, you should at this point then
be able to use the GATT excel function that just added.

 

[mtrimpin24]

All right. i've got the button running. it opens the api, asks me to import
something. now, when i go open up tableappend, i find an empty table, with
nothing in it

All right. made a new module, added in your code after. made no changes to
strFilename or anything like that.
attached a macro to a button that does runcode
basApi

i get the following:
The object doesn't contain the Automation object '<<OFN>>.'
You tried to run a Visual Basic procedure to set a property or method for
an object. However, the component
doesnt' make the property or method available for Automation operations.

and so on.

I'm pasting the whole thing below the first sub, yes? all 100 lines or
so?

No, you want to create a new separate code module for this purpose.

From the main access menu go insert->module

(you can also use the modules tab, and then hit new.....).

You then paste in the api code.

You then can paste in my sample code...

now, hit the save button, you going to give this code module a name.

basApi

You can give the code module any name you want..but, it just can't conflict
name wise with the name of subs, or functions contained in the code in the
module.

The first few lines of the module code should look like;


Option Compare Database
Option Explicit

'***************** Code Start **************
'This code was originally written by Ken Getz.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
' Code courtesy of:
' Microsoft Access 95 How-To
' Ken Getz and Paul Litwin
' Waite Group Press, 1996

Type tagOPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
strFilter As String
strCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long

etc etc. etc............

Note you *should* as a rule place the "option explicit" in all of your code
modules (this will fore you to declare all variables). In fact, while in the
code editor, go tools->options->"editor tab", check the box

[x] Requite variable declaration

It's one of those great mysteries as to why this option is never checked by
default. you don't have to check the check box, but I think for any self
worth developer this would likely be the first thing you do when you start a
new project in access.

anyway, so paste in the code from the api. Then, after the api code, paste
in my example code. Now, as mentioned, hit save....

Adding code to a module simply means that you're adding functions and
subroutines that can be used by any form code, or other "models of code that
you add.... thus, act you added this above code, then you can place a button
on a form to run your other code example, and, you should at this point then
be able to use the GATT excel function that just added.

 

[mtrimpin24]

Shouldn't the button be running "Getexcel" which calls the api for use to
import the spreadsheet? i'm not sure i've put this in properly. the module
contains the api code you pointed to, and at the end of the api code, after
all the end sub's, is your code, get excel.

i put a command button, attached a macro to it, the macro has one thing on
its list, "runcode" and "getexcel()" as the target function.

It isn't working
This may be a bit annoying for you, but i'm really a noob when it comes to
visual basic and access database setup . i can build the things. i just can't
code for jack for them. i'm a c++ coder

All right. i've got the button running. it opens the api, asks me to import
something. now, when i go open up tableappend, i find an empty table, with
nothing in it

All right. made a new module, added in your code after. made no changes to
strFilename or anything like that.
attached a macro to a button that does runcode
basApi

i get the following:
The object doesn't contain the Automation object '<<OFN>>.'
You tried to run a Visual Basic procedure to set a property or method for
an object. However, the component
doesnt' make the property or method available for Automation operations.

and so on.

I'm pasting the whole thing below the first sub, yes? all 100 lines or
so?


No, you want to create a new separate code module for this purpose.

From the main access menu go insert->module

(you can also use the modules tab, and then hit new.....).

You then paste in the api code.

You then can paste in my sample code...

now, hit the save button, you going to give this code module a name.

basApi

You can give the code module any name you want..but, it just can't conflict
name wise with the name of subs, or functions contained in the code in the
module.

The first few lines of the module code should look like;


Option Compare Database
Option Explicit

'***************** Code Start **************
'This code was originally written by Ken Getz.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
' Code courtesy of:
' Microsoft Access 95 How-To
' Ken Getz and Paul Litwin
' Waite Group Press, 1996

Type tagOPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
strFilter As String
strCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long

etc etc. etc............

Note you *should* as a rule place the "option explicit" in all of your code
modules (this will fore you to declare all variables). In fact, while in the
code editor, go tools->options->"editor tab", check the box

[x] Requite variable declaration

It's one of those great mysteries as to why this option is never checked by
default. you don't have to check the check box, but I think for any self
worth developer this would likely be the first thing you do when you start a
new project in access.

anyway, so paste in the code from the api. Then, after the api code, paste
in my example code. Now, as mentioned, hit save....

Adding code to a module simply means that you're adding functions and
subroutines that can be used by any form code, or other "models of code that
you add.... thus, act you added this above code, then you can place a button
on a form to run your other code example, and, you should at this point then
be able to use the GATT excel function that just added.

 

[mtrimpin24]

i fiddled with it some more. when i click the button, the macro attempts to
run "myexcelimport()"
and i changed the line strFilename=getExcel()... well here
let me paste what i've got. this is all in the one module:

Type tagOPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
strFilter As String
strCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
strFile As String
nMaxFile As Long
strFileTitle As String
nMaxFileTitle As Long
strInitialDir As String
strTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
strDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean

Declare Function aht_apiGetSaveFileName Lib "comdlg32.dll" _
Alias "GetSaveFileNameA" (OFN As tagOPENFILENAME) As Boolean
Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long

Global Const ahtOFN_READONLY = &H1
Global Const ahtOFN_OVERWRITEPROMPT = &H2
Global Const ahtOFN_HIDEREADONLY = &H4
Global Const ahtOFN_NOCHANGEDIR = &H8
Global Const ahtOFN_SHOWHELP = &H10
' You won't use these.
'Global Const ahtOFN_ENABLEHOOK = &H20
'Global Const ahtOFN_ENABLETEMPLATE = &H40
'Global Const ahtOFN_ENABLETEMPLATEHANDLE = &H80
Global Const ahtOFN_NOVALIDATE = &H100
Global Const ahtOFN_ALLOWMULTISELECT = &H200
Global Const ahtOFN_EXTENSIONDIFFERENT = &H400
Global Const ahtOFN_PATHMUSTEXIST = &H800
Global Const ahtOFN_FILEMUSTEXIST = &H1000
Global Const ahtOFN_CREATEPROMPT = &H2000
Global Const ahtOFN_SHAREAWARE = &H4000
Global Const ahtOFN_NOREADONLYRETURN = &H8000
Global Const ahtOFN_NOTESTFILECREATE = &H10000
Global Const ahtOFN_NONETWORKBUTTON = &H20000
Global Const ahtOFN_NOLONGNAMES = &H40000
' New for Windows 95
Global Const ahtOFN_EXPLORER = &H80000
Global Const ahtOFN_NODEREFERENCELINKS = &H100000
Global Const ahtOFN_LONGNAMES = &H200000

Function TestIt()
Dim strFilter As String
Dim lngFlags As Long
strFilter = ahtAddFilterItem(strFilter, "Access Files (*.mda, *.mdb)", _
"*.MDA;*.MDB")
strFilter = ahtAddFilterItem(strFilter, "dBASE Files (*.dbf)", "*.DBF")
strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)", "*.TXT")
strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
MsgBox "You selected: " & ahtCommonFileOpenSave(InitialDir:="C:\", _
Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
DialogTitle:="Hello! Open Me!")
' Since you passed in a variable for lngFlags,
' the function places the output flags value in the variable.
Debug.Print Hex(lngFlags)
End Function

Function GetOpenFile(Optional varDirectory As Variant, _
Optional varTitleForDialog As Variant) As Variant
' Here's an example that gets an Access database name.
Dim strFilter As String
Dim lngFlags As Long
Dim varFileName As Variant
' Specify that the chosen file must already exist,
' don't change directories when you're done
' Also, don't bother displaying
' the read-only box. It'll only confuse people.
lngFlags = ahtOFN_FILEMUSTEXIST Or _
ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIR
If IsMissing(varDirectory) Then
varDirectory = ""
End If
If IsMissing(varTitleForDialog) Then
varTitleForDialog = ""
End If

' Define the filter string and allocate space in the "c"
' string Duplicate this line with changes as necessary for
' more file templates.
strFilter = ahtAddFilterItem(strFilter, _
"Access (*.mdb)", "*.MDB;*.MDA")
' Now actually call to get the file name.
varFileName = ahtCommonFileOpenSave( _
OpenFile:=True, _
InitialDir:=varDirectory, _
Filter:=strFilter, _
Flags:=lngFlags, _
DialogTitle:=varTitleForDialog)
If Not IsNull(varFileName) Then
varFileName = TrimNull(varFileName)
End If
GetOpenFile = varFileName
End Function

Function ahtCommonFileOpenSave( _
Optional ByRef Flags As Variant, _
Optional ByVal InitialDir As Variant, _
Optional ByVal Filter As Variant, _
Optional ByVal FilterIndex As Variant, _
Optional ByVal DefaultExt As Variant, _
Optional ByVal FileName As Variant, _
Optional ByVal DialogTitle As Variant, _
Optional ByVal hwnd As Variant, _
Optional ByVal OpenFile As Variant) As Variant
' This is the entry point you'll use to call the common
' file open/save dialog. The parameters are listed
' below, and all are optional.
'
' In:
' Flags: one or more of the ahtOFN_* constants, OR'd together.
' InitialDir: the directory in which to first look
' Filter: a set of file filters, set up by calling
' AddFilterItem. See examples.
' FilterIndex: 1-based integer indicating which filter
' set to use, by default (1 if unspecified)
' DefaultExt: Extension to use if the user doesn't enter one.
' Only useful on file saves.
' FileName: Default value for the file name text box.
' DialogTitle: Title for the dialog.
' hWnd: parent window handle
' OpenFile: Boolean(True=Open File/False=Save As)
' Out:
' Return Value: Either Null or the selected filename
Dim OFN As tagOPENFILENAME
Dim strFileName As String
Dim strFileTitle As String
Dim fResult As Boolean
' Give the dialog a caption title.
If IsMissing(InitialDir) Then InitialDir = CurDir
If IsMissing(Filter) Then Filter = ""
If IsMissing(FilterIndex) Then FilterIndex = 1
If IsMissing(Flags) Then Flags = 0&
If IsMissing(DefaultExt) Then DefaultExt = ""
If IsMissing(FileName) Then FileName = ""
If IsMissing(DialogTitle) Then DialogTitle = ""
If IsMissing(hwnd) Then hwnd = Application.hWndAccessApp
If IsMissing(OpenFile) Then OpenFile = True
' Allocate string space for the returned strings.
strFileName = Left(FileName & String(256, 0), 256)
strFileTitle = String(256, 0)
' Set up the data structure before you call the function
With OFN
.lStructSize = Len(OFN)
.hwndOwner = hwnd
.strFilter = Filter
.nFilterIndex = FilterIndex
.strFile = strFileName
.nMaxFile = Len(strFileName)
.strFileTitle = strFileTitle
.nMaxFileTitle = Len(strFileTitle)
.strTitle = DialogTitle
.Flags = Flags
.strDefExt = DefaultExt
.strInitialDir = InitialDir
' Didn't think most people would want to deal with
' these options.
.hInstance = 0
'.strCustomFilter = ""
'.nMaxCustFilter = 0
.lpfnHook = 0
'New for NT 4.0
.strCustomFilter = String(255, 0)
.nMaxCustFilter = 255
End With
' This will pass the desired data structure to the
' Windows API, which will in turn it uses to display
' the Open/Save As Dialog.
If OpenFile Then
fResult = aht_apiGetOpenFileName(OFN)
Else
fResult = aht_apiGetSaveFileName(OFN)
End If

' The function call filled in the strFileTitle member
' of the structure. You'll have to write special code
' to retrieve that if you're interested.
If fResult Then
' You might care to check the Flags member of the
' structure to get information about the chosen file.
' In this example, if you bothered to pass in a
' value for Flags, we'll fill it in with the outgoing
' Flags value.
If Not IsMissing(Flags) Then Flags = OFN.Flags
ahtCommonFileOpenSave = TrimNull(OFN.strFile)
Else
ahtCommonFileOpenSave = vbNullString
End If
End Function

Function ahtAddFilterItem(strFilter As String, _
strDescription As String, Optional varItem As Variant) As String
' Tack a new chunk onto the file filter.
' That is, take the old value, stick onto it the description,
' (like "Databases"), a null character, the skeleton
' (like "*.mdb;*.mda") and a final null character.

If IsMissing(varItem) Then varItem = "*.*"
ahtAddFilterItem = strFilter & _
strDescription & vbNullChar & _
varItem & vbNullChar
End Function

Private Function TrimNull(ByVal strItem As String) As String
Dim intPos As Integer
intPos = InStr(strItem, vbNullChar)
If intPos > 0 Then
TrimNull = Left(strItem, intPos - 1)
Else
TrimNull = strItem
End If
End Function



Public Function GetExcel(Optional strStartDir As String) As String

Dim strFilter As String
Dim lngFlags As Long

If strStartDir = "" Then
strStartDir = CurrentProject.Path
End If

strFilter = ahtAddFilterItem(strFilter, "Excel file (*.xls)", "*.xls")

GetExcel = ahtCommonFileOpenSave(InitialDir:=strStartDir, _
Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
DialogTitle:="Select Excel sheet")


End Function
Sub MyExcelImport()

Dim strFile As String
Dim strSql As String

CurrentDb.Execute "delete * from tableAppend"

strFile = GetExcel()
strFile = InputBox("Enter excel filename")
If strFile = "" Then
Exit Sub
End If

' import excel file...

DoCmd.TransferSpreadsheet acImport, , "tableAppend", strFile, True

strSql = "INSERT INTO Random " & _
" ([postedamount], [reportname],[sapcompanycode])" & _
" select [postedamount], [reportname], [sapcompanycode]" & _
" from tableAppend " & _
" where ( (postedAmount >= 3500) or " & _
" ( (sapcompanycode in (010,528,185,113)) and (postedamount >= 1500) ) )"

CurrentDb.Execute strSql

End Sub

All right. i've got the button running. it opens the api, asks me to import
something. now, when i go open up tableappend, i find an empty table, with
nothing in it

All right. made a new module, added in your code after. made no changes to
strFilename or anything like that.
attached a macro to a button that does runcode
basApi

i get the following:
The object doesn't contain the Automation object '<<OFN>>.'
You tried to run a Visual Basic procedure to set a property or method for
an object. However, the component
doesnt' make the property or method available for Automation operations.

and so on.

I'm pasting the whole thing below the first sub, yes? all 100 lines or
so?


No, you want to create a new separate code module for this purpose.

From the main access menu go insert->module

(you can also use the modules tab, and then hit new.....).

You then paste in the api code.

You then can paste in my sample code...

now, hit the save button, you going to give this code module a name.

basApi

You can give the code module any name you want..but, it just can't conflict
name wise with the name of subs, or functions contained in the code in the
module.

The first few lines of the module code should look like;


Option Compare Database
Option Explicit

'***************** Code Start **************
'This code was originally written by Ken Getz.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
' Code courtesy of:
' Microsoft Access 95 How-To
' Ken Getz and Paul Litwin
' Waite Group Press, 1996

Type tagOPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
strFilter As String
strCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long

etc etc. etc............

Note you *should* as a rule place the "option explicit" in all of your code
modules (this will fore you to declare all variables). In fact, while in the
code editor, go tools->options->"editor tab", check the box

[x] Requite variable declaration

It's one of those great mysteries as to why this option is never checked by
default. you don't have to check the check box, but I think for any self
worth developer this would likely be the first thing you do when you start a
new project in access.

anyway, so paste in the code from the api. Then, after the api code, paste
in my example code. Now, as mentioned, hit save....

Adding code to a module simply means that you're adding functions and
subroutines that can be used by any form code, or other "models of code that
you add.... thus, act you added this above code, then you can place a button
on a form to run your other code example, and, you should at this point then
be able to use the GATT excel function that just added.

 

[Albert D. Kallal]

If you want to run your code, then don't bother with macros, they are pain
to use.

The code behind a button on a form could go:

Call MyExcelImport.

Furthermore, you can call the code from the "command" line, or so called
debug window.

While looking at the code in the module, you can hit ctrl-g..and you will
get the debug window.

You can then type in:

Call MyExcelImport

(in fact, legal syntax is also:

MyExcelImport

In fact, you can type in any legal VBA command in that debug window. So,
eventually, I sure you place a button on some form to make this easy for the
end users (they never will see the debug-window like you the developer...

Another way to run the code is to place your cursor in the sub you want to
run (anywhere in the sub), and then hit f5 key (it will run that sub from
the beginning).

And, if you hit f8 while in that code, you are in single step debug mode
(one line is executed for he press of f8. Of course, you want to skip the
"getExdel" code as you single step). If you look at the menu bar...you see a
"step over" which means to run the sub (or function) code you are currently
on...but don't single step.

Once again, I would NOT bother with macros at all...I would use code.....

In fact, while in the debug window, try this:

? 2 * 4

? getExcel

You can type in any legal expression...in the above, ? GetExcel will print
the value of the function GetExcel. Give it a try....

 

[mtrimpin24]

Okay. this doesn't seem to do everythign though -- i also wanted to sample
10% of the records that didn't meet the other criteria. so of a set of
records:
any that met the first criteria were in automatically
any that met the second crieteria were in automatically
10% of the records that didn't meet either criteria were also added to the
list
NONE of the records should have the field reportname = "mileage" or "mile"
or "mlg"

am i wrong, or does this not do all of this?

i fiddled with it some more. when i click the button, the macro attempts to
run "myexcelimport()"
and i changed the line strFilename=getExcel()... well here
let me paste what i've got. this is all in the one module:

Type tagOPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
strFilter As String
strCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
strFile As String
nMaxFile As Long
strFileTitle As String
nMaxFileTitle As Long
strInitialDir As String
strTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
strDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean

Declare Function aht_apiGetSaveFileName Lib "comdlg32.dll" _
Alias "GetSaveFileNameA" (OFN As tagOPENFILENAME) As Boolean
Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long

Global Const ahtOFN_READONLY = &H1
Global Const ahtOFN_OVERWRITEPROMPT = &H2
Global Const ahtOFN_HIDEREADONLY = &H4
Global Const ahtOFN_NOCHANGEDIR = &H8
Global Const ahtOFN_SHOWHELP = &H10
' You won't use these.
'Global Const ahtOFN_ENABLEHOOK = &H20
'Global Const ahtOFN_ENABLETEMPLATE = &H40
'Global Const ahtOFN_ENABLETEMPLATEHANDLE = &H80
Global Const ahtOFN_NOVALIDATE = &H100
Global Const ahtOFN_ALLOWMULTISELECT = &H200
Global Const ahtOFN_EXTENSIONDIFFERENT = &H400
Global Const ahtOFN_PATHMUSTEXIST = &H800
Global Const ahtOFN_FILEMUSTEXIST = &H1000
Global Const ahtOFN_CREATEPROMPT = &H2000
Global Const ahtOFN_SHAREAWARE = &H4000
Global Const ahtOFN_NOREADONLYRETURN = &H8000
Global Const ahtOFN_NOTESTFILECREATE = &H10000
Global Const ahtOFN_NONETWORKBUTTON = &H20000
Global Const ahtOFN_NOLONGNAMES = &H40000
' New for Windows 95
Global Const ahtOFN_EXPLORER = &H80000
Global Const ahtOFN_NODEREFERENCELINKS = &H100000
Global Const ahtOFN_LONGNAMES = &H200000

Function TestIt()
Dim strFilter As String
Dim lngFlags As Long
strFilter = ahtAddFilterItem(strFilter, "Access Files (*.mda, *.mdb)", _
"*.MDA;*.MDB")
strFilter = ahtAddFilterItem(strFilter, "dBASE Files (*.dbf)", "*.DBF")
strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)", "*.TXT")
strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
MsgBox "You selected: " & ahtCommonFileOpenSave(InitialDir:="C:\", _
Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
DialogTitle:="Hello! Open Me!")
' Since you passed in a variable for lngFlags,
' the function places the output flags value in the variable.
Debug.Print Hex(lngFlags)
End Function

Function GetOpenFile(Optional varDirectory As Variant, _
Optional varTitleForDialog As Variant) As Variant
' Here's an example that gets an Access database name.
Dim strFilter As String
Dim lngFlags As Long
Dim varFileName As Variant
' Specify that the chosen file must already exist,
' don't change directories when you're done
' Also, don't bother displaying
' the read-only box. It'll only confuse people.
lngFlags = ahtOFN_FILEMUSTEXIST Or _
ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIR
If IsMissing(varDirectory) Then
varDirectory = ""
End If
If IsMissing(varTitleForDialog) Then
varTitleForDialog = ""
End If

' Define the filter string and allocate space in the "c"
' string Duplicate this line with changes as necessary for
' more file templates.
strFilter = ahtAddFilterItem(strFilter, _
"Access (*.mdb)", "*.MDB;*.MDA")
' Now actually call to get the file name.
varFileName = ahtCommonFileOpenSave( _
OpenFile:=True, _
InitialDir:=varDirectory, _
Filter:=strFilter, _
Flags:=lngFlags, _
DialogTitle:=varTitleForDialog)
If Not IsNull(varFileName) Then
varFileName = TrimNull(varFileName)
End If
GetOpenFile = varFileName
End Function

Function ahtCommonFileOpenSave( _
Optional ByRef Flags As Variant, _
Optional ByVal InitialDir As Variant, _
Optional ByVal Filter As Variant, _
Optional ByVal FilterIndex As Variant, _
Optional ByVal DefaultExt As Variant, _
Optional ByVal FileName As Variant, _
Optional ByVal DialogTitle As Variant, _
Optional ByVal hwnd As Variant, _
Optional ByVal OpenFile As Variant) As Variant
' This is the entry point you'll use to call the common
' file open/save dialog. The parameters are listed
' below, and all are optional.
'
' In:
' Flags: one or more of the ahtOFN_* constants, OR'd together.
' InitialDir: the directory in which to first look
' Filter: a set of file filters, set up by calling
' AddFilterItem. See examples.
' FilterIndex: 1-based integer indicating which filter
' set to use, by default (1 if unspecified)
' DefaultExt: Extension to use if the user doesn't enter one.
' Only useful on file saves.
' FileName: Default value for the file name text box.
' DialogTitle: Title for the dialog.
' hWnd: parent window handle
' OpenFile: Boolean(True=Open File/False=Save As)
' Out:
' Return Value: Either Null or the selected filename
Dim OFN As tagOPENFILENAME
Dim strFileName As String
Dim strFileTitle As String
Dim fResult As Boolean
' Give the dialog a caption title.
If IsMissing(InitialDir) Then InitialDir = CurDir
If IsMissing(Filter) Then Filter = ""
If IsMissing(FilterIndex) Then FilterIndex = 1
If IsMissing(Flags) Then Flags = 0&
If IsMissing(DefaultExt) Then DefaultExt = ""
If IsMissing(FileName) Then FileName = ""
If IsMissing(DialogTitle) Then DialogTitle = ""
If IsMissing(hwnd) Then hwnd = Application.hWndAccessApp
If IsMissing(OpenFile) Then OpenFile = True
' Allocate string space for the returned strings.
strFileName = Left(FileName & String(256, 0), 256)
strFileTitle = String(256, 0)
' Set up the data structure before you call the function
With OFN
.lStructSize = Len(OFN)
.hwndOwner = hwnd
.strFilter = Filter
.nFilterIndex = FilterIndex
.strFile = strFileName
.nMaxFile = Len(strFileName)
.strFileTitle = strFileTitle
.nMaxFileTitle = Len(strFileTitle)
.strTitle = DialogTitle
.Flags = Flags
.strDefExt = DefaultExt
.strInitialDir = InitialDir
' Didn't think most people would want to deal with
' these options.
.hInstance = 0
'.strCustomFilter = ""
'.nMaxCustFilter = 0
.lpfnHook = 0
'New for NT 4.0
.strCustomFilter = String(255, 0)
.nMaxCustFilter = 255
End With
' This will pass the desired data structure to the
' Windows API, which will in turn it uses to display
' the Open/Save As Dialog.
If OpenFile Then
fResult = aht_apiGetOpenFileName(OFN)
Else
fResult = aht_apiGetSaveFileName(OFN)
End If

' The function call filled in the strFileTitle member
' of the structure. You'll have to write special code
' to retrieve that if you're interested.
If fResult Then
' You might care to check the Flags member of the
' structure to get information about the chosen file.
' In this example, if you bothered to pass in a
' value for Flags, we'll fill it in with the outgoing
' Flags value.
If Not IsMissing(Flags) Then Flags = OFN.Flags
ahtCommonFileOpenSave = TrimNull(OFN.strFile)
Else
ahtCommonFileOpenSave = vbNullString
End If
End Function

Function ahtAddFilterItem(strFilter As String, _
strDescription As String, Optional varItem As Variant) As String
' Tack a new chunk onto the file filter.
' That is, take the old value, stick onto it the description,
' (like "Databases"), a null character, the skeleton
' (like "*.mdb;*.mda") and a final null character.

If IsMissing(varItem) Then varItem = "*.*"
ahtAddFilterItem = strFilter & _
strDescription & vbNullChar & _
varItem & vbNullChar
End Function

Private Function TrimNull(ByVal strItem As String) As String
Dim intPos As Integer
intPos = InStr(strItem, vbNullChar)
If intPos > 0 Then
TrimNull = Left(strItem, intPos - 1)
Else
TrimNull = strItem
End If
End Function



Public Function GetExcel(Optional strStartDir As String) As String

Dim strFilter As String
Dim lngFlags As Long

If strStartDir = "" Then
strStartDir = CurrentProject.Path
End If

strFilter = ahtAddFilterItem(strFilter, "Excel file (*.xls)", "*.xls")

GetExcel = ahtCommonFileOpenSave(InitialDir:=strStartDir, _
Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
DialogTitle:="Select Excel sheet")


End Function
Sub MyExcelImport()

Dim strFile As String
Dim strSql As String

CurrentDb.Execute "delete * from tableAppend"

strFile = GetExcel()
strFile = InputBox("Enter excel filename")
If strFile = "" Then
Exit Sub
End If

' import excel file...

DoCmd.TransferSpreadsheet acImport, , "tableAppend", strFile, True

strSql = "INSERT INTO Random " & _
" ([postedamount], [reportname],[sapcompanycode])" & _
" select [postedamount], [reportname], [sapcompanycode]" & _
" from tableAppend " & _
" where ( (postedAmount >= 3500) or " & _
" ( (sapcompanycode in (010,528,185,113)) and (postedamount >= 1500) ) )"

CurrentDb.Execute strSql

End Sub

All right. i've got the button running. it opens the api, asks me to import
something. now, when i go open up tableappend, i find an empty table, with
nothing in it

 

[Albert D. Kallal]

Okay. this doesn't seem to do everythign though -- i also wanted to sample
10% of the records that didn't meet the other criteria. so of a set of
records:

Ok, that part was missed, and thus does explain why you had two loops in
your original code sample.
(and, also explains why I asked What the importance of the second code loop
is.). when I looked
at the original code, the 2nd loop looked the same to me. (at least that's
what my brain saw).

Thus, I did ask why the 2nd loop....

any that met the first criteria were in automatically
any that met the second crieteria were in automatically
10% of the records that didn't meet either criteria were also added to the
list

OK, there is really a lot of ways to do this. Does the order matter of the
records we've pulled in?
in other words, if I have a 100 records leftover, can I just take the first
ten records?
(this is less code, since we can use sql statements, and not have to loop
that this was the case).

Or, do you really wanna take every other 10th record distributed throughout
the data that was left over from the first criteria?

am i wrong, or does this not do all of this?

no, what we have so far does the first step in the first criteria (your
first the pseudo code loop).

We could just delete the reocrds in the append table that we already moved,
and then
take 10% of the reocrds as they occur. I think this approach would likely be
the easiest to code.

Howver, if you really want to "skip" every 10th reocrd that is left over,
then will have to break down and build a record set.
( I was trying to avoid this, and save code).

Hence, we can build an actual record set (as the other posters suggested)

I think this approach is a reasonable way to go, especially if we're only
dealing with three fields.

the additonal code for the 2nd part could be:

Dim rstAppend As DAO.Recordset
Dim rstRandom As DAO.Recordset
Dim lngRecordPtr As Long


strSql = "select [postedamount], [reportname], [sapcompanycode]" & _
" from tableAppend " & _
" where (not ( (postedAmount >= 3500) or " & _
" ( (sapcompanycode in (110,118,185,514)) and (postedamount >= 1500) ) ))"

Set rstAppend = CurrentDb.OpenRecordset(strSql)
Do While rstAppend.EOF = False
rstRandom.AddNew
rstRandom!PostedAmount = rstAppend!PostedAmount
rstRandom!ReprotName = rstAppend!ReportName
rstRandom!sapcompnaycode = rstAppend!sapcompnaycode
rstRandom.Update
rstAppend.Move 10
Loop

notice in the above I simply put a "not" command in front of the SQL
criteria. -- also keep in mind the above is a bit of air code but I think
it's pretty close.

I have to give credit to the other poster, as he understood your second code
loop perfectly, and posted code as such -- my apologies for missing that
code....

 

[mtrimpin24]

Honestly it doesn't matter. the idea is, that when they were doing this with
paper (these are Audits of expense reports) they would take a "random" 10% of
the records. In addition, any reports that were over 3500$ or if they were
in certain orgs, above 1500$ since 3500 was rare, they woudl also be audited.
anyway. so 10-- they accomplished this by choosing every 10th record,
guaging whether or not it fit any of the other criteria and thus would have
been included anyway, and, if it was already included, just going to the next
record (11th).
so having included all the records that met the first two criteria, you
would be choosing every 10th, 20th, 30th, record from a list that didn't
include the previous criteria, and where the reportname field didn't have the
text "mile" "mileage" or "Mlg" in it.

on my main form there's a checkbox in case some dumbass mispells mileage or
just goes dyslexic on me -- but i'll get to that later. that will require
some code too, and since the database is split i'm not sure how to approach
that one either.

Okay. this doesn't seem to do everythign though -- i also wanted to sample
10% of the records that didn't meet the other criteria. so of a set of
records:

Ok, that part was missed, and thus does explain why you had two loops in
your original code sample.
(and, also explains why I asked What the importance of the second code loop
is.). when I looked
at the original code, the 2nd loop looked the same to me. (at least that's
what my brain saw).

Thus, I did ask why the 2nd loop....

any that met the first criteria were in automatically
any that met the second crieteria were in automatically
10% of the records that didn't meet either criteria were also added to the
list

OK, there is really a lot of ways to do this. Does the order matter of the
records we've pulled in?
in other words, if I have a 100 records leftover, can I just take the first
ten records?
(this is less code, since we can use sql statements, and not have to loop
that this was the case).

Or, do you really wanna take every other 10th record distributed throughout
the data that was left over from the first criteria?

am i wrong, or does this not do all of this?

no, what we have so far does the first step in the first criteria (your
first the pseudo code loop).

We could just delete the reocrds in the append table that we already moved,
and then
take 10% of the reocrds as they occur. I think this approach would likely be
the easiest to code.

Howver, if you really want to "skip" every 10th reocrd that is left over,
then will have to break down and build a record set.
( I was trying to avoid this, and save code).

Hence, we can build an actual record set (as the other posters suggested)

I think this approach is a reasonable way to go, especially if we're only
dealing with three fields.

the additonal code for the 2nd part could be:

Dim rstAppend As DAO.Recordset
Dim rstRandom As DAO.Recordset
Dim lngRecordPtr As Long


strSql = "select [postedamount], [reportname], [sapcompanycode]" & _
" from tableAppend " & _
" where (not ( (postedAmount >= 3500) or " & _
" ( (sapcompanycode in (110,118,185,514)) and (postedamount >= 1500) ) ))"

Set rstAppend = CurrentDb.OpenRecordset(strSql)
Do While rstAppend.EOF = False
rstRandom.AddNew
rstRandom!PostedAmount = rstAppend!PostedAmount
rstRandom!ReprotName = rstAppend!ReportName
rstRandom!sapcompnaycode = rstAppend!sapcompnaycode
rstRandom.Update
rstAppend.Move 10
Loop

notice in the above I simply put a "not" command in front of the SQL
criteria. -- also keep in mind the above is a bit of air code but I think
it's pretty close.

I have to give credit to the other poster, as he understood your second code
loop perfectly, and posted code as such -- my apologies for missing that
code....

 

[mtrimpin24]

So could i get some help with that

Honestly it doesn't matter. the idea is, that when they were doing this with
paper (these are Audits of expense reports) they would take a "random" 10% of
the records. In addition, any reports that were over 3500$ or if they were
in certain orgs, above 1500$ since 3500 was rare, they woudl also be audited.
anyway. so 10-- they accomplished this by choosing every 10th record,
guaging whether or not it fit any of the other criteria and thus would have
been included anyway, and, if it was already included, just going to the next
record (11th).
so having included all the records that met the first two criteria, you
would be choosing every 10th, 20th, 30th, record from a list that didn't
include the previous criteria, and where the reportname field didn't have the
text "mile" "mileage" or "Mlg" in it.

on my main form there's a checkbox in case some dumbass mispells mileage or
just goes dyslexic on me -- but i'll get to that later. that will require
some code too, and since the database is split i'm not sure how to approach
that one either.

Okay. this doesn't seem to do everythign though -- i also wanted to sample
10% of the records that didn't meet the other criteria. so of a set of
records:

Ok, that part was missed, and thus does explain why you had two loops in
your original code sample.
(and, also explains why I asked What the importance of the second code loop
is.). when I looked
at the original code, the 2nd loop looked the same to me. (at least that's
what my brain saw).

Thus, I did ask why the 2nd loop....

any that met the first criteria were in automatically
any that met the second crieteria were in automatically
10% of the records that didn't meet either criteria were also added to the
list

OK, there is really a lot of ways to do this. Does the order matter of the
records we've pulled in?
in other words, if I have a 100 records leftover, can I just take the first
ten records?
(this is less code, since we can use sql statements, and not have to loop
that this was the case).

Or, do you really wanna take every other 10th record distributed throughout
the data that was left over from the first criteria?

am i wrong, or does this not do all of this?

no, what we have so far does the first step in the first criteria (your
first the pseudo code loop).

We could just delete the reocrds in the append table that we already moved,
and then
take 10% of the reocrds as they occur. I think this approach would likely be
the easiest to code.

Howver, if you really want to "skip" every 10th reocrd that is left over,
then will have to break down and build a record set.
( I was trying to avoid this, and save code).

Hence, we can build an actual record set (as the other posters suggested)

I think this approach is a reasonable way to go, especially if we're only
dealing with three fields.

the additonal code for the 2nd part could be:

Dim rstAppend As DAO.Recordset
Dim rstRandom As DAO.Recordset
Dim lngRecordPtr As Long


strSql = "select [postedamount], [reportname], [sapcompanycode]" & _
" from tableAppend " & _
" where (not ( (postedAmount >= 3500) or " & _
" ( (sapcompanycode in (110,118,185,514)) and (postedamount >= 1500) ) ))"

Set rstAppend = CurrentDb.OpenRecordset(strSql)
Do While rstAppend.EOF = False
rstRandom.AddNew
rstRandom!PostedAmount = rstAppend!PostedAmount
rstRandom!ReprotName = rstAppend!ReportName
rstRandom!sapcompnaycode = rstAppend!sapcompnaycode
rstRandom.Update
rstAppend.Move 10
Loop

notice in the above I simply put a "not" command in front of the SQL
criteria. -- also keep in mind the above is a bit of air code but I think
it's pretty close.

I have to give credit to the other poster, as he understood your second code
loop perfectly, and posted code as such -- my apologies for missing that
code....

 

[Albert D. Kallal]

So could i get some help with that

?? not sure what you reffering to?

so having included all the records that met the first two criteria, you
would be choosing every 10th, 20th, 30th, record from a list that didn't
include the previous criteria, and where the reportname field didn't have
the
text "mile" "mileage" or "Mlg" in it.

prettry much what the code I posted should do.

Often a nice solution is to provide a combo box with the limit to list set =
yes.
That way, the user only has legal choices in the list. This approach means
no code is needed to verify what the user types in, because their choices
are limited in the first place.

The only question here is do you want to create a table with the list of
legal choices, or simply add the 3 values as a "list" when using the combo
box wizard. If you *never* (famous last words) will have more then 3 choices
for that field, then you can use a list, but I almost *always* use a table
for pick lists for a user (since then you can add to the list for legal
values, and not have to write/change code).