Passing A Single Row To A Fonction

May 31, 2006

Hello,

I have a table on which i must work at row level, one by one.
I put the table into a recordset and i scroll the table row by row from the top until the bottom. The work process - a test - is executed for each row and returns an integer.
I would like to put the process into a function and simply call the function when i move to the next row.

I defined : Function TestScore(ByVal MyTable as Recordset) as Byte

As it is so slow and the results are not always correct, I wonder if the WHOLE table might be passed to the function, and not only the current row.

Does anyone has an idea on passing a single row from a recordset to a function ?

Thanks

View Replies

Modules & VBA :: Storing A Single Record In A Single Dimension Array?

Sep 8, 2014

Any Single line of code available?

View 2 Replies View Related

Modules & VBA :: Sending A Single Email To A Single Record In A Query

May 15, 2014

I am trying to create a form with a button attached to each record that would allow the user to click the button and it would automatically open outlook and fill in the TO:, SUBJECT: and BODY: fields. Here is the code that I currently have:

Code:
Private Sub Command33_Click()
Dim strEmail As String
Dim strMsg As String
Dim oLook As Object
Dim oMail As Object
Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.CreateItem(0)

[code]...

There are two issues I keep running into:

1. This code opens outlook and populates all of the fields but pastes the email incorrectly. Instead of pasting just the email (email@email.com) it pastes the html tags as well (email@email.com#mailto:email@email.com#) which means that the user would have to delete everything between the #'s in order to send the email every time.

2. I currently have the email BODY pulling from a table but this obviously limits what I can do. I would like to simply encode the BODY within the VBA code. The setup I am looking for is:
one paragraph
a blank line
a hyperlink to a website
a blank line
another paragraph

View 9 Replies View Related

Max Value For A Single Date/multiple Time/single Tag ID Combination

Oct 18, 2007

Hi, thanks in advance for any help you can offer. I've got a table that has

Date
Time
Tag ID
Power Level

throughout the day a computer listens to several tags (transmitters) and records the power level of the signal generated by the tag each 3 seconds. What I'd like to do is build a query that gives the Date, Time and Maximum Power level reading for each tag ID. I only want 1 record per tag per day

I've tried using "group by" and max in the query but this gives me all the times throughout the day.

anyway, thanks again for looking

cheers, Shaun

View 2 Replies View Related

Argument Passing

Sep 18, 2006

I have a statemtn like this

DoCmd.OpenQuery("UPD_RECEIPT_IMP")

When I run the above, it pops up the input box for user to key in the argument.

How can I stop this and pass the argument to the query in macro

Need help on this.Thanks...

View 1 Replies View Related

Passing Value To A Sub Form

Feb 26, 2005

Hello,

I have a combo box on a main form and one on a subForm (DataSheet View)...

I want the value in the main_combo to populate the sub_combo for every record in the subform.

I think the code would look like this, but I cant get it to work?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub MAIN_DUNS_AfterUpdate(Cancel As Integer)

Me.CLIENT_DEAL.SUB_DUNS = Me.MAIN_DUNS.Value

End Sub

MAIN_DUNS= Combo on main form
SUB_DUNS= Combo on sub form
CLIENT_DEAL= Sub Form
DEAL_INFORMATION=Main Form
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Any Ideas on how to get this to work correctly??

View 1 Replies View Related

Passing Criteria

Nov 16, 2004

I am using Office/Access 2003. I have created a report that functions fine within access. The report is based on a query requesting the users Representative ID. It functions as expected. We then creayed a web page using the same query. Again this works quite well. The user clicks on the link - Access pops up the criteria dialog asking for the rep id. What our problem is that we are trying to determine where we can put the Rep ID in the page so it will automatically fill in the Criteria Dialog. We see no place in the access page where the criteria can passed. We have the criteria because they use it when they sign in to the sight. I can pass it as a hidden field, an application variable, or even a server variable but how do I get the web page to see it and stuff it into the criteria dialog.

Frustrated in Tucson! Thanks for your help in advance

Greggers

View 2 Replies View Related

Passing Value In One-to-many Relationship

Apr 26, 2005

In my accounting database, I have a form for the requisition and a subform with the line items of the order. When a user has completed their changes, they click a button which closes the form and changes a value in the main table (as in the requisition status is now "submitted" or "approved"). Now I'd like to pass that status value to the line items in the sub-form (based on a different table, of course).

I have another subform that displays the requisition record id, the line item record id, and the status for the line item. In the On Current field of that subform, I pass the value from the main form to the subform. The subform is requeried when the user pushes the button that changes the requisition's status.

The problem is when I have more than one line item for a requisition. The first line item always receives the requisition's status value. Any other line items are not updated. Naturally, if I scroll through the records, they refresh and their status is correctly updated. But this subform won't even be visible to the user, and I certainly don't want them to have to scroll through it anyway. Is there anyway to pass the value to each of the records instead of just the first?

Here is the code I'm using in On Current in my status subform:
Me![process_status_rec_id] = Me.Parent!req_process_status_rec_id

Thanks for your help!

View 2 Replies View Related

Passing Values

Apr 25, 2005

I need to find a way of passing a value from an Access form to an .asp page...

Page1.asp is viewable on the web and asks a user for a code and password - these are checked against a database and when a match is found page2.asp is opened showing values based on SQL views which match the criteria from page1.asp.

What I want to do is be able to open page2.asp from a button in Access - the button should therefore force the opening of page2.asp in a web browser and pass to it the values from that record on the database (code and password).

What code would sit on the button to get it to do this?

View 1 Replies View Related

Passing Parameter

May 23, 2007

i have a working stored proc and a working access pass through query

the below is working in access
exec appcheck @myapp='1'

but i want to point directly to a form

ive tried this but with no success
exec appcheck @myapp=[forms]![frm_test]![pass]

Also i would like to know how to return the results directly into a table?

View 2 Replies View Related

Passing Each Record To A Function

Aug 1, 2005

I have written a simple module that sorts and compares two strings. Next I am trying to create an event procedure for a button that will send all the values from three selected colums to that function for comparison.

Below is function "Check" from the module

' sImp = importData
' sAns = answerData
' iOpt = option

Function Check(sImp As String, sAns As String, iOpt As Integer) As Integer

Dim lImp As Long 'Imported values (unsorted)
Dim lAns As Long 'Entered values (unsorted)
Dim lAcnt As Long
Dim lCcnt As Long
Dim sAnswer As String 'Correct
Dim iMini As Integer
Dim sImpSort As String
Dim sAnsSort As String

lImp = Len(sImp)
lAns = Len(sAns)

sImpSort = OrderString(sImp)
sAnsSort = OrderString(sAns)

For lAcnt = 1 To lImp
sAnswer = Mid(sImpSort, lAcnt, 1)

' option
Select Case iOpt
' all
Case 1
Check = 1
Exit Function

' or
Case 2
For lCcnt = 1 To lAns
If Mid(sAnsSort, lCcnt, 1) = sAnswer Then
Check = 1
Exit Function
End If
Next

' and
Case 3
If sImpSort = sAnsSort Then
Check = 1
Exit Function
End If
Case Else
End Select

Next

End Function

What would be the best way to send these values to the function? loop through a recordset?
(Note: Not sure how many records the DB will eventually store. Lets just say 10,000 for now)

Any suggestions would be greatly appreciated..

Thanks

salmonman

View 1 Replies View Related

Passing A Variable's Value Into An SQL Statement

Feb 6, 2006

I want to build an SQL statement in code which includes the value stored in a string variable where the variable name includes a loop counter.

This is a much simplified example of what I am trying to do:

Dim i as integer
dim Strtable1 as string
dim Strtable2 as string
dim Sqlstring as string
...

Strtable1 = "tblEmployees"
Strtable2 = "tblSales"
...
Sqlstring = " select * from ... where....."

for i = 1 to 10

DoCmd.RunSQL "INSERT INTO " & StrTable & i & sqlstring

Next i

I am not sure how to get the table names stored in the string variables into the SQL statement. When I try the above it looks for a variable named StrTable, not StrTable1, StrTable2 etc.

View 4 Replies View Related

Passing A Variable From One Form To Another

Mar 5, 2008

how can i pass a variable from one form to another

this works fine
Tracking_Number = Forms("Main").Control("mytext")

but if "mytext" was in vb (rather than from a text box as shown above) how can i do this

View 4 Replies View Related

Passing Data Between Tables

Feb 8, 2008

I have a bit of a dilemma in my related tables (screen shot of relationships attached).

Basically at the moment I am using a 1-1 relationship between my tables using the primary key of CYID. The CYID is populated using an auto number at the moment but the problem I have is if the tables become out of sync with the numbering.

What I would like to do is when I create a new record in tblClientDetails is create a new record in each corresponding table that is linked and then pass the CYID across to each table. Can anyone offer any assistance or give me any suggestions for better ways of doing this?

View 2 Replies View Related

SQL Server - Passing Parameters

Jun 15, 2005

Hi,

After years and years of working with Jet, I am now trying to use SQL Server backands and am having problems passing parameters to a stored procedure.

How can I build a query which where I can say "WHERE ([AField]=""" & Forms!BlarBlaretc.value & """)" as I can in access because as I understand it you can not look at front end information from the SQL backend.

Any help (and start at the beginning) would be much appreciated.

Cheers

Stu

View 1 Replies View Related

Passing Criteria To A Query

Dec 6, 2005

Hi

I have recently started using queries to base my forms on. Even for basic forms i am using the query as the record source.

If for example i have a standard query with no criteria e.g. a employee form. How would i then use that query to create a report that filters for example a list of employees for a certain company?

Would i have to create a seperate query to base the report on? because the form when opened will always filter that criteria?

If this is the case, is there a way to keep a query set up with no criteria but when the object being used is opened some code will pass to the query the criteria e.g. a combo box on a form list all the companies and when one is selected it passes this to the query e.g. without the criteria already being in the query?

Hope that makes sense.

thanks

scott

View 1 Replies View Related

Passing Parameters To A Query

Feb 1, 2006

Guys I need your help/Advice...

In my Access Database I have a query (lets say qry1) and in this query i have 2 fields for start and end date, which is provided by 2 Get functions.

also i have qry2 based on qry1
then qry3 based on qry2
and finally qry4(using sql in code) based on qry3, and non of these 3 queries have the start and end date fields.

now here is the problem: I am trying to set the criteria in qry4 and then open a record set on this query(qry4) to use the data that it pulls out...

strsql = ""
strsql = "SELECT Sum AS AREA_TOTAL " _
& "FROM qry3 " _
& "WHERE ENERGY_AREA like '" & Area & "';"

Set MyDB = CurrentDb

Set rst = MyDB.OpenRecordset(strsql)

but when the last line is executed I get this error message:

Runtime Error '3061':
Too few parameters, expected 2.

i also tried doing this:

strsql = ""
strsql = "SELECT Sum AS AREA_TOTAL " _
& "FROM qry3 " _
& "WHERE ENERGY_AREA like '" & Area & "';"

Set MyDB = CurrentDb

MyDB.QueryDefs("qry4").sql = strsql

Set rst = MyDB.OpenRecordset("qry4")

but when the last line is executed it gives me an error message saying that the query does not exist or the name is not spelled correctly. (Ps. I have created the query and the criteria does update once the Select statement is run in the code!)

again the reason for this is that the query has not been populated as the main query (qry1) needs 'strat date' and 'end date'!

Is there anyway I can pass these 2 parameters to qry4 directly using code? If there is a way then this will definitly work as i tried opening the query manually in the Query window and after I input the 2 dates in the input box the query ran successfully!

I would appreciate any help/suggestion guys, I need to sort this out quickly as i have a deadline... Cheers

View 1 Replies View Related

Passing A Criteria As A Value From One Query To Another

Apr 11, 2006

I have a combo box in a form which allows users to select a Client Group. One of the choices in the combo box is ALL.

I have some code in a STEP1 query that says
SELECT IIf(Forms![Date Picker].[Client Group]="ALL","'SEDP' Or 'LD' Or 'MH'",Forms![Date Picker].[Client Group]) AS Expr1
FROM MPI_PERSON
GROUP BY IIf(Forms![Date Picker].[Client Group]="ALL","'SEDP' Or 'LD' Or 'MH'",Forms![Date Picker].[Client Group]);

I have a STEP2 query to select all records on the MPI_PERSON table and do some slight processing.

Now, in a STEP3 query, I want to select all records from the STEP2 query where the field [Client Group] satisfies the STEP1 criteria. If the Client Group chosen in the Combo box is one of the existing categories i.e. SEDP or MD or LH, it works but where the ALL has been entered, it is returning no records in the final query.

I have tried pasting the results of the STEP1 query into the criteria box as a test - and in that case it does return all records I want. What do I need to do to get the criteria recognised in the QBE box - do I need to use Eval or something?

Here is the SQL for the final query
SELECT [Find all Clients STEP 2].[Paris ID], [Find all Clients STEP 2].DOB, [Find all Clients STEP 2].[DOB Estimated], [Find all Clients STEP 2].Gender, [Find all Clients STEP 2].[Status From Date], [Find all Clients STEP 2].[Status To Date], [Find all Clients STEP 2].STS_MAIN, [Find all Clients STEP 2].Title, [Find all Clients STEP 2].Name, [Find all Clients STEP 2].NAM_FROM, [Find all Clients STEP 2].NAM_TO, [Find all Clients STEP 2].[Client Group], [Find all Clients STEP 1].Expr1, Len([Expr1]) AS Expr2
FROM [Find all Clients STEP 2], [Find all Clients STEP 1]
GROUP BY [Find all Clients STEP 2].[Paris ID], [Find all Clients STEP 2].DOB, [Find all Clients STEP 2].[DOB Estimated], [Find all Clients STEP 2].Gender, [Find all Clients STEP 2].[Status From Date], [Find all Clients STEP 2].[Status To Date], [Find all Clients STEP 2].STS_MAIN, [Find all Clients STEP 2].Title, [Find all Clients STEP 2].Name, [Find all Clients STEP 2].NAM_FROM, [Find all Clients STEP 2].NAM_TO, [Find all Clients STEP 2].[Client Group], [Find all Clients STEP 1].Expr1, Len([Expr1])
HAVING ((([Find all Clients STEP 2].[Client Group])=[Find all Clients STEP 1].[Expr1]));

Hope there isn't too much "Social Services speak" in there - I can also strip down non-crucial fields if it makes it easier to follow what I'm on about.

View 4 Replies View Related

Passing Variables To Queries

Apr 11, 2006

Hi, I am writing a script which will retrieve all of the tracks relating to whichever cd the user has chosen. The script is being written in asp and the line which sends the variable to access looks a bit like this:

sqlQuery3 = "up_getAlbumTrackInfo " & productID
Set rs3 = dbConn.Execute(sqlQuery3)

If possible could you tell me how to retrieve this value from access as I have become lost.

At present the SQL code in the query looks like this

SELECT tblTracks.trackName, tblTracks.TrackNumber FROM tblTracks
WHERE tblTracks.productID = (** variable would go here **)

Many Thanks

Tim

View 1 Replies View Related

Insert Query (passing Nothing)

May 9, 2006

I have an insert query that works fine when all fields are entered:
INSERT INTO tblMaster (Fname, Lname, Address, Address2)
Values ('Cozmo', 'Kramer', '1Main', '2Main');

BUT...if I have Address2 as blank b/c its an optional field I get a validation rule error...even though there is no validation set up on this field in the table.

INSERT INTO tblMaster (Fname, Lname, Address, Address2)
Values ('Cozmo', 'Kramer', '1Main', '');

Please let me know if you know why this would be.

Thanks,
Kbreiss

View 2 Replies View Related

Passing Value From Form To Report

Jun 20, 2007

Hello,

I have made one form for report purpose. When I enter Start date and End date, I get report of the date range. What I am looking that I want to print the start date and end date in my report. How can I reference date text box in my report?

thanks

mithani

View 7 Replies View Related

Passing Parameter To Query

Apr 11, 2008

Hi all,

Using Access 2003. Really need some help. Have the below code which executes a SELECT query and passes the results as an excel spreadsheet attached to an email. Problem is that the query store_report has some parameters that need to be passed to it. Is there a way that with my current code I can pass those parameters. If not is there some other commands or code I can use to accomplish this?

DoCmd.SendObject acSendQuery, "store_report", acFormatXLS, _ "john@john.com", "c.w.k@gmail.com", , _ "Store Reports", "Here are your reports.", True

Thaniks in advance.

William K

View 1 Replies View Related

Passing ID Number To Another Form

Feb 16, 2005

i have two forms. one is the main form and there i put button that opened another form.
when i open the second form i want to link the ID number from the main form to the second form so i wont have to insert the number each time i am opening it.

View 1 Replies View Related

Subforms - Passing Name To A Function

Feb 24, 2005

Hey long time browser, first time poster!

HELP!!!

Ok, here is the problem that I can't seem to find any posts on here talking about. I have a form, let's call it FormA and a subform (subformA). I am using a non-activeX calendar control, which I did not write. I was informed it could be used on subforms but its not working. In reviewing the code it is looking for the activeform name to be passed to it. This works when the calendar function is called in the FormA, but when it is called in SubformA, it can identify the field that I want the date selected to be entered into, but it does not recognize the subform as being the active form.

The code it's using to determine activeform name is:
Screen.activeform.name

From my research I have found that when the subform has the focus, the activeform.name returns the name of the parent form.

The question: How can I determine the subform name and pass it as a variable to the calendar program?

Mark

View 1 Replies View Related

Passing All Records To Subform

Mar 30, 2005

I have a form called chartinfo which contains a subform called testgroup.
For ever new record in the main chartinfo form I need the testgroup subform to display all records from the testgroup table.
see attachment it is access 02

Thanks
Murlen

View 1 Replies View Related

Passing Date To A Query

Jul 31, 2005

My form has a date field (with a default of today-105 days)( and can be updated by the user) which is part of the following query

SELECT dbo_CM_AS_LG.AS_LG_DT, dbo_CM_AS_LG.AS_LG_WO, dbo_CM_EQ_MA.EQ_MA_ID, dbo_CM_EQ_MA.EQ_MA_DE, dbo_CM_EQ_MA.EQ_MA_PS
FROM dbo_CM_AS_LG LEFT JOIN dbo_CM_EQ_MA ON dbo_CM_AS_LG.AS_LG_TP = dbo_CM_EQ_MA.EQ_MA_NO_P
WHERE (((dbo_CM_AS_LG.AS_LG_DT)>[Forms]![F_Q_RISC_SWAPS]![SearchDate]) AND ((dbo_CM_EQ_MA.EQ_MA_DE) Like "RISC/6000*" Or (dbo_CM_EQ_MA.EQ_MA_DE) Like "ibm power pc*"))
ORDER BY dbo_CM_AS_LG.AS_LG_DT;

The query is thr root query for the form, but when the form opens the default date has not been set and I get an error.
Is there a good way to pass a parameter into a query ?
I have tried running the qury using the ON Open Property too, which works until I try and call this form from another one.

I'm sure there's a simple answer, I just can't see the wood for the trees (yet)

Cheers

View 1 Replies View Related