Forms- Subtable Filtering Controls
Apr 28, 2008
Hi,
I'm new to Access and am having trouble designing a database that houses application details.
All applications have a list of unique wrap id's but have different tables housing different details for these wraps.
I have a main table which is for data entry and lets me input whether or not a piece of software is Vista compatible (yes/no) etc.
I also have a field in this table showing which analyst is responsible for researching compatiblity of an application.
I have another table with different regions i.e. london, manchester etc that has the region/city listed as the field heading and then contains a list of wraps used in that city under the relevant field.
My main entry form is linked to the main data entry table that holds specifics for an app, name, vista compatible etc. This form is used for data entry. I want to add a subform on this form which shows a list of all applications, in table format, but I want a drop down list so I can select an analyst name / region so that analysts can update details for a specific area assigned to them. In the table I want all of the records based on this criteria to be displayed.
I guess I need to make a filter button on my subform but do not know how to do this. I put in a button control for apply filter by form but then dont know what to do.
I'm new to access and don't know much VB - e.g. the only thing I can do in VB is a make a message box that says hello world on the screen.
Any help is sincerely appreciated. Apologies for the Essay.
Kind Regards
Bal
View Replies
ADVERTISEMENT
Feb 14, 2005
Hi
i have a small access program set up and it all works except one item, there is a sub table that we would like to add in information relating to the order number etc:
order number 1
sub table would have j number 12345
allocation 5
and j number 56789
allocation 25
order number 2 (new record)
could have j number 148
allocation 1
j number 2143213
allocation 12345555
j number 86868
allocation 34
etc
then when you move between each order the information in the table will show the specific information relative to the order number
at the moment when you move from record to record it clears the fields but enters the information into a table
hope this makes some sort of sense
any assistance would be gratefully received
best regards
kev
p.s.if it would i could attach a copy of the database
View 5 Replies
View Related
Aug 9, 2006
Hi all!!
I've inherited a new db that was created by folks fresh out of school.
I've been looking through it, checking to see how they built it, trying to scope out any neat and nifty new tricks they've used, before something goes wrong and I have to work on it.
They have a table that displays grouped records, and I'm not sure how they did it.
For example:
In the PRODUCTS table, the first record is for [Product Type] "Sticky Stuff", [Product Name] "GooGoo", [Manufacturer] "XYZ Co.", etc, with a + sign in front of the record.
When you expand the record, you get what looks like a sub-table listing all the transactions having to do with "GooGoo".
While trying to figure out how they got this effect, I checked out the Inventory Management sample db that's available on-line. They also have this type of grouped records in a product table.
Is there a simple explanation of how this is done, or do I need to invest some serious study time?
As always, thanks for any guidance you can give me.
Froggiebeckie
View 3 Replies
View Related
Mar 9, 2008
How do we create a subtable in access. When you open a table. First column come with + singn. When you click the + sign you get another sub table. How do we creat that table. What is the purpose of this table?
regards,
- Mahen
View 5 Replies
View Related
Aug 30, 2004
Employee Record table:
Name
Department
etc...
ICU Required Inservices:
Name
Conscious Sedation/Analgesia (Yes/No)
Intraaortic Balloon Pumps (yes/no)
Inservice 3 (Yes/No)
Inservice 4 (Yes/No)
I am trying to build a query or table that will pull all Employees
in the ICU and include the required inservices so that I can
track who has attended and who has not.
This is a huge database I built for the entire hospital.
I need a relationship between the two that won't require me to
have EVERY employee record (all 850 of them) in the related table
so that I can pull just the ICU names.
Any ideas???? thanks!
View 1 Replies
View Related
Oct 1, 2012
Ok, there is one main form and a subform which is linked directly to a table. A VBA routine checks entries and flags certain rows for errors. We want to highlight specific cells that are out of tolerance.
Been playing around with the Me.ActiveControl.BackColor = vbYellow
But if this can be done to a particular cell on a table, and if this can be done using VBA.
View 14 Replies
View Related
Nov 27, 2013
I have a sub table that acts as a revision history for the items in my main table. I've just finished uploading all the records into the main table and now I want to insert one record into the subtable for each record in the main table to start the history with the record creation.
The sub table looks like this:
tblRevisions
- RevID (auto Number / primary Key)
- RevDate (date of revision)
- RevName (Who made the revision)
- RevDesc (What revision was made)
- RevAuthorized (Who authorized the revision)
- RevLabel (Foreign key to the primary table)
The info I want to insert is:
RevID - Auto Number
RevDate - 11/27/2013
RevName - 3
RevDesc - "Added Label to database"
RevAuthorized - 1
RevLabel - (One for each record ID in tblLabels)
I suspect that I want to start with an insert into statement, something along the lines of:
Code:
Insert into tblRevisions (Revdate, RevName, RevDesc, RevAuthorized, RevLabel)
Values ("11/27/2013",3,"Added label to database", 1, ?)
I'm not sure how to indicate that there should be one label for each Record in tblLabels or that revLabel value should match the ID from tblLabels though.
View 6 Replies
View Related
Jun 2, 2005
Hello,
I want to use a tab form where the user can click add, search, and edit records all from 3 tabs. The problem is I only want to be able to add data on one form, edit data on another, and just read only on the search. This seems to cause a dilemma, how can I specifiy them to add, edit, and read only on one form using tabs?
Mateo
View 1 Replies
View Related
Oct 31, 2013
I am making a database that will be used by multiple people on a network, and they have different resoloutions on their own computers. when the database is loaded on their respective computers, the form controls are all off.
On the computer i designed them on, the forms fit perfectly on screen, as do the controls, i would like to know if there is some vba code i could incorporate into the form open event, or module code that i can use in order to dynamically re-seize the forms and all of the controls to suit different resoloutions?
View 6 Replies
View Related
Mar 30, 2012
I have a main form, 2 sub forms, and 2 text-boxes on the main form. I have a search box now for the first subform. Works perfectly. Once the users filtered based on name, I want them to be able to select that filtered record which would filter another subform.
Right? ok.
My first attempt was to assign the ID that was selected to a textbox on the main form, then automatically requery the second subform based on that value. I used this way on my search box. I'm sure there is a way to go directly from clicking the value to requerying the second subform, but i don't know it.
That being said even the indirect first attempt isn't working. It all has to do with the ".", "!", and ordering of this stuff. It has to do something with Parent child, but beyond that I'm at a loss.
Code:
Me.[Collateral] = Acct_Reference_id
Me.[Navigation]![textbox] = Acct_Reference_id
...
'Me!Subform1.Form!ControlName
'Me!Subform2.Form!ControlName
'Me.Parent!ControlName
I did find this very useful site [URL] ...... It shows what code you can use depending on where you are and what you're doing. Even with that as a resource, i'm still at a loss.
View 7 Replies
View Related
Apr 9, 2005
I would like to be able to apply more than one filter to a subform. It seems no matter how I try I can only have one filter affecting the records in my subform. I have had zero luck getting the child / master link on the form to return a satisfactory result in the subform as soon as i try to apply a second master / child filter. It seems the filters I'm trying to apply aren't 'stacking' and I seem to be getting either one or the other judging from the reports that are being listed in my sub form.
My data table consists of a few details concerning offices. Namely what Region the office is in, how many staff at particular grades, the office location / address contact details, and wether or not they reside in a 'strategic zone'. I've used check boxes on my data table to indicate wether an office is inside a strategic zone or outside a strategic zone.
My combo box is currently working great, it filters out all the records and only shows the offices that match the value in the 'Region' field in the subform. I just can't figure out how to get additional filters to work at the same time.
Ideally I would like to have the combo box which is working great, then a couple more conditions that the user can select to apply additional filter, especially the 'inside strategic zone' check box and the 'outside strategic zone' check box. I would also dearly like to have another combo box that will let users select 'Clusters' that an office may belong to and filter out those records that don't match.
I can use the right click on a field in my form and 'apply filter by selection' and of course that works great! Filtering all the way down until I'm left with a very short list that matches the same value of many filtered fields I've right clicked upon. But ewwww...that is not what anyone wants to have to rely on to give them data on the screen that conforms with the filter criteria they wish to apply.
Secondly, (sorry almost done!) how can I show the number of records that have a particular Yes/No check box ticked. Was thinking along the lines of having a couple of text boxes that count all the 'inside strategic zone' check boxes that are ticked and the same for 'outside strategic zone' How can I do this?
As I'm sure you will have picked up on, I am just setting out on my Access Database Learning curve, I have very limited experience but I am hoping to develop my own skills over the next few years. I have been humbled by the knowledge of the patrons here and warmed by the goodwill shown to helping the novices like myself who come here asking for your assistance.
Thanks for reading and cheers to you all.
View 1 Replies
View Related
Sep 29, 2006
Hi there,
I've been puzzling over this one for a few days. I've done a few searches on here to borrow some code - but the fact is I don't understand how to implement the suggestions. I'm hoping somone can help.
Basically, I have a form for editing the database, however, there are a number of reasons for opening this form - so at the moment I have several copies of the same form all with very minor changes. In order to streamline the way I've done this, I'd rather have controls always call up the same Form - but state in their code whether or not certain controls on the editing form are visible or not...
:mad: :eek: :mad: :eek: :confused:
It's all gone rather Pete Tong here. And I must admit I'm button bashing the code a bit now. Basically, when a button is pressed, I want it to load the form "frmEditJob". Depending on what I want visible, the Tags "N, M and V" will activate and deactivate certain controls on "frmEditJob".
With me? :confused:
Any help is appreciated!
Cheers.
Private Sub EditJob_Click()
On Error GoTo Err_EditJob_Click
Dim stDocName As String
Dim stLinkCriteria As String
If IsNull(Me![IDEntry]) Then
strMsg = "You need to enter a Valid ID number in the box provided."
strTitle = "ID Number Error"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Exit Sub
End If
stDocName = "frmEditJob"
Dim ctl As Control
stLinkCriteria = "[WorkorderID]=" & Me![IDEntry]
DoCmd.Close
DoCmd.OpenForm stDocName, , , stLinkCriteria
If ctl.Properties("Tag") = "N" Then
ctl.Properties("Visible") = False
Else
ctl.Properties("Visible") = True
End If
Exit_EditJob_Click:
Exit Sub
Err_EditJob_Click:
MsgBox Err.Description
Resume Exit_EditJob_Click
End Sub
View 5 Replies
View Related
Jul 23, 2014
Access 2007
I have a subroutine which cycles through all controls on a form, and for controls with applicable tags, either enables or disables the control.
The routine accepts a form as the parameter (frm), and is called from a button on the form itself.
Despite trying frm.repaint in the generic routine, and, me.repaint from the calling form, the controls do not seem to repaint until the mouse moves over them. A command button, for example, will be grey-ed out, despite being enabled, but will un-grey when the mouse goes over it.
View 8 Replies
View Related
Aug 16, 2013
I have a main form, lets call it FrmMain. In addition, I have a subform on FrmMain that has a tab control on it (frmSubform). What I would like to do is on form load, reference the tab control that is on the subform, loop through the controls/textboxes, lock them and change their backcolor. The code I am attempting is listed below, however, I am getting a "type mismatch error" highlighting this line:
PHP Code:
Set Frm = Forms!frmConsultingMain!FrmConsultingFeeData
Below is my code:
PHP Code:
Dim ctl As ControlDim Frm As FormSet Frm = Forms!frmmain!FrmSubformFor Each ctl In Frm.Controls
If (TypeName(ctl) = "Textbox" Or (TypeName(ctl) = "combobox") Or (TypeName(ctl) = "listbox")) Then
ctl.Value = Null ctl.BackColor = vbYellow ctl.Locked = True End IfNext ctl
View 10 Replies
View Related
Apr 29, 2014
I had never seen this before as I have recently moved from 2003 to 2010. When I create a from using the wizard sometimes the controls are grouped (inside a dashed square) which does not allow me to modify its size and/or position independently.
How can I "ungroup" these control?
View 2 Replies
View Related
Jul 9, 2014
On my data entry Form, I have a Combo Box [Condition] and five tab controls. Each tab has as many as 10 controls on it. The purpose of the Form is to populate a table. My questions are:
1) Do I still need to use a subform on each Tab or leave them as-is? i.e. the 10 controls are on each tab
2) How do I make the value of [Condition] transfer focus to the appropriate tab??
View 14 Replies
View Related
Apr 21, 2013
how to hide all form controls at once like if i have many text box and labels how to make a loop to hide it all at once
View 1 Replies
View Related
Aug 13, 2013
I have a library database with a form called Author and a subform from a junction table. The junction table connects Title/Genre. It's many to many because I usually have a few genres for one book.
This is my problem. I want to add more information to the subtable that is in the junction table. For every record I would like to add also Book Group (I am a member of several groups and want to remember which one I read the book with) and Purchased From.
So I am assuming I have to have 2 lookup controls to be able to add Book Group and Purchased from since neither one is on the junction table (and they both come from tables). Or do I add them to the junction table?
View 3 Replies
View Related
Nov 6, 2013
I have created a form with some boxes on it which include in their body textboxes and labels and combos. I would like to know if it is possible (or if a control exist) to group this items which are inside the box and with a simple box.visible = false to make them all non visible without tagging them or adding code with their names statically to make them non visible. Any link that shows how to move controls in the form in runtime?
I would like to hide some controls and make smaller the form and if user selects a checkbox to show these controls that exist inside a box and grow the form.
View 1 Replies
View Related
May 17, 2014
I cannot link a label with a control. I don't know how it happens, or rather how I do it, but on various forms the label is not linked to the proper control. I try to do it by clicking on the warning popup on the right side and indicate I want to associate the label with the control, but sometimes it just doesn't work. That is, if I set the visibility of the control to True or False depending on the value of another control, the label still appears.
I've looked in the properties of each, but there doesn't seem to be anything there to indicate the link or association. Other than laboriously copying and pasting the label back on the form with the control highlighted, is there any way else to do this?
View 11 Replies
View Related
Oct 23, 2014
In the attached picture has a representation of my setup.
My main form has a listbox search form (taken from this site). The search results populate data in subform1.
In the detail section of main form has subform1 with 3 cascading combo boxes. I have queries set up that run the cascading combo boxes.
In the detail of subform1, I have subform2 with a combo box.
Today I decided to merge my search form and the other forms so I wouldn't have two pages. Everything used to work with no errors - but I can't figure out how to reference everything properly now.
I've tried every combination from here to reference things properly. Somehow, I'm missing something.
View 12 Replies
View Related
Jan 7, 2014
I have a form that uses VBA codes to write all the info from the controls in the form into different relational tables. I was stopped at the point that I wanted to have repeating controls. Collect multiple instances of the same type of data, such as in the case of writing many people's Name, Age and Specialty in the Employees table. How do I do that? What is the VBA code for this task?
View 1 Replies
View Related
Jun 27, 2015
Which option in property window is responsible for group lining up of some controls vertically? how to ungroup them to allow resize one individually. I could not also find option in menu for removing pre-formatting of those controls.
View 3 Replies
View Related
May 8, 2014
I have a form with four pages and subforms on each page. I am having issues with scrolling on pages 2-4. The first page will scroll correctly (to the bottom of the form), but the other pages will not scroll at all. I did enable the scroll bars on the subforms, but they will only scroll to the bottom of the viewable form, not the whole form, which I would prefer. Is there a way to scroll through the whole form on pages 2-4? I tried setting the focus to the form itself, but my code didn't work:
Private Sub Form_MouseWheel(ByVal Page As Boolean, ByVal Count As Long)
Me.Parent.FRM_TABLE_TAB_1.SetFocus
End Sub
I would change the form; however, it is designed to look exactly like the actual questionnaire.
View 1 Replies
View Related
Oct 10, 2014
I have a form with 10 text boxes on it. depending on a Case
I would like to change the special effect and backcolor
select case colour()
case red
Me.Txt1.specialeffect = 1
Me.Txt1.backcolor = RGB(256,256,256,)
case green
etc
I'm trying to write something so that i can apply both these settings to any text box easily.
View 1 Replies
View Related
Sep 19, 2014
Is it possible to have a form and dependant on the security level of the user, will depend on what control will appear?
Or can I have the controls there and lock them to a user?
I have a DB of staff directory and I want to lock it so users can only view information, supervisors can edit and admin have full control.
I've done it so far by creating a different form and controls for each level but it a lot of work and queries. Is there a simpler way to do it?
View 6 Replies
View Related
