I am trying to run a check that will make the user aware if the request they are adding via the form is likely to be a duplicate. If so I want the option to allow or reject the entry. Alternatively I have seen solutions that just notify on the form is something might be a duplicate, by highlighting a textbox red for example.There will be duplicates across these fields but there are many other options included on the form, as I said this is just an indicataion.
The table is called "MainAc"
The form is called "SDC Request Form"
The comparison fields are called: "First_name" - Text "Surname" - Text "Change_Number" - Number "Date_from" - Date "Date_to" - Date
Ive been trying the Dcount() function, IIF() function but no joy.I tried to putting the below in the before update event, but it didnt run at all, now Ive put it on the button click acion that duplicates records for me, however it just returns a non duplicate responce regardless.
Dim PreviousRecordID As Long PreviousRecordID = 0 PreviousRecordID = DLookup("first_name", "MainAc", "first_name<>" & First_Name & _ " AND surname=" & Surname & " AND change_number=" & Change_Number)
[code]...
This was a copy and modify from an example database but I dont entirely understand what each part does.
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.)
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.
I have a form with a number of fields for users to enter data into, with a "Total" being calculated. The users also have the option to ignore these fields and just enter a total manually into a seperate "Totals" field.
I want to set the form up so that when the form is opened the manually entered "Totals" field is hidden, leaving users to enter the component values, with the total being calculated automatically.
However, I want to add an option button so that if users just want to type in a total manually rather than enter the component values, they click the option button and the manually entered "Totals" field appears and the other component fields become disabled. This would prevent both a calulated total and a manually entered total being entered into the form.
Any help on the code that I need to use when the option button is clicked would be greatly appreciated.
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.
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.
I have a sub form that I enter date range in 2 text box's txtstartdate and txtenddate this is filtering field "date raised" and I have combo box call cboclient this is filtering field "client name" this code is behind a command button after hitting the command button it open's the report and it show's all records with in the date range and with client name enter in cboclient works great. problem is I have now added another cboclient2 to filter client name twice so now I enter a date range in txtstartdate and txtenddate put a client name in cboclient and a client name in cbocleint2 hit command button it open's the report but doesn't apply the date range to cboclient2 it just show's all records with that client name
Code: Private Sub cmdPreview_Click() 'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long
I'm attempting to filter my form with combo boxes. I've added the below code, but the if statement that checks if one of the combo boxes is null won't work. If the combo box is null or "", it's supposed to assign a wildcard to the variable. But it doesn't pass the test and goes straight to the Else statement. I've highlighted the if statement that keeps failing in red.
Code: Private Sub btn_Search_Click() 'create variables to store the combo box values Dim str_Country As String Dim str_Vendor As String Dim str_Survey As String
I am using master/child form for data entry for packings and the details for this packings (i.e. the products inside a packing is entered in subform and packing master is entered in main form ).
In the detail subform i use a productId field which has look up from 1000 products , so i want to filter that particular combo box based on different types from product master ( from which it choses the products).
How many ways are there to filter that combo box based on different types ( which i have in product master as type,design,material etc).One way of which is combo boxes on main form.
I have three comboboxes and I want to have the first one filter the second one and the filtered second one filter the third one.
Filtering the first one on the second one works, but when I whant to add a third one to it, it just shows blank spaces on the third one.
What I did to the first and the second combobox :
1: I pressed the second combobox. 2: Went to rowsource. 3: filled criteria in on the value i whant to connect the first combox to the second combobox ( [Forms]![Thetableimin].[1stcombobox]. 4. requery the second combobox in programcode on the first combobox.
What I did to the second and the third combobox:
1: I pressed the third combobox. 2: Went to rowsource. 3: filled criteria in on the value i whant to connect the second combobox to the third combobox ( [Forms]![Thetableimin].[2ndcombobox] 4. requery the third combobox in programcode on the second combobox.
How do I make a third combobox that uses the filtered values of the second combobox to filter the third combobox.
I am new in MS Access programming and is trying to create a search form for all records based on selected dates and check box. I was able to filter the records by dates but can't find ways to filter the records that meet the dates to be filtered again based on the selected check box/s. Basically what I wanted to is display all the records within the selected dates and check box/s.
I thought I was in the home stretch of my project, everything worked great when I was messing with 200 records. Now that I'm messing with 2000+ records, things are very slow. So a search form redesign!
I have a form (frmSearch) that has a subform (frmSubSearch) embedded in it. The frmSubSearch is a datasheet only that is just pulling its info from a query (qrySearch). qrySearch has about 8 columns of data in it.
On the main form I have a combo box that is feeding its list from the qrySearch using a SELECT DISTINCT statement. So a user selects an item in the combo box and my After_Update fires. This sets a filter on frmSubSearch. I have three of these combo boxes that can add to the filter and they work great so far.
But I want to have the combo boxes filter themselves based on whats left on frmSubSearch. So if a user selects something in the 2nd combo box, the sub form filters and updates, but then I want the other two combo boxes to only have valid selections, and not something selectable that would wind up giving me a blank sub form result.
I have tried using .Requery in various ways, but its not working. I have also been looking into the idea of Cascading Comboboxes, but these don't seem to quite fit what I'm trying to do.
i want the user to make a selection from one of two option boxes, with the choice that they make i want a list box to appear straight away, depending on there selection, can anyone help please.
I have a database where new products are entered into it. Over time there are revisions that are made to these products and to capture this, a drop down box has been added that allows the user inputting the product to select the current revision number of this product.I also have another form which shows the product serial numbers, and then a bunch of fields relating to revision numbers (a check box for each), this can be used by field technicians who can locate older models and tick off a revision when they have updated a product to this specification.
What I am hoping to do is that when the user initially enters in a product and selects the revision number of it, the other form will automatically update (the check boxes) up to and including this revision... so for example if a product is being entered and is up to revision 5 - the check boxes under this serial number on the 'revision' form will all be checked automatically (Revision 1, Revision 2, Revision 3, Revision 4, and Revision 5 check boxes).
I am creating a query that should filter records of events based on multiple fields. The filters should work with any combination of field criteria, but only two of the four field are working properly, as follows:
1. a text box for searching with event name (free text) - this is working; 2. a combo box to filter events by country name - this is working; 3. a combo box to filter events by event's keyword (category) - this is NOT working; 4. a combo box to filter events by year - this is NOT working
i have a calender of which show's records on date box's i m having a issue when i double click on a datebox it opens a form call update which is filtered on open to show only records for this date but on date boxs on 1st to the 11th i have add the code to do this but when the form open's the form is blank and shows no records but the code works fine for 12th to 31st which i can't understand why? as im using the same code that does dateboxs 12th to 31st on datebox 1st to 11th but doesnt work i'm pulling my hair out on this one !!
I'm trying to build an database for aircraft operators. I've got the basic tables structure and relationships but I'm stuck on building an search form to filter records by user input.I've got following controls on my form (unbound):
1. AircraftType (combo box) from tblAircrafts 2. CompanyName (combo box) from tblListOfAircraftsOperators 3. TeailNumber (text box) from tblAircraftOperators 4. AirportNameSearch (combo box) from tblAirports 5. PassengersNumber (text box) from tblAircraftOperators 6. ManufactureYear (text box) from tblAircraftOperators 7. SourceSearch (combo box) from tblInfoSource 8. CountrySearch (combo box) from tblCountry 9. CategorySearch (combo box) from tblAircraftCategory 10. EamilToOperator (text box) from tblAircraftOperators 11. InteriorPhoto (Bound object frame) from tblAircraftOperators 12. ExteriorPhot (bound object frame) from AircraftOperators
I need to enable users to search for aircrafts based on those criteria. As I mentioned I'm new to Access and I don't have any advanced coding skills. I have a query build to perform the search and this is the code I've managed to write so far:
I'm very good with utilizing the access tools however I can only code in vb editor minimally. What I'm looking to do is have the option buttons control two combo boxes. One to search by user name and the other to search by serial #. I've created an option group with two buttons I set the default values in the combo boxes in accordance with the option buttons However, the option buttons aren't doing what they are supposed to do. I've been told I need to tweak the code for the buttons in order to make them control the combo boxes. Does anybody know what I need to do to make this happen? Ultimately the user will be able to click either of the buttons to perform a search through a single combo box.
Thanks to everyone out there for making this possible. Your expert knowledge will help me grow into an expert myself.
I've created a form using the form wizard and all of the fields show up as combo boxes. I would like to change a couple of them to option groups with radio buttons. Is there anyway to do this without deleting the fields and making the option boxes from scratch? The reason I ask is because when I do the option boxes from scratch with the wizard, it alters the way the information shows up in the datasheet (it's a split form).
there is a way to convert multiple text boxes to combo boxes all at once, rather than right clicking on them one at a time, and selecting Change to.
I have a form with about 50 fields and most of them need to be converted to combo boxes. I'd always done it manually one at a time up to this point, but I'm trying to build up my learning and look for smarter ways to do things.
I have two questions. I have a form with 2 combo boxes. The first pulls from a simple list. Once you make a choice, the second combo box is then filtered by the first. This works correct, except that once a choice is made in the first, the filter is locked. If you change the first combo box's value a second time it does not effect the second box. The query that I am using for the second combo box has two columns that pull the first 50 characters of a pair of memo fields. Quote: SELECT MSSS.SS_ID, Left([Application_Name],50) AS Expr1, Left([Description],50) AS Expr2, MSSS.Site_Code FROM MSSS WHERE (((MSSS.Site_Code)=[Forms]![Edit or Delete Requests]![Combo6])); These fields are then used to populate 2 text fields using an event procedure. Quote: Private Sub Combo10_AfterUpdate() ' Display Partial Application Name and Description based on choice Me!txtApplication_Name = Me!Combo10.Column(1) Me!txtDescription = Me!Combo10.Column(2) End Sub This is working fine, but it starts the next problem.
I need to change the form to include the entire memo field into a text box. I was using the columns of the second combo box to fill them in. Since the combo box is limited to only 50 characters, i could only grab part of it. I am guessing there is a much better way to accomplish what I need, but I am too inexperienced with forms and vb to come up with it. Below are the fields I need from table MSSS Business_Name Application_Name Description Acronym Level_1_Support Level_2_Support Escalation_process Troubleshooting Priority Links Modified Disabled
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 form with 15 unbound text boxes (daily temperatures) and what I am trying to do after entering the temperatures into the text boxes the user clicks an add button which will add 15 new records into the temperature table
