I would like to transmit several parameters from my main form to the subform when I open it.
Parameters could be used in the subform by onload event, for eample, to define form caption, hide or show buttons, enable or disable edit boxes, give them a color, ... It could also allow to use the same form in different application contexts, which would reduce so the development time.
More than one technique might be possible.
I used the following one :
- I put the parameter value I want to pass into myParameter
- when click on the button to open the subform :
DoCmd.OpenForm myDocument, acNormal, , , , , myParameter
- when loading the subform, retreive the parameter value in Me.OpenArgs and using it (eg disable a button)
It works fine. But I can pass only a single value with this technique and it's not enough. I tried to give an array as myParameter and fill it with my parameters values, but Access refuses an array to be in the DoCmd statement for OpenArgs option.
Any idea about passing several parameters to the subform ?
Or may be an alternative to the DoCmd technique ?
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.
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 continuous form that is bound to a SQL Server view.
For each record in my form I have a button, which when pressed opens up a second form. The second form is bound to a stored procedure that takes a parameter. The parameter value that I want to pass to this second form is the value of one of the fields in the first form.
I did the following in the click event of the button on my first form:
Dim stDocName As String Dim stLinkCriteria As String
However, when I run this it keeps prompting me to specify the value of Field1 so this value is obviously not getting through. Do you have any idea why this might be happening?
I am trying to create a form where a user will enter in a value into a text field. Afterwards, when the user clicks "Enter", a query will run and will LOOK FOR THE VALUE THAT WAS ENTERED INTO THE TEXT FIELD. i.e. User enters their address into the field and clicks the enter button. Afterwards, a query will run like select * from customers where address = @address <== the value the user entered into the text field. This is where the mystery lies. How do you pass values?????
I would like to have a user enter a start date and an end date into two textboxes on a form. The two dates will be used to query a table. I would then like to print a report that was created from that query.
Here is the query created as a stored procedure:
SELECT Transactions.*, Hoods.* FROM Hoods INNER JOIN Transactions ON [Hoods].[ID]=[Transactions].[BoxID] WHERE ([Transactions].[Date] Between [@StartDate] And [@EndDate]) ORDER BY [Transactions].[Date];
What would be the best way to pass txtStartDate to @StartDate and txtEndDate to @EndDate in the VBA code of the form? How would I open or print the report created from that query filtered on that date range?
Any suggestions? Am I going about it wrong? Should I have created the report from the above query, or should I do it another way? Can anyone direct me to some code that does all of the above or something similiar?
The report is bound to a query that has 2 group by fields, 1 count field a a further field, a date field ([Part Date], that I put a default criteria on. This field is not displayed. If I don't put a criteria on this field disappears when I close and open again.
I pass a date to the program via a form and this ultimately ends up in SQLDate. When I run this I get promted to enter [Part Date] even though I'm setting it equal to SQLDate above. I can out garbage to a proper date in here either way the report picks up the default date entered in by the query.
1. Get rid of all of the parameters off the query.
2. Then you can use the Where Clause of the DoCmd.OpenReport code to specify the parameters based on your variables.
1.Not sure what this means but when I get rid of the criteria for the parameter the field disappears (I'm setting the show field to no as I don't want totals group by date). Getting rid of the field gives me all parts used.
2.I think I'm doing this in the above but will bow to superior knowledge!!
or is it I can't pass a parameter to a report run by a query that is grouping fields together to produce a count.
Incidentally once the report has been run (albeit with the wrong parameters) and I go into design mode and look at the property sheet for the report the correct filter is there (i.e., the date that has been input) but it quite clearly ignores this.
I have a navigation form that will have 6-8 tabs. We were using about that many databases, but we are finally consolidating them into one. The result of us using so many databases has been the multitude of forms and reports that were necessary for each database prior to merging them together.
The problem: There will be anywhere from 12-20 (text boxes) that the user can use to search anything in our database. What we need to have happen, if possible, is for those search parameters to show up in the header of our report if they have text in them. If the text box is blank, it should not show up in the header of the report.
I have read how to to do the start/end date technique, but I do not know if that would work for what we are doing since the boxes would only show up if they are populated by the user.
I have a query that searches for records that are between two dates using the WHERE clause. The two dates are referenced to two respective text boxes on a form. On the same form I have a button that will launch the query in VBA using querydefs. I get the error 3061 saying I need to input the parameters. Therefore I am a bit unsure how to set the parameters in VBA. So far I have done this:
Code:
Dim Db As DAO.Database Set Db = CurrentDb Dim QDef As DAO.QueryDef Dim rst As DAO.Recordset Set QDef = Db.QueryDefs("Rqt_F_BrokerageMandate_MF3_TEST")
[Code] ....
Where Date_VL is the field to be filtered. I know this is wrong but all examples I have seen have equated the parameter to a fixed value i.e 30/12/2012 for example, but I want this to be at the users discression. The only way I know of to get around this at the moment would be to write a temp query in VBA with PARAMETERS in the SQL code instead using the method above/
After this I'm going to assign the recordset to a matrix but that's a different story!
I am trying to pass parameters to my qury thru my combo selection. I keep getting this error "Data type mismatch criteria expression", does anyone have an idea why? WHERE (((fShiftWorked([tblTimeLog].[timeStart])=[Forms]![frmOperatorWorkDone]![cboShift] Or IsNull([Forms]![frmOperatorWorkDone]![cboShift]))=True));
I have spent so much time onthis already and i am sick of it :mad:
I know how to use openargs to pass values between forms but im not sure how to go about passing all the values from one subform to another.
To be specific I have an order form (frmCustomerOrders) that has fields like CustomerName, EmployeeName, OrderDate and so forth, the subform (frmCustomerOrdersSubform) contains information on the Products the customer is ordering which usually has multiple records with fields like ProductName, PartNumber, Quantity and ItemNumber. I want to pass the values from the CustomerOrdersSubform form to another subform (TransferSubform) My initial thought was to use something like the openform command through openargs but im not sure if that will work or if there is another method I should use.
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
I got a little problem wich i'm trying to solve for several days now. My situation is like this. I got a form called "Frm_File_List" and in there a subform "Frm_List_Sub" as in a datasheet. In here there are several records. Im trying that when u double click on the record that a new form "Frm_View_file" will be opened, but according the record I selected in the subform. So when I doubleclick on ID 7 it opens the form with record matching ID 7.
I tried in vba the following
Private Sub Form_Click() DoCmd.OpenForm "Frm_View_File", , , "Files_ID = Forms!Frm_List_File!Frm_List_Sub.Form!Txt_Files_ID " End Sub
But when I double click on the record it gives me this popup screen asking for the ID. When I fill in this manually then it does give me the wanted record in the form. But how do I get his ID passed automaticly?
I am in no way shape or form familiar with access beyond the very basics. I am however the house programmer and some of the employees here like to create access forms (no programming knowledge though). So from time to time I am asked to help out with the more difficult tasks.
The issue I have run into is that we built a form(that has no connection to a table or query) that has a subform on it (that is connected to a table). Basically the subform information is used to help fill out the form which is then printed.
Problem is that when the form is printed all of the records of the subform are also printed. I am struggling with finding a solution.
Is there a way to just have the subform print the current record shown?
I tried having the form grab the current record from the subform to print the info out on the main form but I can't seem to get the two to talk. How do I use subform info on the form? I have tried something like Me.mysubform.control.text or [Forms]![Me]![mysubform].whatever but nothing seems to work.
One work around I am currently trying to implement is to have the subform visibility set to false which allows me to just print the main form. Problem is that I need the info off the subform also. I try to say something like myformlabel.caption = mysubformtextbox.text but nothing seems to work.
Any help or direction would be greatly appreciated.
I will have a ListBox on the form with “Multi Select” set to either simple or extended. I have a collection of documents which must be recorded in a database. Some documents may have only one author, but could have more. Same with the recipient or copied-to.
Ideally it would be nice to have the ListBox on the left, selected one or more from the list and then pass them to one of the three fields by a command button, sort of like:
I would like to have all of the names selected from the ListBox as a string, but fully understand the problems associated with normalization. Other posts have suggested subforms. So, the question is how to select from the ListBox and then pass the possible multiple selections to discrete fields on a subform that would display, say three fields and which would get away from the normalization issues:
The secondary issue will be that there will be subsequent names which are not found until actually in the process of document review which will create a need to update the ListBox, which I know is not akin to a ComboBox NotInList function.
If there are any thoughts or coding out there which will help, it’d be appreciated. Lawguy
Hi--I am working on a database which is a modification of the following MS Access 2003 Order Management template: (URL address blocked: See forum rules)=CT101426031033
In my database, I have a subform called sbfOrderDetails, which in its Form Footer has a text box that sums the items in the order and is called OrderDetailsTotal.
I am trying to pass that value to a text box in the main form called OrderSubtotal.
I have three questions:
1) What is the proper syntax to use to get the value of the subform control into the OrderSubtotal control on the main form? I have tried various permutations and continue to get a #Name? error.
2) If you look at the expression in the Order Subtotal control of the Add and Manage Orders form in the original Order Management template, it looks like this:
=[Order Details Subform].Form!OrderDetailsTotal
When I try to replicate that in my database, I cannot--if I add brackets around my subform name and press Enter, Access adds brackets around everything in the expression, i.e. =[sbfOrderDetails].[Form]![OrderDetailsTotal] (which returns a #Name? error). If I edit out the brackets I don't want and then press Enter, it puts the brackets back in. In other words, Access keeps trying to "fix" my expression by either having brackets around each item, or no brackets at all! This is really annoying; is there any way to turn it off??
3) When sbfOrderDetails is set to Single Form view, I can see the Form Footer (and the value in the OrderDetailsTotal text box as well). When it is set to Datasheet view, I cannot. Why?
Thank you very much, lcnlit
PS>I cannot attach my database since I am a new poster, and the forum is disabling the URL of the Microsoft template as well.
There is a command button on my form that calls a filtered query. I'm filtering the query based on a combo box within the form using
[Forms]![frmAssignDwgs]![Combo15]
It works beautifully until I put the form as a subform in the Navigation Form I'm using. When I execute it from within the Navigation form, it errors out looking for [Forms]![frmAssignDwgs]![Combo15].
Is there another syntax I can use so this will still work from within the Navigation Form? Or another way to approach this functionality?
frmNavigation is the main navigation form frmAssignDwgToPkgs is the subform that holds Combo15
I found another thread (which I can't link to since I'm new) and tried various arrangements of the following to no avail.
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?
