Access Subform Query Help
Mar 28, 2005
Hi all :D
Background:
I have a few tables, (products, Customers, Backorders, NewCustOrders)
And 2 main forms using a couple of subforms. One of the main forms has 2 tabs; Tab 1 shows current products list, Tab 2 shows back order list.
The other Main Form has 3 Tabs; tab 1 has the customer details, (that part is fine), Tab 2 has a couple of fields used from the first tab and a subform which is ment to be the customers order., Tab 3 is ment to show customers order history.
On tab 2, new order subform
I have 6 fields, ProdName, ProdSize, UsebyDate, ProdQuantity, Prod-R-Price, TotalCost.
Prodname and prodsize are combobox's
Totalcost is calculated from prod-r-price*ProdQuantity.
Main problem:
1)
I am trying to get the prodName to list each product Once only, not once for each entry in the table. How do i filter the combobox so it will only show one product name, even if there are multiple entries in the products table.
2)
Trying to filter the prodsize to only show available sizes of selected product - (From first question). not every size in the table as it currently shows.
This is driving me Nuts, i have looked for the last 4 months in over 60 books, allover the web, and asked anybody who has had anything to do with access. NO ONE KNOWS THE ANSWER!!!!!!!!!! :eek: :eek:
Thanks
Peter
View Replies
ADVERTISEMENT
Dec 20, 2007
How do you pass a primary key to an append query from a subform?
Example:
Main form Table Subscriber (subscriber-id primary key)
Linked (subscriber-id (main form) = ot-subscriber-id (subform))
One to many
Sub form - Other residents (ot-othres-id primary key)
From sub form append history record using primary key by query
With in query criteria [forms]![other residents subform]![ot-othres-id]
Does not work The primary key isnt being passed which shows in the subform. Pop up box appears.
View 12 Replies
View Related
Jun 25, 2014
I have saved query object named qrySearchBill. I wan to call this query through vba and display the result in a subform named subQrySearchBills in datasheet view. Here's how I want it to work:
When the main form loads, I want all unfiltered records to be displayed in the subform initially. The user may then decide to filter based on date range, so he enters startdate and enddate parameter values in their respective textboxes in the main form. Then click search button to run the saved query based on the date range parameter taken from the textboxes.
I have this code so far:
SQL of the saved query object:
Code:
PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
SELECT tblInvoice.BillNo, tblCrdCustomer.CstName, tblCrdCustomer.CstAddress, tblCrdCustomer.Island, tblInvoice.Date, Sum(tblInvoice.[TotalPrice]) AS Amount
FROM tblCrdCustomer INNER JOIN tblInvoice ON tblCrdCustomer.IDNo = tblInvoice.NameID
WHERE tblInvoice.Date Between [StartDate] And [EndDate]
GROUP BY tblInvoice.BillNo, tblCrdCustomer.CstName, tblCrdCustomer.CstAddress, tblCrdCustomer.Island, tblInvoice.Date;
vba code to call the query and its parameter:
Private Sub btnSearchBill_Click()
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Set qdf = CurrentDb.QueryDefs("qrySearchBills")
[Code] ...
This code works fine except that when the main form loads, a prompt window appears to ask for the value of dateStart and dateEnd. I don't want it to prompt because it's suppose to get these values from the main form's textboxes (txtStartDate and txtEndDate respectively), plus it should initially display all the unfiltered records.
View 3 Replies
View Related
Sep 16, 2014
I have a form, a couple of comboboxes and text boxes on it. When these are filled out, the SQL of a query is changed using these parameters.
There are three subforms on the form, all pivot charts, all based on the query being changed.
The goal would be to update all three according to the user-given parameters.
Right now the subforms only update if I close and open the form, which is probably not the best solution, since it's too slow.
I've also tried to requery and refresh them, with no result.
Then I tried to overwrite the recordsource of the subforms with the same text that was originally there. This got them to refresh their data, but then all of the charts disappeared and had to be built again, so this is a no go too.
View 7 Replies
View Related
Apr 19, 2005
I am working on a massave aplication that has been running in Access 2000, but recently several of the file sharing users have installed Access 2003 because of the limited availability of Access 2000. All the users are using the same file off the server.
The problem we are having is that when we reference a subform in the "[Forms]![FormName]![SubformName]![FeildName]" Access 2003 does not recognize it and returns an error. I have found that if I will modify it to "[Forms]![FormName]![SubformName].[Form]![FeildName]" it is recognized in both 2000 and 2003.
To try and change every instance of a subform reference will take forever and I am garuteed to overlook something. We reference subforms all over our program, missing any one of them would be a disaster. Before I went to the tedious task of looking through everything I just wanted to throw the situation out there and see if any of you had any great ideas on how to get it fixed efficiently. I would apreciate any ideas.
View 5 Replies
View Related
Mar 8, 2005
I have posted about this before andhave done some reading, but cant get it working no matter what I do.
I have the following forms:
SuppierOrderCreateNew
SupplierOrderCreateNewSubForm
Obviously "SupplierOrderCreateNewSubForm" is a sub forms on "SupplierOrderCreateNew".
There is a text box on "SuppierOrderCreateNew" called "supplierOrderNumber".
There is a text box on "SuppierOrderCreateNewSubForm" called "productNumber".
I then have a second form: "ProductSuppliers". I want to return a value from "ProductSuppliers" when a button is clicked on. When the button is clicked on, I want to put the value into "productNumber". However, I dont know how to refer to the text box.
I can put the value into "supplierOrderNumber" but not into "productNumber".
Can anyone help?
I have tried:
Forms![SuppierOrderCreateNew]![SupplierOrderCreateNewSubForm].Form![productNumber] = 10
But it doesnt seem to work.
Note: 10 is not the value I want to put into the textbox, its only a random value ive been using to test the statement with.
Thanks.
View 6 Replies
View Related
May 5, 2005
I have a sub form that shows items booked for a person, this subform is refreshed everytime a new item is booked. I want to be able to add commission so I have a combo on the main form that has commision rates in it.
I have a click event on the row in the subform with the hope that I could click a particular product and then if user clicks the description then the click event will add the commision due on that particular item.
the row is [prodid] [desc] [cost]
the question is how do i access the row data on the subform?
in this case I have grabbed the commission rate from the combo and now want the cost from the row i clicked.
regards in advance
peter
View 4 Replies
View Related
Jul 19, 2006
Hello All,
I have a bit of a problem. I have a main form with two subforms. The subforms are linked to the main form my two fields. The main form and subforms are each based upon a different query.
I have written VBA code that filters the SQL statements of the subforms based upon the value of the the REV field on the main form. When the user changes to different record on the main form, the subforms are supposed to change accordingly.
The problem is that the subforms on the main form will not refresh each time the user chooses a new record on the main form. If I change to a new record on the main form and then open the subform outside of the main form, the correct information is shown. The problem however is that the subform shown on the main form does not automatically refresh. I have tried numerous strategies, but to no avail. If I am on a record in the main form, and I exit the main form, and then re-open the main form, the subform data on the main form will reflect that of the record I was on prior to previously exiting the main form.
Here is some of the code that I have tried:
Me![qryRevText_ sf].Form.Requery
Me![qryAdminRevText_sf].Form.Requery
Me.Refresh
or
Me.[qryRevText subform].Form.Refresh
Me.[qryAdminRevText subform].Form.Refresh
or
DoCmd.Close acForm, "qryRevText subform"
DoCmd.Close acForm, "qryAdminRevText subform"
Please help me if you can. I would appreciate it greatly.
Thank you in advance.
Akagami
View 14 Replies
View Related
Jan 26, 2005
Hi,
I have two subForms on separate pages of a Tab Control on a main form. I want to create a Textbox on one Form to display the month of a Date field on another form, using DatePart.
Using the On Current event, this works ok, if I have the 'other' subForm open in its own right, but I get message telling me it can't be found if i don't. Here is what I have...
Private Sub Form_Current()
If Not IsNull(Forms![Reviews]!SelectLessee) Then
Dim revDate As Date
revDate = Forms![subReviewPEST]!DateOfNextCreditReview
Me.Text118.Value = DatePart("m", revDate)
End If
End Sub
What syntax do I need, or how then can I access the control without having the subForm open itself, by getting to the page on the tab control, then to the control on the subform...
View 2 Replies
View Related
Sep 12, 2005
Hi,
I'm trying to requery a subform within a tab after I select from a combo box in the main form. Can anyone help?
Or just to simulate the "refresh" button click within a macro?
thanks
Jeff
View 4 Replies
View Related
Jun 20, 2006
I am having problem linking data in a form to a subform.
What I have is a form that contains the name of a project and the description of the project. That form then includes a subform that lists the various progress reports for that project in a tabular form.
I have a database to keep track of the link between the project and the various progress reports that it can contain. My problem is when using the Form/Subform to create new progress reports, it is not creating the necessary values in the table to link the project and the new progress report together. Any help would be appreciated!
Here's a screenshot to give everyone a better idea:
http://img220.imageshack.us/img220/4442/temp5ua.th.jpg (http://img220.imageshack.us/my.php?image=temp5ua.jpg)http://img111.imageshack.us/img111/5753/temp26eg.th.jpg (http://img111.imageshack.us/my.php?image=temp26eg.jpg)
I would post the DB but it's to big according to forum attachment size limits.
TIA!
View 6 Replies
View Related
Sep 25, 2006
I'm trying to develop a simple company price list. I have created a suppliers products table. But I want to use the suppliers products to make up our coded goods (A Bill of Quantities simply). But I'm caught in a certain area that I dont have any knowledge on.
I want to create a form / subform whereby I can create my company codes, add up the cost price and put mark on it.
I have attached a image of what I'm trying to do in excel. In excel all I do is enter the code and the descrition and price comes up in the cells automatically. I just enter the quantity I require and total (sum) it all up.
In the subform itself. I can't get the the rest of the text boxes to automatically update once I have entered the code in the combo box. I rekon I have to enter a event procedure. Can some
one help
View 4 Replies
View Related
Apr 13, 2005
is anyone has idea about how to undo update for subform?
i have a parent form(linked to A table) and a subform (linked to another table). the parent table has two button-save and cancel. i was wondering how the subform cancel update if the parent form's cancel button has been clicked? the code Me.undo will undo the parent form's record only, since the subform record has been saved into access.
View 1 Replies
View Related
Feb 27, 2015
I have a subform which is created with textbox and some command button in the top of the window, so this will simulate like a Datagrid.
[URL]
This is how it looks my datagrid when it fills from a Query.
[URL]
What I want is when I select a row from my subform, I want to Click the button "Cambiar", and get the value of the machine selected.
[URL]
View 4 Replies
View Related
Jan 1, 2014
A subform on a main form is not populating after the user clicks on the subform and then returns to the main form and updates the field which the subform is linked to. The first time the subform populates absolutely okay; however once the user clicks on the subform and then returns to the mainform and updates the field which the subform is linked to the subform goes blank.
View 7 Replies
View Related
Nov 12, 2012
I have an access form that has a marco button to open another form using a filter that returns records in the new form that have a matching recordID field from the main form. This works fine.
I want to be able to add records to the table which works as well only the recordID field is not populated with the id field used in the filter from the main form. How can I populate the recordID field with that of the master filed from the main form? It is just blank on the new records created now.
View 13 Replies
View Related
Dec 2, 2004
I have a form and a sub form. How can I create a dropdown/lookup field on the main form, which will find the customer details from the customer table, and then access the correct subform for their spend details?
I hope someone can help. Thanks in advance.
Regards
Peter
View 7 Replies
View Related
Feb 11, 2004
Hi,
I have a problem to make a search form in access, I want to divise my form, at the top will be the search criter (8 fields) and in details section will appear the result. I use a continuous subform with a query on the searched fields.
But I can't actualise or open the subform with the new results.
I would like a button to start my search or a system to automatically show the result on AfterUpdate event.
Can you help me, please? i trying to solve this for a long time...
Thank you,
Mrflo
View 13 Replies
View Related
Oct 22, 2014
It seems that if I have a continuous subform open and scroll down through the records, then hit Alt, the subform immediately scrolls back to the top again?
I only noticed this today when I scrolled to a particular record, then wanted to use Alt+Tab to switch to a different window. When I Alt+Tab'd back to my DB, the subform had scrolled back to the top and I had to scroll again to find my record. It was only then that I realised the auto-scroll was happening as soon as I hit the Alt key.
How to disable this behaviour as I use Alt+Tab pretty frequently (as do my users, more importantly) and it will get pretty annoying pretty quickly if the subform keeps reverting to the top?
View 4 Replies
View Related
Apr 28, 2015
I am having trouble to link a listbox on a subform (table) tot de main form (table). I would like to use it as an lookup list for the connected records. Already tried a lot but but it doesn't work .
View 4 Replies
View Related
Nov 5, 2014
I have not used access since Office 97 so, I see many changes. A while back I used Access to create some minor tracking databases. Now, with 2010 I'd like to modify the database that I am currently using. I have a main form that tracks daily trips or tasking(s). Within the form are two sub forms that track:
1. People assigned to the trip through a combo box; and
2. Itinerary for this trip.
I have been asked to add a Trip justification(s) through a combo box which will have several selections avail that can be chosen and would apply to each trip. These can be added to by double clicking the justification (figured that one out). What I would like the sub form to do for each new trip that is entered is to display only these values as a single selection rather than 12 times (example) if there are 12 trips that have the reason justification as part of the trip/tasking.
I just can't seem to make this work. The personnel one and itinerary one work just fine (previously existing). I just can't seem to get the new one to work. I have checked that the new table is connected through the relationships. In the exact same manner. The new form has the ID (Key) OPID (to link each trip separately) and justification text field.
View 6 Replies
View Related
Dec 10, 2012
I have a Form with 2 subforms. One subform has an onload property to adjust columnwidths. I am trying to do the same for the other subform - but I get the following error:
The Expression on load you entered as the event property setting produced the following error: A problem occurred while micorosft office access was communicating with the OLE server or Active X Control.
View 3 Replies
View Related
Nov 16, 2014
I created an instructor form with a sub form table on the bottom - [pic at the bottom] I would like to add columns in the subform from the instructor_info subform but it should not be editable, just display it as per the column relationship key, and the relationship key for the 2 tables (instructor_info & course_table) is course_id.
Course Table
- Course Date (instead of Course)
- Course Location
- Station Demo
- No. of Teachings
- Hours Taught
Instructor Teaching Info Table
- Instructor ID
- Alone
- Course
- Course Date
- No. of Teachings
- Course Type
- Notes
View 8 Replies
View Related
Apr 12, 2014
I am trying to create a database to manage IT assets, most of the structure is done, I'm now trying to get the details done.
The issue I am currently trying to tackle is to get a control button on one form to create a new record in the sub form that sits in the main form via another pop-up form.
So I have :
>PeripheralsViewForm - that has 4 control buttons (Edit Quantity, Edit Description,Add an Item and Exit)
>> PeripheralsSubForm - (Datasheet) which is referenced to PeripheralsQuery
The Edit Quantity and Edit Description open a separate form which allows the user to update only the quantity or description respectively (I have done it this way rather than allowing edits in the data sheet as I feel that it is too easy to hit a key incorrectly and overwrite data in the datasheet view) Both of these functions work perfectly (to my amazement) - I am using the id field in the subform to link to the pop-up form which then has unbound text input boxes, which I then write back to the subform.
e.g from edit button on click event: DoCmd.OpenForm "perquantform", , , " ID=" & Form.PeripheralsSubForm!ID and then from the pop-up form "perquantform" on the "Exit & Save" button I have :
Forms!peripheralsViewForm.PeripheralsSubForm.Form! Quantity = Me.newquant DoCmd.Close acForm, "perquantform", acSaveYes
This allows the user to select the record in the datasheet form and then click either the change quantity or description button.Also also the other reason I did it this way was because I just couldn't get the subform to requery when I had the two edit popups linked to the table or query.
The problem now is that when I click on the "Add an Item" button, it overwrites whatever record is currently selected !
The code I have is this:
From the "Add an Item" button on click action:
DoCmd.GoToRecord , Forms!peripheralsViewForm!PeripheralsSubForm.Form! , acNewRec
DoCmd.OpenForm "addperform"
And from the form "addperform" - "Save & Exit" button on click action:
Forms!peripheralsViewForm.PeripheralsSubForm.Form! PerType = Me.pertypedrop
Forms!peripheralsViewForm.PeripheralsSubForm.Form! PerMake = Me.permakedrop
Forms!peripheralsViewForm.PeripheralsSubForm.Form! PerModel = Me.newmodel
Forms!peripheralsViewForm.PeripheralsSubForm.Form! PerDescription = Me.newdescription
Forms!peripheralsViewForm.PeripheralsSubForm.Form! Quantity = Me.newquantity
DoCmd.OpenForm "PeripheralsViewForm"
DoCmd.Close acForm, "addperform"
--------------------------------------
the line "DoCmd.GoToRecord , Forms!peripheralsViewForm!PeripheralsSubForm.Form! , acNewRec" is what I have added to try to solve the overwrite issue, but when I run this it comes up with the error :
"runtime error 2498 - An expression you entered is the wrong data type for one of the arguments"
I have put this line in the "addperform" exit action and it comes up with the same sort of error.
Using Access 2010.
View 14 Replies
View Related
Sep 6, 2013
Is it possible to requery a subform 'in the background' before repainting / refreshing the screen?
I have a subform which has multiple controls, some of which have conditional formatting applied to them.
The problem is, whenever I open the tab that contains the subform, there is a momentary 'flicker' as the CF calculates and highlights the appropriate controls. I have a requery command in the change event of the tab control (rather than recalculate the entire form).
Code:
Private Sub tabAllFunctions_Change()
Select Case Me.tabAllFunctions.Value
Case 0 ' Page 0
Me.sfmSubForm0.Requery
Case 1 ' Page 1
Me.sfmSubForm1.Requery
Case 2 ' Page 2
Me.sfmSubForm2.Requery
End Select
End Sub
It would be neater if the subform waited until all of the necessary requerying / recalculating were done first before showing the final result. Admittedly this would introduce a short delay (we are talking a fraction of a second here) but it would make the output appear more stable rather than seeing controls flick between colours while the recalculation takes place. For me, this would be preferable.
How I can 'pause' the screen update while the Requery takes place to remove the momentary flicker?
View 1 Replies
View Related
May 15, 2014
I have a continuous subform with a fairly complex snapshot query as its record source. I have controls on the main form to allow criteria parameters for the query and a command button to requery the subform based on the selected criteria. I also have conditional formatting on a number of the continuous controls in the subform (the conditions are fairly straightforward, i.e. highlight if null, true/false etc)
When the subform is (re)queried, it seems to repaint several times (3 or 4 although it appears arbitrary to me) before it eventually settles. And it really louses up the aesthetics as controls flicker between colours; sometimes I even lose the background colour on parts of the detail section, or controls disappear altogether. And this continues as I scroll down the subform (or even click anywhere on it)
I don't mind that the query itself takes some time to run - of the order of a few seconds each time - but I'd prefer for the screen to wait until the query is completed, and all of the CF applied, before refreshing itself, instead of 'sweeping' top to bottom repeatedly. It's a snapshot query so once it's run, there should be no changes to the dataset.
I've tried Application.Echo but it has no effect whatsoever.
View 6 Replies
View Related
