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 ?
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
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.
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
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
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?
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.
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
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)
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.
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?
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.
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?
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
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.
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 **)
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.
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?
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
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.
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?
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
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)
