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
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.
Hi,
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 ?
Thanks.
Hi
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?
Thanks in advance
Hello all.
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.
Using Access 2003.
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:
cmdAuthorAuthor1
LISTBOXcmdRecipientsRecipient1; Recipient2
cmdCopiedTo Copy1; Copy2; Copy3
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:
cmdAuthor [Author1] [ ] [ ]
LISTBOXcmdRecipients [Recipient1] [Recipient2] [ ]
cmdCopiedTo [Copy1] [Copy2] [Copy3]
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.
if you are in a subform, can you pass the subform as a form to a procedure.
eg
msgbox(me.name) 'shows the form name correctly, but then
call somefunc(me) doesn't seem to work.
I get a type mismatch in
function somefunc(frm as form)
E.g.
function somefunc(frm as object)
msgbox typename(frm) 'returns "Controls"
end function
I have a filtered form with a sub form displaying only non-printed invoices.
The sub-form has a check-box (which is how the form is filtered) to only show unchecked (not yet printed) invoices.
All i want to do is print only the invoices visible in the sub-form.
I've setup the report to link to a query and the report is all setup, i just can't get the query to function how i want it to!
I've got this SQL code for the Query.
Code:
SELECT tblOrderForm.OrderNo, *
FROM tblOrderForm INNER JOIN tblOrderDetail ON tblOrderForm.OrderNo = tblOrderDetail.OrderNo
WHERE (((tblOrderForm.OrderNo)=[Forms]![Export Orders]![ExportSub].[form].[OrderNo]));
However this only shows the field with focus. I would like it to display ALL data in the subform.
I have a form where I do a search and find the correct auditor in the listbox from the AuditorTable. This form is opened from a master form where I have the same fields in the table (ClientTable). What I need is that when I find the one, I double click it and the records from that auditor is passed to the client table.how to use Dlookup.
View 14 Replies View RelatedI 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!
In my simple database (attached), I need to mass duplicate Tasks and their Notes.
I have three tables: tbTasks (PK: Task_ID), tbNotes (PK: Note_ID), jtbTaskNotes (FKs: Task_ID and Note_ID). jtbTaskNotes is my many-to-many junction table that ties Tasks to Notes.
The main form (fmTasks), bound to tbTasks, has a subform (sbfm_TaskNotes) that displays notes associated with each Task. On themain form,you select which Tasks you want duplicated via a checkbox. The append query (quCopyTasks) will duplicate all tasks that have the checkbox checked. All good there. However, I can't figure out how to also duplicate each task's Notes.
I found Allen Browne's solution [URL] ....., but that only handles duplication of one record at a time, whereas I need to duplicate many records at a time (sometimes 10+ records). How do I go about duplicating multiple Tasks and their associated Notes?
Before you ask "why are you duplicating records?": There are times when tasks need to be re-accomplished and therefore need to have a new record. It's easier to duplicate records than it is to hand-jam everything again.
I am building a debt management program in A2k. I have created a tabbed control which contains 6 pages based on a set of queries. These pages are a budget template and the intention is that when this form is inserted as a subform into the customers form the budget will open as a blank template with the budget categories displayed. The clients budget will then be entered and saved. The Budget table contains 56 budget categories.
At the moment when I insert the budget form and use it the only records that change are the template. I want the first 56 records to remain with zero values so that they display as blank for a new client but I want the total 56 new records to have the customer ID associated so that when that client record is accessed again their budget will appear. I have attached the SQL code for the query that displays a sub portion of the budget.
SELECT Budget.BudgetID, Budget.ClientID, Budget.SectionNumber, Budget.BudgetTitlesID, Budget.Description, Budget.Cash, Budget.Credit, Budget.Frequency, IIf([frequency]=1,([cash]*52/12),IIf([frequency]=2,([cash]*26/12),IIf([frequency]=3,([cash]),IIf([frequency]=4,([cash]/12),0)))) AS Totalscash, IIf([frequency]=1,([credit]*52/12),IIf([frequency]=2,([credit]*26/12),IIf([frequency]=3,([credit]),IIf([frequency]=4,([credit]/12),0)))) AS Totalcredit
FROM Budget
WHERE (((Budget.ClientID)=[forms]![customers].[clientid]) AND ((Budget.BudgetTitlesID)=1)) OR (((Budget.ClientID) Is Null) AND ((Budget.BudgetTitlesID)=1));
My thought was to do an after update event that would copy the clientID from the main form to the sub form and populate all the records in the budget. However, I have only succeeded in populating the individual record that has changed.
I probably haven't explained the problem sufficiently to get some help but would appreciate any thoughts.
Thankyou
Ladies / Gents !
I am writing a database for my work environment to enter sample numbers in a predefine batch. My Form contains information to identify batch ID. Attach to this form is a subform which I would like to limit the number of records to 24.
exsample: form : as unlimited records
subform: only limited to 24 records within the master form.
Now, everytime i create a new record in the master form , I should be allowed to enter only 24 records in the associated subform.
Does anyone have an idea on how to accomplish this !! Your help would be greatly appreciated.
Thank You
Hi all
I have a subform which loads the items from a table the form is non editable just for viewing the items, the problem is how can i
A. limit the subform to only show the first say 10 items in the subform
B, to have a command button on the form where the the subform is on (command button not in the form) and when the user clicks it it goes to the next set of records say from 11 to 21 and displays that in the form.
i tried doing record cound but somehow record count is not there when selecting, also would the code go in the subfrom on load or on the form load where the subform is based?
any help on this would be appreciated.
thanks.
Hi, im having a problem with shring my db over a networkt,
as far as i understand access 97 locks a page (2k) of records when a record is being edited,
does anyone know a way in 97 how i can get access to lock ONLY the EDITED record and not the page?
in Tools/Options/Advanced i have "edited record" locking clicked and each form locks only the record edited but its still locking the page, i know there is "record level locking" feature in Access 2002 but not in 97, is this possible?
thank you
Scott
I have three tables;
tblPallet
PalletId
DateDespatched
TblBox
BoxId
PalletID
TblKitting
KITID
BoxID
Model
Process
The users will be adding a kitting item to a box. Only 10 items are allowed in a box (no more no less) and the box needs to have its own Id Number and then the box is placed on a pallet. 40 boxes are allowed on a pallet (again no more no less) and the pallet is despatched. Then a new pallet is set up.
I am trying to think of the easiest way to do this as I am a little new to Access. Any tips in the right direction would be greatly appreciated. I thought about a series of subforms from a main pallet form but am not sure how to introduce a limit to the number of records in a subform. Perhaps Ive bitten off more than I can chew (I hope not)
Many thanks
Hi,
Is there a way to hide a subform on a main form if there is no records in the subform?
Thanks
Hi,
I have several forms with subforms attached for data entry purposes. In a nutshell - this program is for an interview - where we need the respondent to give us a listing (say as to what intices them to join a study, etc.), but for some cognitive research method we need to be able to know the order they gave us the list in then we have them rank their list, thus the order may not be in the order they gave us the list. I hope that made sense. Anyway, when a form is called and loaded the subform always adds an extra record. Below is a screen shot of what I am talking about:
http://www.geocities.com/msfstl/ScreenShot/ScreenShot.JPG
So, not only do I get an extra record, but when I am entering data and get down to the new record line, the cursor will jump up to the top of the column. What can I do to prevent the extra record and also the cursor skip? I am thinking if I resolve one issue the other will fall into place.
Thanks,
Scot
I have a subform within a form and the subform has the child records for the main form. When I move from the main form to the subform by tabbing the records in the main form are saved. However, in the subform after it is completed if a certain field in a groupbox is chosed another form opens up which contains records in the many side of the relationship with the first subform. The problem is that the first subforms records aren't saved because the originall form isn't closed and I don't want it closed...How would I save those subform records when the new form opens up?
Thanks,
I have a "display only" subform that is updated by a popup form. If I add or amend an entry the details of the changes are displayed immediately in the display window by requerying the form "afterupdate". However, I have a delete button on my popup form which will delete a record but replaces it with the word #deleted in each of its fields. The #delete will only disappear once I have moved to another record which I don't want to do.
Can any one tell me why the amend/insert update works well but the delete behaves in this way.
How can I set up the delete function to function and remove the #deleted message without moving to another record. I have tried adding a save button but this doesn't fix it.
Thankyou
I have a form that has a subform in it, which is another table in it. I want to add the number of fields that are in the subform. What is the best way to do so?
I would like to write a VB script so that a field in the master field = the count of the records in that subform. Any help is greatly appreciated. Thanks.
Hi,
I am a bit stuck now and I want to validate several points with you.
First let me explain the context: I am writing a timesheet application and I've got a main form + a sub-form.
In the mainform, I select the engineer, the contract and the week to input.
My mainform calculates the first and the last day of the selected week and modifies the recordsource property of my sub-form to select the records with the right engineer + contract nbr + range of first and last of week. This means that I can only have maximum 7 records for the seven days of the week because the application records only a global time per engineer/contract/per day.
Here it becomes tricky and this is the most difficult part of the application (I hope); what I want to do is to help the user having to enter the times.
so I need to retrieve the records but if they don't exist, I
want to create new records with engineerId, ContractID, day of the week and let the nbr of hours to zero. This in fact will help the user because he will have only to complete the nbr of hours. Iw ant obviously to present those entries in the right order; I mean in ascending ordre based on the day of the week.
Eventually, when the user has completed the week, what I want to do is to update the existing records, insert the new records where the number of hours is > 0 and discards rhe records where the number of hours is = 0.
I know this is a bit tricky to understand but if you could give the main ideas to implement it would be very grateful.
Regards.
Fabiano Gaiga.
Dear All,
Maybe my question is not too difficult.
I have a form-subform structure and my aim is to number certain records of the subform. These records would contain value: 1,2,...
And when changing the record on the main form, the subform's record should have the values 1,2,... again.
(These values are shown on the bottom of the subform, where we can step the subform's record.)
If I use an autonumber field, the numbering goes continously.
Please help me!
Thanks you in advance.
Sz.Cs.
I’m in the process of developing a holiday property availability form for displaying the available and free dates for a particular property in a particular period (usually 35 days). The main form allows the selection of the start and end dates of the period using a Calendar control and then completes text boxes with the days of the week for each day of the period.
The next step for the user will be to click on a property button to run a select query to find the booking records that are present for the availability period being looked at. Each booking record has the start date and the number of days booked together with the property name and other fields.
I have the select query results as a subform and can access fields on the first record using
Mydate = Forms!Availability!CoiresgeirDates.Form!Revdate
MsgBox Mydate
However, I need to be able to access each record on the subform to be able to colour each text box for each day the property is booked so the user can see visually which days are booked for that period.
I’m struggling to find out how access the subform recordset to be able to loop through the records and pick off the days that are booked. I’ve tried defining a recordset but VB rejects the dim setting with Can't find project or library. Can somebody point me in the right direction?
I have a form with a subform. I want the records on the subform to be in alphabetical order of first name. At the moment, they appear in the order the records were entered.
I have opened up the subform itself in design view and specified the record order. If I then switch to form view for this, the records correctly display in alphabetical order.
However, when this form is displayed as the subform, the ordering reverts to the original order of record entry.
How can I change this?
Thanks,
Gary