Filtering On A Combo Box Based Off A Autopopulate Field
Hi, i've searched the forums but couldn't find an issue/resolution that fit my problem. I tried to put as much info as needed but please let me know if more info is needed.
I've set up one of my forms that require a signature with a combo box "CurrentUserName" to capture an electronic signature from a jpeg stored on the local server.
The CurrentUserNamecombo box has a one-to-many relationship between another table named "Electronic Signature" that holds the user's network ID and the Path on the server where the signature file is stored. The primary key in the second table is also named "CurrentUserName". This works perfectly, i select a username from the drop down list on the form and the picture atumomatically populates an unbound image field with the image.
The issue is, i only want the current user to be able to select their own username from the combo box on the form and hide the other usernames so i need the CurrentuserName combo box to be filtered on the form.
So what i've done is create another field named "CaptureUser" that will automatically capture the current user's network user ID using the =CurrentUser () as the defualt value of that field once the form is opened. What i need to do now is connect the two fields so for instance, if current username "jblow" is in the CaptureUser field, the CurrentUserName combo box will check the CaptureUser field and filter the choices so his name will only appear in the CurrentUserName combo box.
Hope that made sense, any help is greatly appreciated!
View Complete Forum Thread with Replies
Related Forum Messages:
Design Problem - Filtering Combo Box Items Based On Related Table Field
Hi im stuck on filtering a combo box (i am using an sql query to populate a combo box with a filtered selection, this is in Access but im after some design sanity checking). I have users who can be assigned a category. Jobs can be assigned a category and also have a list of people working on that job (in the JobDetails table). I have the following tables: Categories Table: CategoryID (PK) Description Users Table: UserID (PK) Username CategoryID (FK on Categories.CategoryID) Jobs Table: JobID (PK) CategoryID (FK on Categories.CategoryID) JobDetails Table: JobID (FK on Jobs.JobID) UserID (FK on Users.UserID) Then in the job details when listing users for a job (many users can be for one job) i would like to only show the users which have the same category as the jobs category. Is this possible? i tried the SQL below for the lookup column field JobDetails.UserID but it doesnt work: SELECT Users.ID, Users.Username, Users.CategoryID FROM Users, Jobs WHERE (((Users.CategoryID)=[Jobs].[CategoryID])); All the tables are linked with relationships but my SQL isnt so hot! Any ideas as to how i would do this and get it working? Even if it can be done, is this even recommended? I can see funny conditions happening if the job details category changes or the users category changes then even if they are existing in the job details list they will not be shown? Even so, i would be interested in the above to know how it is done (if possible). Thanks in advance, Chris
View Replies !
Autopopulate A Field In Table Based On Another Table
Hello everyone. How do I go about automatically inserting a date from one table into another table. This are my tables: tblPAF (Personnel Action Form) PAFNo EmployeeNo Date Department Division Salary Comments tblBenefits BenefitsNo EmployeeNo Date BenefitType CoverageDate Every time an employee’s salary or benefits changes we fill-out a PAF (paper format) on it we write all the changes. In my PAF db form I’m thinking adding a command button to open my Benefits Form. Once my Benefits form is open I don’t want to renter the date that was entered in the PAF form I wanted to automatically insert that same date. Hopefully I made myself clear. Thank you for your assistance. HR
View Replies !
Autopopulate Related Field
I have a table that I'm storing Horsepower and Watt in. Is there anyway to set it up so that when I provide Horsepower it automatically populates Watts? (just a simple multiplication)
View Replies !
Need To Autopopulate A Field In A Form
Have form as follows. Can # (Lookup/dropdown) Movement Date (date/time) Current Location (lookup/Dropdown) Current Address (lookup/Dropdown) Prior Location Prior Address When I drop the can # down in the form, I want the Prior Location and Address to auto Populate with the last known current Location and address of that can. Any ideas on the best way to do that?? I assume there is code and a query, but I am an absolute database illiterate so exact directions would be nice. To explain again, whenever I put a new record in, as soon as I select the Can# from the drop down tab, I want the prior location and address to be populated with the last known current address for that Can#. And if I change the can # I want the prior location and address to change with it. HELP PLEASE.
View Replies !
Filtering Field In 2nd Subform Based On Selection In 1st Subform
I almost have this form done. 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 hope that someone can help with this! :confused:
View Replies !
Filtering Field In 2nd Subform Based On Selection In 1st Subform
I have a form that is almost complete 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 hope that someone can help with this!
View Replies !
Auto Insert Field Info Based Upon Combo Box
Someone once posted an database on how to do this but I was not able to see how they were able to automatically input the data into a text field. I know how to do a combo box to query and input those results into a combo box but I am confused how they got the information from the combo box selection to automatically put the other variables into the testbox. They did this without using the dlookup command. In fact it was done without any code. They showed how to do this automatically without code and with two other methods (one being dlookup). Can someone please help me if it is clearcut as I don't have too much time to search the forums for empty answers (believe me I have already wasted too much time) Thank You
View Replies !
Field Values Based On User Selection In Combo
Hello, I'm stuck! I want to create a form which has a combo box where you select someone's name, and their address appears in another text box on the same form. The names and addresses are in a table called "cardholders". I'm doing separate combos for first name, surname cos I think that's the only way you can do it. The fields in the table are credit_add, credit_name & credit_surname. The combos are called "cardholder name" "cardholder surname" etc. This is the code in the text box where I want the address to appear:- =DLookUp("[credit_add]","cardholders","[credit_name] = '" & [cardholdername] & "' And [credit_surname] = " & [cardholdersurname]) Can anyone help? Thanks.
View Replies !
Filtering Report Based On Records In A Form
I am making a Customer Contacts database. The people in this database are organized into 30 groups. I have one main form, and 30 queries to filter the records displayed on the form I made buttons on a form representing the different groups of contacts to view, and when you click on one of those buttons, the main form opens with the record source as the query of the group you clicked on. Here is my coding for one of the buttons DoCmd.OpenForm "MainForm" Forms![MainForm]![HomeAddress].Form.RecordSource = "Company Work" Also on the main form, I have a button to open a report that displays address labels of all the people in the database How do i code that button, so that the records displayed in that report will be the same as those records displayed on the form? How do i filter the records in the report so they will be the same as in the form?
View Replies !
Filtering On Combo Text
Aaaargh! It's a couple of years since I got my hands dirty in Access and I seem to have forgotten more than I thought! :( I have a simple database which includes tables STOPS (stopID, stop name, routeID, Cost band), JOURNEY(CustID, routeID, stopID), and ROUTES (routeID, Route name, Bus co). I have a main form with the customer number on linking to a sub-form on which the user will select the route from a combo box and then the stop from a combo box. All works fine but I want to filter the stop combo box to show only stops on the route already chosen. I've done similar before but I have tried all sorts and failed to get anything to work. Any help appreciated.
View Replies !
Filtering On Combo Boxes
I have a question about filtering on combo boxes. I was able to set up a form with 2 sub-forms, but it's more than what I need. What I want it to do, is when the user makes the selection on the first combo box, I want it to filter on that selection and then drop that into the 2nd combo box. When the user makes the selection on teh 2nd combo box, then it should filter the results to a sub-form. Any help? I have the relationships, and can get it to work when I have 1 form, and 2 sub-forms, but I really want just 1 form, with 1 subform.
View Replies !
Filtering Employee Name From Combo Box.
Hi all, My database is like 1 Training course with many of the employee. My Training course are using the reference no. as the ID of my training. Now I have a form that have a reference no. as my first combo box and all the employee name are in my second combo box to let the user select and add employee to the training. So now what i need to do is how to filter out the employee that had already been added to the specify training? For example now user want to select a training 'A' from combo box with only 10 employees (out of 50) are involve in the training. And now when user select a employee and click save then the employee name will be disappear from the combo box when the second time user want to select another name. So do someone know how to do the filtering of the existing employee in the training? Thanks alot@@!! Regard, alex
View Replies !
Complex Combo Box Filtering
Ok, so I invoke your help once again. Here's the situation: I have a list of Crew Leaders. Each crew leader has the option of being the leader of up to three different types of crews, which is determined by checkboxes. In a separate form, I would like to do the following: The first combo box contains a list of the three types of crews. Once an option is selected, I would like only the Crew Leaders' names corresponding to that selection to show up in a second combo box. However, they are not ONLY part of this selection, necessarily. They may also be a part of a different selection, as they may be the leaders of one, two, or all three types of crews. The third combo box I think I can figure out (the first box filters different services that the crew can provide, but it is irrespective of the name of the Crew Leader, so the services are crew TYPE specific, not CREW specific). I've done it where the first box filters the second box, but only when the selection in the first determines an exclusive set in the second. What I'm trying to do is have the first box create a non-exclusive set in the second box. Ex: Crew Type: A Crew Leader (Selection pool): Joe, Bob, Jim, Jerry, Phil, Mark Crew Type: B Crew Leader (Selection pool): John, Bob, Jim, Mike, Barry, Dan, Mark Crew Type: C Crew Leader (Selection pool): Rob, Jack, Jim, Joe, John, Mark Anyone have any ideas? So far you guys have been amazing helps. Thanks in advance! -Jason
View Replies !
Need Help On Filtering Records Using Combo Or List Box
Hi, I need help on my search command. I am hoping to be able to use a combo box or a list box coming from a table as my criteria to use to filter records from a form and present it a subform/subreport upon clicking the command button. Ideally I should have a form wherein I will have a either a combo box or list box for my criteria, a subform/subreport, and a command button. When I select a particular item on the combo box or list box and I click the command button, the subform/subreport would show me records matching only the particular criteria I selected. I tried using several approach but it's not working, I don't know what I'm doing wrong. Please help me, I am just learning how to do this all by myself. First Approach: I tried using a list box to list all the countries I have available from the country table and a command button so when I select a country from the list box and click on the command button I will be able to show on a datasheet view only records matching the country criteria. This is the code I used: __________________________________________________ _______________ Private Sub Preview_Click() DoCmd.OpenForm "qrysumcountry subform", , "Country", "Country = [List4]" End Sub __________________________________________________ _______________ But everytime I click on the command button Preview, I am always asked to enter parmeter value then when I type the country that's when it shows the record in forms format matching the criteria country but when I dont type anything and click ok, it just shows a blank form and indicates it's filtered but no record is showing. But I click cancel, it shows a Run-time error '2501'. why does it still have to make me type the parameter if I have selected it on the list box already? Second Approach: On the form: I used a combo box, a subform/subreport and a command button. On the combo box I have to show different countries available on my country table. on the subform/subreport I have used my a form created from a query. I want to select from the combo box a particular country and used it as my criteria to filter the records I have on my subform when I click on the search command button. I tried following the sample given by gromit but it doesn't want to work on my database. This is the code I followed: __________________________________________________ _______________ Private Sub btnClear_Click() Dim intIndex As Integer Me.cmbCountry = 0 End Sub Private Sub btnsearch_Click() Me.frmqrybyCountry1.Form.RecordSource = "SELECT * FROM qrybycountry" & BuildFilter Me.frmqrybyCountry1.Requery End Sub Private Function BuildFilter() As Variant Dim varWhere As Variant varWhere = Null ' Main filter 'Check if there is a filter to return... If IsNull(varWhere) Then varWhere = "" Else varWhere = "WHERE " & varWhere ' strip off last "AND" in the filter If Right(varWhere, 5) = " AND " Then varWhere = Left(varWhere, Len(varWhere) - 5) End If End If BuildFilter = varWhere End Function __________________________________________________ _______________ After selecting on the combo box and click on the command button it just shows all record. It doesn't seem like it is reading what I selected from the combo box as my criteria to filter the records. What could be I be doing wrong? Honestly, I dont understand what is happening to the code here especially the BuildFilter function. Please help me, I would really want to figure this problem out. Thank you so much.
View Replies !
Filtering Out Items In Combo Boxes
Need some help here.... I am designing a database to keep track of workers for a haunted house. I have a Roster table, a table of all the nights we are open, a table of Spots in the house, and a table to record who works what nights and what spot they are in. This table has a Room combo box and a Spot combo box. The Room combo has a Row Source of SELECT DISTINCT SpotsAll.Room FROM SpotsAll ORDER BY SpotsAll.Room; The Spot combo is then populated with VB code all the Spots that are in that room. That works fine. This is what I'm trying to accomplish: When a Spot is assigned to a Worker for that night, I want that spot to no longer be available in that list FOR THAT PARTICULAR NIGHT. So, lets say Joe Somebody works in Spot 1 (out of 4 lets say) of the Library Room, when we assign another person to the Library room, I don't want Spot 1 in the Spot list. Can this be done with a query or VB code? If I'm not making sense, please let me know. I can also upload the Database I am creating so that you can play around with it and see what I mean. (I have attached a word doc. that shows the table relationships.) Thanks a lot ahead of time!! ScrmingWhisprs
View Replies !
Combo Box Subform Filtering Problem
Hi all I have been using some code from this site to filter my forms and subforms via a selection of combo boxes. However, when I try to filter by either date or callid, I get runtime error 2001 and the debugger drops in at the first forms!filter line. This seems to work perfectly well for the other 3 combos that I use. Is it to do with the type of fields I am trying to filter with or am I missing something integral to the code? Each combo is named Filter1, Filter2, etc and the name of the field to filter by is placed in the tag line for the corresponding combo box. Private Sub Command13_Click() Dim strSQL As String, intCounter As Integer ' Build SQL String. For intCounter = 1 To 5 If Me("Filter" & intCounter) <> "" Then strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _ & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & _ " And " End If Next If strSQL <> "" Then ' Strip Last " And ". strSQL = Left(strSQL, (Len(strSQL) - 5)) ' Set the Filter property. forms!frmoutershell.frminnershell.Form.frminnerinn ershell.Form.Filter = strSQL forms!frmoutershell.frminnershell.Form.frminnerinn ershell.Form.FilterOn = True End If forms!frmoutershell.frminnershell.Form.lblfilter.C aption = strSQL End Sub Any help would be appreciated as I am getting quite frustrated with it only working on certain parts of the form. Regards Jason
View Replies !
Filtering Combo Boxes Within A Form
I have a form that we are creating to issue project numbers. We have about 80 different clients. I have set up a combo box for the client name, where we can choose the client from the drop down menu. We want the form to filter according to the client name that you choose. For example, if you pick Lawrence as the client name, the next combo box is the project name and I want it to only show the project names that have been assigned to Lawrence in the past. Can someone please help me on how to do this and if it is even possible at all? Thank you so much for your help!! I have been wrestling with this for about a week and a half now.
View Replies !
Combo Boxes Filtering Or Populting Same Records
Anyone help I have 2 combo boxes cd group and cd name when I select CD group the records only associated yo that cd name display with that group. Anyway, also the name of songs form works too,. So then now when I go from record to record the records display right with the songs and not with the cd group and name when I select Mormon Tabernacle Chior and name God Bless Ammerica it disaplys for all the records any help. Why is it the relationships. Thanks, Nike Van der stad mikevds@optonline.net
View Replies !
Help Needed Filtering A Form Using Combo Boxes
Please help. My inability to achieve this is very frustrating. I have a tabular form based on a query, which returns a large number of records. I need users to be able to filter the records to display data relating to what they have entered in a number of combo boxes. For example I have fields showing Product ID, Manufacturer ID, Purchase Order number and due date. Each of these fields has a corresponding unbound combo box where users can select values that appear in the table. I need to be able to filter the form based on what is in these combo boxes. I.e. Filler for a product ID to show all purchase orders numbers and due dates relating to that product ID or filter for a manufacturer ID and due date to show all Product IDs and purchase orders relating to that a manufacturer and date. Ideally this would be achieved by pressing a command button. The query, table and combo boxes are all built and work fine but I am unable to get the filter to work, I am using Access 97 and am not very familiar with visual basic. Any help would be appreciated. Thanks.
View Replies !
Change Combo Contents Based On Previous Combo Selection
Hi everyone, I have two combo boxes on the same form bound to a table. I want the contents of the next combo box to change based on the previous combo selection e.g cboContinent cboCountry Africa Zambia Africa Congo Africa South africa Europe England Europe Holland If I choose Africa in cboContinent, I want to see only Zambia, South Africa and Congo under cboCountry and if I choose Europe I want to see only England and Holland Thanks Humphrey
View Replies !
Constraining Combo Box Values Based On The Value Selected In Another Combo Box
Thanks in advance for your help. I would like to set up rules/constraints such that the value selected in combo box A determines the available values in combo box B. For example, "Combo box A" is bound to the [PartnerType] field and "Combo box B" is bound to the [PartnerRole] field. Let's say that the two choices in Combo Box A are "LLC", "LP", and "Corporation". There are 5 possible choices in Combo Box B: "X", "Y", "Z", "Q", and "U". If a user selects "LP" in Combo Box A, I would like Combo Box B to only show choices "X" and "Y". And if a user selects "LLC", only choices "Y", "Z", "Q", and "U". Similarly, I would like to set this up so that Combo Box B is not initially visible--it becomes visible when a user selects "LLC" or "LP". If a user selected "Corporation", Combo Box B would remain hidden.
View Replies !
|
TRACKER
