Some help, please with code

G

Guest

Oh. umm also appending every tenth record that didn't fit any of the
previous criteria (so we don't have to form a new recordset. i'd rather do
it from code than go through the trouble of building a new list of items and
then picking every 10th. that doesn't make any sense when a loop with some
restrictions will do the same thing with less overhead)
 
G

Guest

Should i just try to combine parts of what wolfgang has?
or am i just not being patient enough :)
 
W

Wolfgang Kais

Hello "mtrimpin24".

mtrimpin24 said:
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?
Click to expand...

Sorry for the wrapping, I thought that I had cut it correctly...
the "dbAppendOnly)" belongs to the previous line.

Probably the field names in the table whose name you have specified
do not match "postedamount", "reportname" and "sapcompanycode".
When "debugging" the code after the error occurs, you can type
?strSQL
in the debug window, this will show you the sql statement that is to be
executed. You can copy and paste it to a new blank query and try to
execute that query to see what's wrong with it.
 
A

Albert D. Kallal

Should i just try to combine parts of what wolfgang has?
or am i just not being patient enough :)
Click to expand...

Sure using wolfganes code, or simply add that 2nd code snip
I posted to the original code. (I kind of thought that was obvious
to a developer).

By the way, if your experienced in C++, and you have visual studio 6, then
why not just add a DAO reference to a c++ project, and write the code in
c++?

You don't have to use ms-access, you can *well* use the JET database engine
from c++ application.

The "dao" object model we use is a windows "com" object, and it works well
when used from c++. You will see the same methods and properties we are
using when you use dao in c++.

In fact, you can use any "com" aware development platform to write this code
(so, even Delphi would work).
 
A

Albert D. Kallal

mtrimpin24 said:
Blah. Albert's abandoned me. someone help :)
Click to expand...


No, still here...just taking a 5 minute coffee and lunch break...and I be
back to working on my project....

So, just a *very* busy person.....

Do continue to ask questions...I do my best to answer them....I likely not
be back on this board till later this evening...after supper, but I will be
back!!!
 
G

Guest

Several reasons -- one, i'm not familiar enough with those applications to
try for an inter-relationship between the two. I know c++ quite well. DAO
not at all, i don't use it for that. VBA, i knew at one point in my youth
but no longer. I just wanted to make sure i would be able to get it to run
with little fiddling or problem time debugging. I'm about to be hired into
another department, i won't be around to fix any problems that crop up, i
have to make sure its as "foolproof" for loser-user as its going to ever get.
 
W

Wolfgang Kais

Hello Albert.


Albert D. Kallal wote:
Sure using wolfganes code, or simply add that 2nd code snip
I posted to the original code. (I kind of thought that was obvious
to a developer).
[snip]
Click to expand...

Sorry for jumping in:
You forgot the "mileage" criteria in the second loop.
 
M

mtrimpin24

Hope everyone in the US had a great Thanksgiving. Here's the continuation of
my last question:

Albert this is what i have now, and it has a couple problems.
1. it actually asks me for the excel spreadsheet twice. why do we ask for
the name of the excel spreadsheet after doing the import?

2. I get an error at line CurrentDb.Execute strSQL
it says it expects 4 arguments?

3. on the front end of my database i'll need a piece of code that links to
a checkbox. if the user clicks the checkbox to true, the record is removed,
and replaced by the next valid record on the list, using the same rules we
used before to import the data in the first place. The point of this is that
if the user discovers that somoene mispelled "Mileage" (someone inevitably
will) that they can reject the record on that basis, and get another one. i
dont know how to do this front end to back end interaction.

4. Here's the code we have so far:

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],[SAP Company Code])" & _
" select [postedamount], [reportname], [SAP Company Code]" & _
" from tableAppend " & _
" where ( (postedAmount >= 3500) or " & _
" ( (sapcompanycode in (010,528,185,113)) and (postedamount >= 1500) ) )"

CurrentDb.Execute strSQL
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 (010,528,185,113)) and (postedamount >= 1500) ) ))" _
& (Reportname <> "mile" Or "mileage" Or "mlg")

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



End Sub
 
D

Douglas J. Steele

mtrimpin24 said:
1. it actually asks me for the excel spreadsheet twice. why do we ask for
the name of the excel spreadsheet after doing the import?
Click to expand...

Your code has

strFile = GetExcel()
strFile = InputBox("Enter excel filename")

Remove the second line.
2. I get an error at line CurrentDb.Execute strSQL
it says it expects 4 arguments?
Click to expand...

Is it saying 4 arguments, or 4 parameters? If 4 parameters, are you sure
that you spelled the names of all of the fields correctly? Also, is
sapcompanycode perhaps a text field, not a numeric one? If so, change

sapcompanycode in (010,528,185,113)

to

sapcompanycode in ('010','528','185','113')
3. on the front end of my database i'll need a piece of code that links
to
a checkbox. if the user clicks the checkbox to true, the record is
removed,
and replaced by the next valid record on the list, using the same rules we
used before to import the data in the first place. The point of this is
that
if the user discovers that somoene mispelled "Mileage" (someone inevitably
will) that they can reject the record on that basis, and get another one.
i
dont know how to do this front end to back end interaction.
Click to expand...

Sorry, but this isn't clear to me.

For what it's worth, with linked tables, the fact that you've got a
front-end and back-end really is pretty much irrelevant: you work with the
linked tables the same as if they were in the same database in almost all
cases.
strSQL = "select [postedamount], [reportname], [sapcompanycode]" & _
" from tableAppend " & _
" where (not ( (postedAmount >= 3500) or " & _
" ( (sapcompanycode in (010,528,185,113)) and (postedamount >=
1500) ) ))" _
& (Reportname <> "mile" Or "mileage" Or "mlg")
Click to expand...

This SQL is invalid.

& (Reportname <> "mile" Or "mileage" Or "mlg")

needs to be either

& (Reportname <> "mile" And Reportname <> "mileage" And Reportname <>
"mlg")

or

& (Reportname NOT IN ("mile", "mileage", "mlg"))

(Rember, too, to change the sapcompanycode line if you had to previously.)
 
Top