Navigating A Filtered List
Jun 8, 2005
I have a form that contains a pretty large list of items and several columns and the list can be sorted by each of these columns. When a user double-clicks on a particular item, another form pops up that lets the user view/modify the item. The problem I'm having is that I still want to be able to use the navigation buttons in that second form, but right now its just saying 1 of 1 (Filtered).
The way I'm applying the sorting is by updating the Rowsource field of the list with an "Order By" statement
Any help would be appreciated
Adam
View Replies
ADVERTISEMENT
Oct 9, 2006
Ok access noob here and a first post as well...I have to filter a table that has children and teachers.I made a form with a combo box of teacher names then filteredon the teacher name.in the subform I have to create a matrix of the children thatare assigned to that teacher.it would be something like thisteacher date child name start endthe teacher and child name are in tblmainthe date start and end values have to be in tbldata1so the beginning they want to choose a teacher and a dateso I shifted and put the teacher in a combo box and tied it to nothingbut the subformso combo box teacher and field datelinks to subform are teacher;datecombo box(teacher name)==> Ms. Eve text box(date)==> 01/01/06but they want a filtered list on teacher so the subform would have to beteacher date child name start endMs. Eve 01/01/06 mary lamb Ms. Eve 01/01/06 billy bullMs. Eve 01/01/06 bob jonesthen they could put in start and end for each childI don't see how I can get the child names filtered thru a query (thisI can do) BUT then to be able to edit the fields and put this datainto a new table.IS there a way to do this?a big help for any ideas or pointers as I am lost on this
View 3 Replies
View Related
Dec 5, 2006
Help !! - Can anyone help. I have had a quick look through the previous threads and can't seem to find anything that may help me. Has anyone got any ideas.
I have a table which has many columns, one of these columns is called actions. On the design of the table, the action column is a Text Datatype and on the lookup tab the row source is a typed in list of actions.
A form has been created from this one table. A drop down box has been created which looks at the action column and so drops down to give us our typed in list of actions.
Users will select the current action from the drop down list.
What I actually want to do is depending on what the current selected action is, that the drop down box only shows a subset / filtered list of actions.
I know how this is designed is not the tidiest/slickest way of doing things, But unfortunately, I have inherited this from someone else who has been running the database this way for over 6 years !!
View 1 Replies
View Related
Apr 17, 2014
I have a form which displays records based on a selection from a list box on the form.
The default record displayed when I load the form is record 1 from the table, this is not an issue when I initially load the form but I also have options to filter the records displayed in the list box using various SQL statements, depending on the options selected.
However, when I apply one ofthese filters to the list box the default record displayed on the form still defaults to the first record in the table, which may not be included in the filtered list box.
Is there a way I can default to the first record listed in the list box rather than the first record in the table.
View 2 Replies
View Related
Mar 21, 2013
I have three linked tables [Regulator], [RuleBook] and [Rules] (each has a corresponding form for data entry).
In the "Rules" form, when you select name of the regulator, the rule books dropdown down is filtered to show only the rulebooks for that regulator. the code I use is:
Private Sub Regulator_Change()
Me.[Rule Book].RowSource = "SELECT [ID],[Rule Book],[Short Code],[Regulator],[RegName],[Short Form],[Active] FROM" & _
" [RuleBook] WHERE Regulator = " & _
Me.Regulator
Me.[ShortReg] = Me.Regulator.Column(3)
End Sub
Basically, if I select the FSA as the regulator, The Rule Books drop down is filtered to only show the FSA Rule Books. If I pick OFT, I get the OFT list etc. This all works fine.
In the [RuleBook] table, each entry has a yes/no tick box called "Active".
The reason for this being that the regulators change their rule books quite regularly, but from an audit perspective, I can't delete or overwrite the old ones once they are no longer valid.
What I want to do, is further filter the rule book list to those where Active=True so that when I select FSA, I can only choose from their current rule books and not the ones that are no longer relevant.
View 7 Replies
View Related
Dec 15, 2014
I've created a form with two textboxes and a listbox. What I want to do is to show in the listbox the registers filtered by the 2 textboxes. But I would like that the listbox refreshes while typping in the textboxes.It was no problem to do filtering with only one textbox. The rowsource of the listbox is a query in which I set in the criteria Like [Forms]![Form1]![Textbox1].[Text] & "*"Then I write the code for the change event of the Textbox1:
Private Sub Textbox1_Change()
Listbox1.Requery
End Sub
With this I have no problem. The problem is when using the 2 textboxes. I write the same in the criteria, Like [Forms]![Form1]![Textbox2].[Text] & "*", on another field but it doesn't work. I also write the code for the second textbox:
Private Sub Textbox2_Change()
Listbox1.Requery
End Sub
The listbox doesn't filter with both textboxes. When you type in one of them, the listbox filters from it, but when you type in the other textbox, the listbox shows the whole table filtered with the characters of the second textbox.
View 10 Replies
View Related
Mar 8, 2013
I have a table with a list of Students. While the academic year is in progress those students are in an "Active" status. Once the year is finished those students are moved into (hopefully) a "Passed" status or some other status that is not considered "Active".
Throughout the rest of the database I have many forms that refer to the Student Listing. As an example we have a Test so we select the Student performing the test using a Drop Down List and go from there.
I have set search criteria to the Drop Down List via the Combo Box's Query to only display active students and it works fine during the Academic Year. However if I have to review a test from a previous year then the Student Drop Down List no longer displays the name but their ID# (Primary Key) because the Student's status has now changed and no longer meets the query criteria.
Is there a way to use the Filter/Search Criteria to chose from a listing of Active Students but once selected it will display the Student name regardless of their status?
View 2 Replies
View Related
May 26, 2014
I have a table with a huge master list (some hundred thousand) of unique bar code tags, which either have 8 or 12 digits. (Valid characters are "012 345 678 9ACEFHJKLMNPRYXW"). I created a form, which is automatically looking up valid records as the string is entered position by position from left to right. This is working fine and sufficient in 99% of the cases.
Sometimes characters are not readable on the original document, so I need a tool/form, were I can enter only the readable positions of the code and the tag list is then filtered accordingly. For that, I created a separate form with 12 text boxes for each possible position of the bar code and a listbox. This list box should be filtered as the readable characters are entered in their respective positions/textbox.
View 1 Replies
View Related
May 8, 2006
If an answer to this is out there, sorry. I haven't had luck finding anything.
I have a form with an unbound combo box. When you select an item from the box, the objects on the form and subform fill in based on the selection. The person using this would like to click arrows to navigate through the combo box. How do I do this?:confused:
View 2 Replies
View Related
Sep 9, 2005
Hi
with the help of this site, i have made big advances in learning and adapting code, in building my access FE on an SQL BE.
however, i have a problem, i cant find any information on in how to resolve.
i am using menus to navigate between forms, but i cannot synchronise the critical value between the forms.
so i am on a form with critical value, say hullID
i use a menu command to open another form, but i need the form to open with the same hullID as the critical value as the previous form which is about to close.
because i can go to the new form from any form, using the menu command, i cannot directly link the critical value,
is there a way to use the critical value from the current focus form, to the new opening form
many thanks in advance
View 8 Replies
View Related
Feb 22, 2005
Hello,
I need to navigate through all records for a table where a <field> = a certain "string". Ideally I would have a next record button that would know to ignore records that do not meet my criteria?
Simply put,
Next available record where <field> = "string"?
Is there anyway to do this?
Regards,
Chris
View 2 Replies
View Related
Aug 31, 2005
I'm having a problem with a form/subform. The table I'm using includes the fields: date, personnelID, status, comments. The main form has a single field, date. The subform has the fields date, personnelID, status, comments. I have the form/subform linked using the Date field, because there are multiple instances of the same date in the table, I have to keep clicking the navigation buttons on the main form to go through all the duplicate dates to get to the next one. Is there a way I can filter the Date field on the Main form to only show one copy of each date? I had thought about creating a new table with only a date field and no duplicates, then just append all dates from the main table to this one and use it to go through the dates, however I'd rather not have to make an extra table just for this. This is basically for personnel accountability, I want to be able to select a date on the main form and display the "daily status"(present, on leave) for each person in the subform.
Even better would be if I could create a next and previous button to switch the date on the main form(I know how to do this.) but how would I get the subform to update the records to the date on the main form and keep it from going to dates that don't exist?
View 1 Replies
View Related
Dec 7, 2006
Hi
I was wondering if anybody could help with this. I have searched through this forum and there doesnt seem to be an answer to this.
I have a form that i would like to validate. At present i have put the data validation on the save button which triggers a script that checks to see if certain boxes have been filled in on the form. I can only get this to work be attaching it to the onclick event on a save button, however, what i really want is to be able to ditch the save button (since access writes to the DB as it goes along) and have this script triggered whenever the user tries to navigate to either a new record/ another existing record or close the form
I tried putting this on the beforer update event, but this does not work, additionally tried doing before update event with the code inside an if me.form.dirty = true statement
this did not work either, has anybody got any suggestions
Please see my code below
Thanks
Marcus
Dim sDeliveryto As String
Dim sDeliveryValid As String
Dim sDept As String
Dim sDeptValid As String
Dim sReq As String
Dim sReqValid As String
Dim sReqNo As String
Dim sReqNoValid As String
Dim sReqPoint As String
Dim sReqPointValid As String
Dim sOrderDetailsValid As String
Dim sQuantity1 As String
Dim sQuantity1Valid As String
Dim sDetails1 As String
Dim sDetails1Valid As String
Dim sPrice1 As String
Dim sPrice1Valid As String
Dim sSupplier1 As String
Dim sSupplier1Valid As String
Dim sCostCentre1 As String
Dim sCostCentre1Valid As String
Dim sAccountCode1 As String
Dim sAccountCode1Valid As String
Dim sAuth As String
Dim sAuthValid As String
sDeliveryto = Me.TBDeliveredTo & ""
sDept = Me.TBDept & ""
sReq = Me.tbrequisitioner & ""
sReqNo = Me.TBRequisitionNo & ""
sReqPoint = Me.TBReqPoint & ""
sQuantity1 = Me.TBQ1 & ""
sDetails1 = Me.TBD1 & ""
sPrice1 = Me.tbup1 & ""
sSupplier1 = Me.tbs1 & ""
sCostCentre1 = Me.ccc1 & ""
sAccountCode1 = Me.tbac1 & ""
sAuth = Me.TBAUTH & ""
Select Case sDeliveryto
Case Is = ""
Me.TBDeliveredTo.BackColor = "8421631"
sDeliveryValid = "Invalid"
Cancel = True
Case Else
sDeliveryValid = "valid"
Me.TBDeliveredTo.BackColor = "16777215"
End Select
Select Case sDept
Case Is = ""
Me.TBDept.BackColor = "8421631"
sDeptValid = "Invalid"
Cancel = True
Case Else
sDeptValid = "valid"
Me.TBDept.BackColor = "16777215"
End Select
Select Case sReq
Case Is = ""
Me.tbrequisitioner.BackColor = "8421631"
sReqValid = "Invalid"
Cancel = True
Case Else
sReqValid = "valid"
Me.tbrequisitioner.BackColor = "16777215"
End Select
Select Case sReqNo
Case Is = ""
Me.TBRequisitionNo.BackColor = "8421631"
sReqNoValid = "Invalid"
Cancel = True
Case Else
sReqNoValid = "valid"
Me.TBRequisitionNo.BackColor = "16777215"
End Select
Select Case sReqPoint
Case Is = ""
Me.TBReqPoint.BackColor = "8421631"
sReqPointValid = "Invalid"
Cancel = True
Case Else
sReqPointValid = "valid"
Me.TBReqPoint.BackColor = "16777215"
End Select
If Len(sReqPoint) < 6 Then
sReqPointValid = "Invalid"
Me.TBReqPoint.BackColor = "8421631"
Cancel = True
Me.lblReqPoint.Visible = True
Else
sReqPointValid = "valid"
Me.TBReqPoint.BackColor = "16777215"
Me.lblReqPoint.Visible = False
End If
Select Case sQuantity1
Case Is = ""
Me.TBQ1.BackColor = "8421631"
sQuantity1Valid = "Invalid"
Cancel = True
Case Else
sQuantity1Valid = "valid"
Me.TBQ1.BackColor = "16777215"
End Select
Select Case sDetails1
Case Is = ""
Me.TBD1.BackColor = "8421631"
sDetails1Valid = "Invalid"
Cancel = True
Case Else
sDetails1Valid = "valid"
Me.TBD1.BackColor = "16777215"
End Select
Select Case sPrice1
Case Is = ""
Me.tbup1.BackColor = "8421631"
sPrice1Valid = "Invalid"
Cancel = True
Case Else
sPrice1Valid = "valid"
Me.tbup1.BackColor = "16777215"
End Select
Select Case sSupplier1
Case Is = ""
Me.tbs1.BackColor = "8421631"
sSupplier1Valid = "Invalid"
Cancel = True
Case Else
sSupplier1Valid = "valid"
Me.tbs1.BackColor = "16777215"
End Select
Select Case sCostCentre1
Case Is = ""
Me.ccc1.BackColor = "8421631"
sCostCentre1Valid = "Invalid"
Cancel = True
Case Else
sCostCentre1Valid = "valid"
Me.ccc1.BackColor = "16777215"
End Select
Select Case sAccountCode1
Case Is = ""
Me.tbac1.BackColor = "8421631"
sAccountCode1Valid = "Invalid"
Cancel = True
Case Else
sAccountCode1Valid = "valid"
Me.tbac1.BackColor = "16777215"
End Select
Select Case sAuth
Case Is = ""
Me.TBAUTH.BackColor = "8421631"
sAuthValid = "Invalid"
Cancel = True
Case Else
sAuthValid = "valid"
Me.TBAUTH.BackColor = "16777215"
End Select
' Display message box warning
If sDetails1Valid = "Invalid" Or sQuantity1Valid = "invalid" Or sReqPointValid = "Invalid" Or sReqNoValid = "Invalid" Or sReqValid = "Invalid" Or sDeptValid = "Invalid" Or sDeliveryValid = "Invalid" Or sPrice1Valid = "Invalid" Or sCostCentre1Valid = "Invalid" Or sAccountCode1Valid = "Invalid" Or sAuthValid = "Invalid" Then
MsgBox "Please fill all highlighted fields on the form!!!!!"
Else
DoCmd.Save
MsgBox "Is all the information Correct?", vbOKCancel
'open report
Me.btnClose.Visible = True
Me.btnInvoice.Visible = True
Me.btnDeleteClose.Visible = False
End If
View 3 Replies
View Related
Apr 10, 2015
I have made an access database for doing inventory at work. We have an inventory of approx. 700 items that need to be counted each month. I have a subform that is in datasheet view, it has Inventory IDs, product descriptions, UOMs, and a field to enter quantities. On the parent form are pictures and extra descriptions linked to each item in the subform.
The Inventory IDs all start with a 3 letter alpha code for which class of item they are PLU for plumbing, FAS for fasteners, ELD for electrical device, etc. There are about 15 different classes of these inventory items. The rest of the InvIDs are 4 digits: (PLU5453 or ELD1123)
We are using tablets to do the inventory counts, so to search for an item we can swipe through the datasheet to find the InvIDs we are looking for and then enter the quantity. After our initial inventory my supervisor asked if I could add buttons to jump to the first item in a particular class of item such as PLU0001. He showed me his address book in his phone as an example, how he can push a P button to bring him to the first name that starts with P.
I considered tabs for each class, but he wants to navigate to other items by just swiping also. I tried to make a Macro that would use Go To Record but was unable to make that work.
View 1 Replies
View Related
Sep 19, 2013
When using Access 2007, I have a new record button, but that record is only written to the db once you navigate away from it. How do I write immediately to the DB, so that record is saved without navigating away from it. I believe I can use ADO or DAO but I'm having difficulties.
Private Sub Command71_Click()
DoCmd.GoToRecord , , acNewRec
MaxValue = DMax("[Record Num]", "Joblog")
Text64.Value = MaxValue + 1
View 9 Replies
View Related
Jul 1, 2013
I have a form that reads records from a query.. It loads the first record into the form, without issue.
Code:
Sub FormLoaded()
Dim r As DAO.Recordset
Set r = CurrentDb.OpenRecordset("Results") ' Query we want
Forms("frmmainnew").lbladdUser1bad.Visible = False
[Code] ....
Now I have have 2 buttons at the bottom of the form, one for next record and another for previous record...
Code for next record
Sub NextRecordbtn()
Dim r As DAO.Recordset
Set r = CurrentDb.OpenRecordset("Results") ' Query we want
r.MoveNext
If r.EOF = True Then
[Code] ....
I know that I have 3 records that result in the query named "Results", the next record button will bring the form from record 1, to record 2... however it will not move from the 2nd to the 3rd record...
View 14 Replies
View Related
Oct 4, 2005
Hi, Sorry noob here with a noob question... I built my first database for my work, and I want buttons that can navigate through the records on the form view, but I want them to be like A,B,C,D etc... When I click the button I want it to go to the first last name in the record of that letter of alphabit.... I know it can be done because at my previous employment they had that feature. Please help... I can't figure this out... :confused: Also, how do I import IMT forms into an access database???
View 1 Replies
View Related
Sep 11, 2006
I have a main form that displays four lines of data. I have a tab control with 3 tabs. The form displays perfectly until I navigate to one of the other tabs and back to the main tab. It appears to scroll down a couple of lines and I loose the top line of data.
Any idea what is happening or how to stop it?
:confused:
View 1 Replies
View Related
Aug 10, 2013
I have a form and a subform for data entry.It is often the case that not all the data may have been entered and so a user might need to go to recordID 24 in 30 already entered main records.However, he needs to get to that record via a (unique) 1-1 field. How do I create the form so that as well as being able to go to the first, the last, the next, the previous record, I can go to a record of my choice.
View 5 Replies
View Related
Aug 7, 2014
First thing: I have a navigation form that leads through 5 subforms on my database. One is a data entry form, the rest are various informative forms with explanations, images and other text mainly. In this scenario, a user enters data on the first tab but doesn't finish it. The user clicks over to check up on some guidelines on the other tabs and then comes back to the first tab for find that all of the fields he entered reset and he lost everything.
Is there a way of preventing this from happening? Plan A would be for me to allow the user to switch around tabs without losing the data he or she entered into textboxes or dropdowns. Plan B would be for me to have a popup that appears when he tries to switch tabs that warns him of data loss and that he should save before moving tabs.
PS: One other thing: I have the standard Access wizard search button on my first tab that allows the user to search records. This works well, but I'd like for the search box to close immediately after a search turns up a matching record. This seems odd, but the users requested it because manually closing the search box after every search can add up to a lot of wasted time.
View 8 Replies
View Related
Jan 22, 2014
I have a form which consists of four (4) tabbed sheets. The first sheet is the main form and the other three sheets are the linked subforms.
Pressing the tab key on the keyboard navigates from field to field in each individual form; however, I would like that when I get to the last field of each form, pressing tab moves to the next sheet or the first field of the next subform.
The problem I am getting is that when I get to the last field of the main form and/or subforms, hitting the tab button creates a new record in the specific form, which I do not want. I want to just tab to the next subform.
View 14 Replies
View Related
Dec 23, 2014
I have a custom command button cmdPreviousRec and cmdNextRec for navigation, but when i click the cmdPreviousRec its not navigating to the records and the cmdNextRec is up to 2nd record only.
Code below is the i've working:
Private Sub cmdNextRec_Click()
On Error Resume Next
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM tblAMHMace")
If Not rs.EOF Then
rs.MoveNext
[Code] ....
View 5 Replies
View Related
Oct 11, 2005
Hi all, complete Access n00b here requiring some assistance if possible with the dreaded cascading combos. Would appreciate any help I can get. I've tried searching but can't find this particular problem mentioned (am probably searching for the wrong thing, but anyway...) Here is my situation:
I have a form [Component] with a simple cascading combo box scenario [cboCategory] and [cboSubcategory].
[cboCategory] has as its row source -
SELECT tblCategory.categoryid, tblCategory.Category FROM tblCategory ORDER BY tblCategory.Category;
Control source is unbound.
I have Me.cboSubcategory.Requery as an afterupdate event.
[cboSubcategory]'s row source is -
SELECT tblSubcategory.subcategoryid, tblSubcategory.subcategory FROM tblSubcategory WHERE tblSubcategory.Categoryid=Forms!Component!cboCateg ory ORDER BY tblSubcategory.Subcategory;
Control source is bound to SubcategoryID in tblComponent.
I have set up [tblCategory] as the one side of a one to many relationship with [tblSubcategory] and [tblSubcategory] as the one side of a one to many relationship with [tblComponent]. [SubcategoryID] (foreign key) is the only piece of 'category' information I am storing in [tblComponent]. I presume that's the correct thing to do since you can calculate the category from the stored subcategoryID.
The problem is that the form only seems to remember the most recent combo choice so if i fill in one record, navigate to another use the combos again, then navigate back, the original combo choice has been overwritten in the form record (although the underlying tblComponent seems to have the correct data written to it). In other words ALL records on my form display the last values that were entered using the combo boxes and not the correct values. Is this normal? I hope not, and if not then what am I doing wrong?
Thanks in advance :)
View 10 Replies
View Related
Mar 5, 2013
I need to ensure that the data entered into a field on a form is correct. The field contains an email address and I have a function to check if the format is correct.
If the format is incorrect I need the cursor to return to the field in that record. The form is continuous.
What event should I use?
View 2 Replies
View Related
Jun 26, 2006
Hi,
I have a data entry form/subform with an embedded graph allowing users to preview the data they input. When I navigate to a new record in this form the the fields in the form and subform echo "#Deleted". Yet the data is still there, it is just not shown in this form.
I have tried copying all the controls and code into a new form but the same error still occurs!
Any thoughts?
Bobadopolis
View 3 Replies
View Related
Aug 2, 2005
I would like to know how I can put filter fields on a DAP that when you press a button will then filter a table. Also, does anyone know of any good references on working with DAP, becasue from what it seams like, there isn't any technical information out there.
View 1 Replies
View Related
