Currently I have over 600 columns I need to use in a query and since the limit is 255 columns per query, I need to create multiple queries. Currently the form shows the record source of the single query I have created. Now that I need to create the other queries, how do I have the forms record source to recognize the other queries as sources as well?
Thanks,
JGM
onesweetdude@hotmail.com
I know my form is large and got too many fields for a standard form but trying to expand it anyway and don't really want to go to sub-forms...as then cannot expand it without re-creating forms/queries/tables structure. (or that what i believe)
My form and subsequent table is nearing the 255 limit, so I created a second table and just used a query to put both in form, but then I found out that a query also has the limit...
So if have two queries set up from each table but to same form?
I am making a master form for three tables which ultimately form a hierarchy. I am getting all the fields from each table onto one form. By default the record source would be made up of an INNER JOIN such as below
SELECT PERSON.first_name, PERSON.country, PERSON.last_name, PERSON.street, PERSON.area, PERSON.city, PERSON.telephone, CONTRIBUTOR.registration_date, CONTRIBUTOR.fav_category, MUJAHED.profession FROM (PERSON INNER JOIN CONTRIBUTOR ON PERSON.id=CONTRIBUTOR.id) INNER JOIN MUJAHED ON CONTRIBUTOR.id=MUJAHED.id;
I'm trying to change the record source to match using the where clause instead. However the following allows me to view but not add a new record
SELECT PERSON.first_name, PERSON.country, PERSON.last_name, PERSON.street, PERSON.area, PERSON.city, PERSON.telephone, CONTRIBUTOR.registration_date, CONTRIBUTOR.fav_category, MUJAHED.profession
FROM PERSON, CONTRIBUTOR, MUJAHED
WHERE (PERSON.id = CONTRIBUTOR.id) AND (CONTRIBUTOR.id = MUJAHED.id);
A tip or guideline on how I can correct this will be appreciated.
Regards
I have a form using a query as a control source so that is prompts the user for a parameter when they open the form.
I also have a listbox that is calling the same query.
The issue I am having is when I open the form it will prompt me twice to enter the same parameter.
Tables:
tblship
tblconsignee
tblbill
tblinvoicedetails
Query
qinv
Form
frminvoice
When the user enters the invoice number it populates the ship, consignee, billing address information. I have the list box to display line items for that invoice.
Currently I just have them enter the invoice number twice.
Is there a way I can take the user input and apply it to my other query?
I have a list box on a form which uses the following SQL to pull the list items from a table :
SELECT DISTINCT tblMyTable.MyField FROM tblMyTable ORDER BY tblMyTable.MyField;
For some reason, during testing, the first two items on the list are blanks. I am quite happy for one blank to be returned at the top of the list (as this would easily identify for the user any records for which this field has not yet been completed, which is a good thing) but I can't understand why it would appear twice?
Why doesn't the DISTINCT command ensure any blank entries only appear once?
Incidentally, I have a subform which populates on the back of selections from the list boxes, so I can quickly see the corresponding records which generate these blanks. What's puzzling me is that the same records show for both blanks on the list - suggesting it is the same value repeating itself (and not, say, "" vs " ", for example, which was what I originally suspected...)
On the form I need to give the user the option to select a 'from date' and 'to date' and for this I have put in 2 calender controls which have the same control source(same table column). The problem I am facing is when I select a date from one calendar, the other calendar control automatically populates with the same date and vice versa.
I need to have a functionality where 2 different date can be selected and then when the Search button is clicked, then data within the date ranges should be displayed.
what changes I need to make to acomplish this ? find a screenshot of the form attached.
This code loads a new subform in the frame. It then scopies a value to an input field but then i need to set the record source and display the data. This latest pasrt does not work.I've been trying for some time now by changing the part before recordsource but no luck.
Code:
gekozenwo = Me.Keuzelijst0
Forms!switchboard.subfrmWindow.SourceObject = "frmInformation"
Dim frm As Form
Set frm = Forms!switchboard.subfrmWindow.Form
frm.DataEntry = True
Forms!switchboard.subfrmWindow.Form!Tekst33 = gekozenwo
MsgBox gekozenwo
frm.RecordSource = "SELECT * , [orders_maint_detail]![woisnr] & ' / ' & [orders_maint_detail]![Ordernummer] AS Expr1 " _
& "FROM orders_maint_detail " _
& "WHERE (((orders_maint_detail.woisnr)='" & gekozenwo & "'));"
Me.Requery
wonr = gekozenwo
I tried and failed to get this to work using a multiselect listbox..I have a list of departments in tblFunctionalArea...My main table is tblStatic..I want to be able to for each record select multiple departments affected by a record and store them in the tblStatic.After looking around i couldn't find many people successfully maanging to store listbox values in a table...
I decided to create 5 fields in tblStatic and in my form create multiple combo boxes cboFunctionalArea1, cboFunctionalArea2 etc etc which are bound to these fields.I want to be able to ensure the list for any combo box requeries and takes out any selection in the other boxes.
I have this working in a strict cascade fashion i.e. in cbo1 all dept's visible, in cbo2 it takes off whatever was selected in cbo1 etc. But if someone then jumps back and deletes the content of cbo3 then the whole thing breaks or if they amend in the wrong order it breaks
I currently have a query that a subform uses to display some results. In this case, there are 3 columns displaying the name of a sandwich, the ingredients, and the number of servings of each ingredient. It looks like this:
SandwichName IngredientName Servings
Accordian Lettuce 1
Accordian Ham 1
Accordian Cheddar 1
Accordian Mayonnaise 1
That is the current output to the subform. The SQL statement I use to generate this list is here:
SELECT [TBL_Sandwich].[SandwichName], [TBL_Ingredient].[IngredientName], [TBL_SandwichContent].[SandwichTotalServings]
FROM (TBL_Sandwich INNER JOIN TBL_SandwichContent ON TBL_Sandwich.SandwichID=TBL_SandwichContent.Sandwi chID) INNER JOIN TBL_Ingredient ON TBL_SandwichContent.IngredientID=TBL_Ingredient.In gredientID
WHERE TBL_Sandwich.SandwichID=6;
I have bolded the last item the 'SandwichID'. It is this number I need to change dynamically based on a button being pressed on the main form. Essentially they enter a new sandwich, put the ingredients in, save it io the database, and I save the servings for each item as 1. Now I'd like the new sandwich to be displayed in the subform using the new ID of the sandwich which was just created.
If I try to put this long SQL statement in the RecordSource property I am told it's too long for the property to handle so I can't set this property dynamically. how to get the subform to requery with the new ID number of the item I just generated. I can get the ID number via DLookup, that's easy, but setting the subform to use this is my problem.
I want to use a query as the record source of a subform. I want to be able to actually write the query out, not refer to a query object, because I want to change the query on the fly using VB.
This subform displays records when I have it opened by itself, but will not return records when it's displayed as a subform. Does writing the query out not work for subforms?
I am trying to get a follow-on form to reference a subform. Here is what I currently have going:
I have two (2) tables: 00 Orders and 00 Products; both forms have primary keys "Orders ID" and "Product ID" respectively.
The "Orders ID" in "00 Orders" has a one-to-many relationship to "Orders ID" in "00 Products".
The user adds the main order details to "00 Orders" Table via "01 New Order" Form, and adds the ordered products to "00 Products" Table via a "00 Products Sub Add" Subform.
The user can then update the order via "02 Update Order" Form which has a "00 Products Sub Update" Subform.
This is working perfectly!
The "00 Products Sub Update" Subform has a "Contr Details" Button for each product ordered which opens a follow-up "00 Products Sub Update Contract Details" Form to allow the user to add additional details to the "00 Products" Table. I did this to reduce the size of the main form as the additional details are not needed to be viewed as the order process is going through the stages.
In the Record Source of this "00 Products Sub Update Contract Details" Form I have the criteria for the "Product ID" set to [Forms]![00 Products Sub Update]![Product ID]. This ensures only the additional details for the selected product come up on the form.
Everything works perfectly; I can add new orders, I can update orders, and the selected product comes up when I press the "Contr Details" Button on the "00 Products Sub Update" when it is a stand-alone Form.
What the problem is, Access asks for the user to "Enter Parameter Details" for "Forms!00 Products Sub Update!Product ID" when the "Contr Details" Button is selected in the "00 Products Sub Update" when it's a subform within the mainform.
So everything works when I use "00 Products Sub Update" as a Form by itself, but Access does not seem to be seeing the "Product ID" when "00 Products Sub Update" is a subform.
i have a form that has a record source
Code:
SELECT tblClientDetails.FirstName, tblClientDetails.Surname,
tblClientDetails.MobileTelephoneNumber, tblClientDetails.SentTextMarketing
FROM (tblCategories INNER JOIN tblItems ON tblCategories.CategoriesID = tblItems.CategoriesID)
INNER JOIN ((tblClientDetails INNER JOIN tblOrders
[Code] .....
this does not allow edits on a continuous form. the edit i am trying to do is the check box. it will allow edits if the grouping is not on but if it is not on then i will get many duplicates of client details.
so how do i set it up to allow edits but still only get one instance of each client detail.
I have a main form with two unbound text boxes that filter a subform with a select query in the record source. The problem is that it only works sometimes.when I copy and paste the SQL into a query instead of the form it works all the time. for example when I open the query and the form side by side with the same SQL the form does not show results but the Query does, and yes I have "requeried" both of them after entering criteria into the unbound text boxes.
Here is the SQL:
SELECT [Quote_ID] & "-" & [Version] AS QuoteIDv, tblQuoteID.Quote_Type,
tblQuoteID.Quoted_By, tblQuoteID.Agency, DLookUp("[Contact]",
"tblContacts","[QuoteID] = '" & [Quote_ID] & "' And [Version] = '" & [Version] & "'") AS contact,
tblQuoteID.Quote_Date, tblQuoteID.FileName, tblQuoteID.FileLocation, tblQuoteID.Amount,
tblQuoteID.Active, tblQuoteID.Quote_ID, tblQuoteID.Version, tblQuoteID.Selected
[Code] .....
Can I change the Records Source of a subform from within that subform and do a requery to have a different set of records displayed'
I want to be able to refine the records displayed in the subform
My form has a Record Source of qry_Profile, it is a query that shows the user the records of the dogs that they currently own. I want to put a button in the form footer to allow the user to show the records of all the dogs that they have ever owned.
So my question is how do I change the record source with vb.
Access 2013 on a Window 8.1 system.
I have a combo box based on a look-up table which I use to filter the records in the form. Seems after closing the form and reopening it, the filter remains.
I'm not sure if this is referred to filtering or restricting the records.
The value in the record source doesn't get changed but the records displayed don't reflect this. The records shown are the last filter I selected in the combo box.
The only work around I have found is I must explicitly set the record source in the on open of the form.
The was not occurring with access 2010. And I have made no changes what-so-ever.
I have one form which houses all my product information via the use of a query and subforms where appropriate. I would like to filter it in order to find records missing certain information.
I want to filter the information using a combo box.
The combo box has a user friendly name (Product Dimensions) and the name of a query related to that name (qryfilter_missing_prod_dims - this query is the record source query for the form plus the query that has the missing info, the query is set to return all records that match ie. all records that are missing info).
I want to write a vba code to filter my form based on that query.
And i can't get it to work using the cmd.applyfilter (docmd.applyfilter me.cbo_missing)
I've also tried to use me.recordsource. when i do it filters perfectly, but i can no longer edit any of my data in the form.
How to filter my form using a query that is not in the native form's record source?
I have an unbound form with three tabs. On each tab there is a sub form. Each tab is a search form and each sub is a results returned. I have made everything unbound and set the sub form recordsource and its controls control souce on afterupdate of the main form search criteria. Works fine except for after some use the db decides the sub forms are not unbound and sets the record source and control sources.
I'm trying to do a
Me.PollingPlacesResults.Form.RecordSource = ""
Me!PollingPlacesResults!PollID.ControlSource = ""
but this does not seem to work in actualy removeing the record source and control source.
In short can I display a field on a form that is not in the forms field list without using a sub-form? I am trying to show a value from a query on this form. I tried this in the control source:
[qry_op500_entry_delta]![countofphone model]
I get an error stating that this is not in my field list for the form.
If the answer is No or if needed I can give more information on what I am trying to do.
In my database I have a form who's record source is a query. That query is based on a table, and have set a criteria on one of the fields.
When I use the form to add a new record I want the value of the field in question to automatically be what I have specified in the query.
Instead, the record is added but without having that value in the field, and when I go back to the form it doesn't display the record because it doesn't meet the criteria specified in the query that the form is based on.
Is it possible to assign a query to the recordsource of a form dynamically? I assume the answer is yes. Can this action be done when the form is not open? If yes what is the correct syntax to use when the code is in a normal module as a public sub? I tried many possibilities and could not get it to work.
Code:
Dim ForNm as string
Dim Qry as string
ForNm = "PersonalFm"
Forms(ForNm).Recordsource = Qry
Forms![PersonalFm].Recordsource = Qry
[Forms]![PersonalFm].Form.Recordsource = Qry
I have a navigation form that has a tabbed form on one of its tabs. The tabbed form has a 'current client' query record source and allows one to choose from a list of current clients and when a client is selected - details relating to client are displayed on various tabs on tabbed form.
I want to duplicate tabbed form and change record source to a query selecting 'exited clients' so I can see same information but for exited clients.
I have created a new tab on navigation form for my new exited clients tabbed form and changed record source by creating a new exited client query but when I change record source on exited client tabbed form it automatically changes record source of current client tabbed form to the exited client query and visa versa.
I would like to know how i can have a continuous form or subform with dynamic record source.
Before I was using a list box with dynamic row source and i had not problem.
Now about the continuous form i have problem when i define record source of form on the fly how we have to add fields to the form.
My form with record source of query has stopped allowing edits.
I have reviewed property setting and do not understand what's going on.
I am setting up a generic form template that will enable me to maintain data in a standardised way. All generic code is stored in a module. There is very little code specific to the table being maintained. Much of the form functionality is determined by data held in supporting tables therefore data driven.
When a form is loaded a commonly named query is created that is used as the Record Source of the form. This query only ever returns one record and uses the value of textbox on the form as criteria in the unique id field.
Despite immediately creating the query in the Open event of the form, Access tries to process the form first and returns an error as the Record Source query applies to another form, the one last opened.
How can I create the query before anything on the form is referenced / processed without creating it before the form is opened?
I have a form that shows multiple rows of linked/child records.The form in question is the "frmFilterNumberTypeView" form. When a particular filter is used (installed on an automobile), I would like to be able to click on the corresponding "Installed 1" button left of the filter number, so that by code, I can reduce the Qty on Hand by One, and insert the corresponding filter ID to the History table, along with the date/time the filter was used.
how to reduce the Qty on Hand, nor how to do the updates to the History table, I'll figure that out myself over the next week or two (hopefully it won't take that long). What I'm trying to figure out here is how I can associate the red "Installed 1" button with the filter to the right of whichever button I press.
how to sort the filter column on this same form. I'm fairly certain that this would be very easy to do if the subform in question was populated by means of a corresponding query, but I'm afraid that if I go that route that I'll spend another 20+ hours trying to figure out how to get the proper filter records to match the filter manufacturer, not to mention having to next figure out how to link the table so that if I modify any particular filter record, that I'll be able to have the associated table update accordingly.
Is it possible to sort the filter column without the subform record source being a query?