lets try a different angle

[reel knotty]

I have a continuous form with zip codes listed in each record. I would like to combine all of the zip codes in a single field. I can get it to pull the zip from the current record but not the rest. how would i:

[Zip]
next record
[zip]
and so on

the number of records can vary from 2 to 10

Thanks for your time,
Nate

 

[Rich]

We now have four threads on this topic, please stick to the original

 

[reel knotty]

sorry I am getting extremly frustrated!! I will clean it up.

 

[Rich]

I know, there is a way to alter the PrevRecValue function to enable its use in a query, but I'm not sure it'll help you

 

[reel knotty]

I think that would do it because then I could call the API from the query as well then its all down hill from there. (I think)

I have several ways I can get this data but I can't get any of them to work

 

[Rich]

Function PrevRecValc(KeyName As String, KeyValue, _
FieldNameToGet As String, Source As String)
Dim rs As Recordset

On Error GoTo Err_PrevRecValc

' The default value is zero.
PrevRecValc = 0

' Get the recordset.
Set rs = CurrentDb().OpenRecordset((Source), dbOpenDynaset, A_NORMAL)

' Find the current record.
Select Case rs.Fields(KeyName).Type
' Find using numeric data type key value?

Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
rs.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
rs.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
rs.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"

Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select

' Move to the previous record.
rs.MovePrevious

' Return the result.
PrevRecValc = rs(FieldNameToGet)

Bye_PrevRecValc:
Exit Function
Err_PrevRecValc:
Resume Bye_PrevRecValc
End Function

 

[reel knotty]

Ok Rich,
Got it all makes sense. but now when i debug it stops at "FindFirst" and tells me Method or Data member not found. which doesn't make any sense because it works in PrevRecVal. I do have the DAO 3.6 object library active.

 

[reel knotty]

That might work to Pat. See I have an API that I is returning miles. Currently on my form I have point to point miles for several stops on a trip. I would like to total the miles for the trip at the bottom of the page. So the 3 ways I can accomplish this is by

A) call the API from the query so I wouldn't be summing a calculated field in the form.



B) Sum the fields in the form via Code


C) create a field in the form that contains all of the Zip Codes in the order they appear on the form seperated by an *. then I can call the API again to get total route miles.


I appologize for the multiple posts. I know many message boards have experts that stay within there area of expertise (IE Froms) and don't check other threads. I was just hoping to get some extra eyes looking at it so I can get my Boss off my arse!

Thanks so much for your help.

 

[reel knotty]

Ok Rich,
I got the Method or Data member not found issue figured out. I had to change Dim rs As Recordset to Dim rs As DAO.Recordset.

But now I have a new issue. I think I am messed up with the Source. here is what i have in the module:

Function prevrecvalc(Trans As String, KeyValue, _
Zip As String, shipmentlookupwcode As String)

shipmentlookupwcode is the name of the query

and in the query i have:

Expr1: PrevRecValc([Trans],"Trans",[Trans],"Zip",[shipmentlookupwcode])


If we can get this to work I will have 100 virgins sacrificed in your honor!!

 

[Rich]

You've changed the argument names to match your object names, put them back as posted, it's too confusing
you also need a unique key ID, which is not Trans.
PrevRecValc([KeyID],"KeyID","fieldName","TableName")
that should of course have read sequential Key field

 

[reel knotty]

Trans actually is the unique value and Primary Key for that table.

Ok I tried to adjust according to what you posted and here is what I have now:

Function prevrecvalc(Keyfield As String, KeyValue, _
field to get As String, Tablename As String)


And the expr in the query:

PrevRecValc([keyfield],"keyfield","field to get","Tablename")

I think I am starting to figure it out, one more kick should clear it up (I hope) I can see you getting annoyed with me

 

[Rich]

I'm not, I always appear that way
PrevRecValc([Trans],"Trans","Zip","shipmentlookupwcode")

 

[reel knotty]

Ok let me make sure I have this straight...

The code should read:
Function prevrecvalc(Keyfield [trans] As String, KeyValue, _
field to getzip As String, Tablenameshipmentlookupwcode As String)

The red being the actual text after deleting the description.


where I am confused is what the source should be. Should it be the table that all of the zips are listed or the query I am trying to get the previous value for?



Thanks for your patience!!

 

[Rich]

No, the function declaration should read
Function PrevRecValc(KeyName As String, KeyValue, _
FieldNameToGet As String, Source As String)
in the query it's just

PrevRecValc([Trans],"Trans","Zip","shipmentlookupwcode")
where Trans is the keyID field, Zip is the field you want the previous record for and shipmentlookupwcode is the Table name

 

[reel knotty]

Ok I have eveything as listed above and its a bit more clear to me now but I am getting "undefined function 'prevrecvalc' in expression" error 3085.

Any ideas on this one?

 

[Rich]

It means access can't find the function since you've not typed it in correctly, open the code builder and it'll be in the list