I have a main form with a control that can take one of three values. And I would like to add a subform that work as follows.
Selecting each value should change what the subform displays like this.
Control Subform
A Select * from TableA where Key = "A"
B Select * from TableB where Key = "B"
C Disable subform somehow because it doesn't apply
Could someone please provide some advice on how I could do this.
I have a form which shows training events - these can take place over one or several days, and can be run by one trainer or several, so I have my basic Events Info in one table (EventID, EventType, Location, that kind of thing) and the 'Jobs' in a separate table (EventID, JobDate, TrainerID, etc)
The problem I'm having is that I want the List of Events to be sorted in order of their start date... which is on the subform, not the main form.
How can I go about it - and still leave both the main and subforms fully editable?
Greetings! This forum has been most helpful to a rusty Access user.
I'm creating a student group score entry form, having problems with creating the form I want. Say for example we have the following: 1. Three groups: A,B, and C. 2. Group A has 5 students, Group B has 3, and Group C has 20.
I want a form where I can select group A, and a list of the students will populate and enable me to enter scores for all of them on the same page. This will save time not having to re-enter the information for so many students. If anyone has a clue of what I'm talking about or is willing to lend me aid, I would appreciate it.
I am assuming Access cannot handle Many to Many relationships very well.
I have an Account form ("MainForm") with an embedded SubForm that displays only the Account Address with suite number, street number, street name, street type. Account to Address is a Many to Many relationship. My Account form's source is tblAccount, and the SubForm is built off of a query of 3 tables -- Querying Account, LinkTable, and Address table. The link between the SubForm and the "MainForm" is done via AccountID.
My problem is, when I query for an Address (say entering a street number of 25), I want to get back all the addresses with street number of 25 AND the corresponding Accounts that have street number 25 only
What it does is return the addresses with Street Number 25 and ALL the accounts regardless of what their addresses are!
Is there a way to do this?? Can I design a form without using a SubForm in this case, ie, display the account and its many addresses? Or does Access Forms not support Many to Many Relationships? I'm thinking the link table is causing the problems here.
Please advise -- this "simple" project is becoming quite the headache! :eek:
"edit" -- I also notice this problem with a 1 to Many relationship (the "many" portion being the subform)
I had a text box on a main form linked to a text box on a sub form using the following control source =Forms!Form![frm_Doctors_details subform].[Form].[Title]. This was working, however I had to play around with the subform alot and now the link isn't working. All i get in the field is #name?. Nothing has changed on the subform textboxs, just that I added a primary key to assist in the linking action bewteen forms. I can't for the life of me get it back to what it was . any ideas??
i'm working in access 97 and am having difficulties pulling a subtotal or referencing any fields on my subform on my main form. I've searched the forum and help and tried to apply the info provided, but i still can't seem to get it to work.
as far as i can tell this should work:
=[MySubForm].[Form]![TxtTotalStaffNumbers] but i'm still getting the dreaded #Name? error.
anyone got any clues as to what i'm doing wrong?
what about the case where the subtotal i'm referencing is a calculation such as =Sum([Quantity]*[FacilityArea]). Should i still be able to reference it on my main form by pointing to the name of the field?
I have a query (SelectedData_Query) , on which a form (SelectedDataQuery_SubForm) in datasheetview is based, which is placed on a form (NAWInvoerForm) where customer adress data is put in. When someone clicks new case button on the NAWInvoer_Form, another form (CaseDateTimeInfo_Form) is opened, data is entered, but when the "CaseDateTimeInfo_Form" form is closed, the data in the subform in datasheetview on the NAWInvoerForm is not yet refreshed. So I tried the following:
Private Sub Form_Close() '------------------------------------------------------------ ' RefreshNAWInvoerForm ' '------------------------------------------------------------ On Error GoTo RefreshNAWInvoerForm_Err
Which works just fine. After the form is closed, the name, adress and cases overview form is reopened and refreshed, thus the case that has just been created is shown nicely in the subform which is based on a query.
But it is starting to become bothersome, sometimes when I close the form in which I create a new case, I don't want to have the other form (NAWInvoerForm) reopening again.
So how can i make it so that when I close the form only the data in the "NAWInvoer_Form" is refreshed, without having to reopen the form again. I mean, in the background, the form is still open anyway. It just shouldn't keep popping up everytime.
It can't be hard, but I can't see it. I'm new at this. I have found some similar topics on this forum, but none which helps me out enough.
It should be something like:
On "thisandthatevent" do a "thisandthatcommand" on "thisandthatsubform" in "thisandthatform"
If I know how the syntax of that is done, it would help me out with some other problems I see coming in the near future. I've looked at, and tried some things based on this link: http://www.mvps.org/access/forms/frm0031.htm But without success.
May be this is easy for your guys. I want to freeze or lock the mainform data fields while allowing users to input, edit, delete data on a subform. I tried to change mainform properties so it can not edit, add or delete data on mainform. By doing so, I cannot do anything with subform, it seem read only as well.
My Database is really simple, it contains 1 table (called Querys) and a form called Dashboard.
In my main form (Dashboard) I have inserted a sub form which shows all the records in my table (called Querys) when I use my main form and add a record this sub table can then be refreshed and it shows all my records.
This part is fine,What I would like to do is be able to select a record in my sub form and it show the record data in my main form. This would really speed up the navigation process as the database gets bigger.
I want it to filter out any matches between the 7 fields. Here is my code:
Code: Private Sub Command19_Click() Dim strFilter As Variant, _ strSDate As String, _ strEDate As String 'check Text13 Text13.SetFocus 'set focus to Text13 to be checked
[code].....
It will only filter for Build Date 1 and not for any of the other fields.
I have a Mainform [FrmReconcileMain] and it contains a Subform [FrmReconcilesub]
What I'm trying to is, on the Main form type in a statement date in textbox [TxtStatementDate].
I have a checkbox on my subform [ReconciledYN], along with a textbox [TxtReconcileDateSub]. when I click the checkbox, it simply pulls the date from the mainform and populates the date in the subform.
I've even tried experimenting on a simple form (with no subform) to see whats going on, but still I can't get it to work, even on a simple event such as this...
If [yourcheckboxname] = -1 Then [controlnametoupdate] = date() Else [controlnametoupdate] = ""
I'm having a problem with the syntax of a recordset of a Datasheet inside a subform which is also inside a Main Form.
Main Form - frm_1_0_LMS Subform - frm_1_4_0_TeamApprovals Subform(Datasheet) - frm_1_4_1_TeamApprovalsList
Here is my code:
Code:
Dim rs As DAO.Recordset Set rs = Forms!frm_1_0_LMS.frm_1_4_0_TeamApprovals.frm_1_4_1_TeamApprovalsList.Form.Recordset If Not (rs.EOF And rs.BOF) Then Forms!frm_1_4_2_ApproveDeclineUserLeave.Controls("lblFiledDateLeave").Caption = rs!Leave_Date End If
I am getting this error: Object doesn't support this property or method
How can I get some mainform fields' data to be the first entry in a subform? From the mainform, I would like the Head of Household name and date of birth to be carried over to the first entry in the HouseholdMembers subform.
The two forms are tied to separate tables. For each household member I need to be able to enter full legal name, date of birth and some additional pieces of information such as income and source of income.
What I'd like to avoid is making the users enter the Head of Household's name and date of birth twice. Is there a way to carry that information over to the subform?
Ohh Stress. Need Help....I have a mainform that contained two subforms in it. MainForm called "Employee", the two subform called "vac", and "Prevac". There is a field in my mainform called "Hours", that I'd like to use to Add with a field in Vac subform called "VacA" and a field in Prevac called "FHours". I'd like to display the total (sum of all three field on the same mainform. Is there anyone have any idea? Thanks in advance...
Would like to be able to update a text field on the main form with information from a dropdown box on a subform when the subform data equals a certain value. Would like the main form field to update as soon as the subform information is selected from the dropdown box.
Example (sorry don't have code for it)
Mainform field = Mytext Subform dropdown field = Newtext1, or Newtext2, or Newtext3
If Newtext2 is selected on subform, I would like Mytext to immediatly show the value in this field (mainform/mytext). Do not want either Newtext1, or 3 to ever show in Mytext but want it to remain in subform field
It can either be on Lostfocus from subform or onupdate from subform, etc.
Whatever works best/easiest Thanks for the help in advance
Would like to be able to update a text field on the main form with information from a dropdown box on a subform when the subform data equals a certain value. Would like the main form field to update as soon as the subform information is selected from the dropdown box.
Example (sorry don't have code for it)
Mainform field = Mytext Subform dropdown field = Newtext1, or Newtext2, or Newtext3
If Newtext2 is selected on subform, I would like Mytext to immediatly show the value in this field (mainform/mytext). Do not want either Newtext1, or 3 to ever show in Mytext but want it to remain in subform field
It can either be on Lostfocus from subform or onupdate from subform, etc.
Whatever works best/easiest Thanks for the help in advance
I have a form with a combo box, and 2 subforms from it. When you select in the combo box (Group), it brings up the choices (Sections) for that group in a subform. Then there is another subform, that is supposed to bring up a series of questions that are related to the section that has been selected. The question field has a drop down to a list table, that has all the questions. Once the question has been selected, it stores in a Master table. The relationship is there. If you try to select any questions that do not pertain to the section, it gives you an error message.
What I need it to do, is when the section is selected, filter out the questions that pertain to that section, and have those questions be the only ones available in the drop-down list to choose.
I know that I need to have an After Update code once the section has been selected, but not sure how the coding should be.
I have a form with a combo box, and 2 subforms from it. When you select in the combo box (Group), it brings up the choices (Sections) for that group in a subform. Then there is another subform, that is supposed to bring up a series of questions that are related to the section that has been selected. The question field has a drop down to a list table, that has all the questions. Once the question has been selected, it stores in a Master table. The relationship is there. If you try to select any questions that do not pertain to the section, it gives you an error message.
What I need it to do, is when the section is selected, filter out the questions that pertain to that section, and have those questions be the only ones available in the drop-down list to choose.
I know that I need to have an After Update code once the section has been selected, but not sure how the coding should be.
Well hello everyone. I'm building some queries that need to have a specific Top N amount read from a value in a table (or form). Can someone please tell me how to do that without using the query Top N drop-down? I have this built into a VBA module, so it's possible I can change the Top N from there. Thanks!
I have a main form which has a subform control in it. when the main form is opened the sub form control contains a list of events in a sub form (a summary view), for the person selected in the main form.
I want to change the subform to a different one which shows the whole event when i click in a field on the subform, based on the event i clicked in. ( i can do it by opening a new form but not with a new subform)
I can make the form change using the following code:
What I'm trying to do is make it so that a certain area of my Detail area in my report changes colors based on whether or not a field name for each record is set to "Yes". The effect I wanted was produced easily enough by creating a Rectangle, coloring it Red, and setting it to not visible.
Putting RedBox.Visible = True in the Report On Load procedure works just fine. The problem I am having is attaching a condition to it.
Code:
If Me.FormatBox.Value = "Yes" Then RedBox.Visible = True End If
This compiles ok, but doesnt do anything to the actual report. Will it not work because the report has x amount of FormatBox's because it gets replicated for each record that is displayed? I should also note that I tried using a DL Lookup within my If statement and that didnt work either. That just colors all the boxes red and ignores the check.
Code: Dim TestVar As String TestVar = DLookup("Priority", "Table_Lancaster_Dispatch", "Priority = 'Yes'") If TestVar = "Yes" Then RedBox.Visible = True End If
I am trying to come up with a simple way for users to query the database and pull back entries relating to the (potentially vague) data they input.
I know how to run a query based upon data input in a form ("=[Forms]![FormName]![FieldName]"), and I know I can do this several times in one query.
However, if one of the fields is blank, the query doesn't return anything.
Is there a way to use only one query, but still allow the user to leave search criteria blank? The only thing I can think of is by using a very convoluted VB script and creating a query for each possible combination of criteria.
It's not an error-checking issue, either, as entering info into all of the fields isn't required.
For reference, the searchable fields are: Agent Name, Beginning Date, Ending Date (intended for a date range), Request Type, and District Name.
A user could then search for any requests made by an Agent; any requests by an agent within a date range; any requests made by anyone in a date range; ad nauseum.
There are quite a few combinations, there, and I'd rather not create a query for each one (or code the VB conditionals to choose the right one).
I hope I'm clear in what I'm trying to accomplish :o
I'm getting seriously frustrated with Access now so I hope some of you can help me out.
I have made an access application to enter various types of products (eg paint, tools, glue etc). The organization I have built this for has asked me to create a report which can be used as a catalogue of all their products.
They want this report to keep different categories of products in different sections. So say the paints will go into a section with an orange background and the tools have a blue background. I thought I could just let it print a number of reports (one for each type of product) however this will give problems with the page numbering and indexes etc.
Anyone have any ideas how I can let the lay-out (mainly colors) change if a new category kicks in and also force this category to start on a new page? Any help is very much appreciated!
