HI THERE. IM HAVING A FEW PROBLEMS TRYING SORT THIS PROBLEM OUT. I HAVE A DATABASE IM TRYING TO APPLY A FILTER ON THROUGH AN UNBOUND COMBO BOX. WHEN SELECTING THE DROP DOWN FILTER TO SORT THE RECORDS NOTHING HAPPENS. THE CODE IM USING IS
Private Sub Combo??_AfterUpdate ()
'Find the record that matches the the control
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[WARD_AREA] = '" & Me! [Combo??] & " ' "
If Not rs.EOF Then.Bookmark = rs.Bookmark
End Sub
Does anyone know why this filter is not sorting through the records. Any comments much appreciated!!
I'm Looking for a bit of advise. I'm about to try and build a new database that will have about 40 tables. One of these will be the main table. This table will have a primary key TAGNUM and several other fields containing data associated with each tagnum. Most of the other tables will then contain more detailed info about each tagnum. So the obvious aim is to have all these secondary tables connected somehow to the main table so that when a new tagnum is added/deleted, the corresponding row in any effected secondary tables is added/deleted also.
My attempts so far have involved setting up a relationship beween tagnum in the primary table and tagnum in each secondary table. This appears to work ok but I'm trying to be sure that my setup is good. I have a one to one relationship in each case, I have selected referential integrity, and the cascade changes etc. My join type is "All records from main table and only those... secondary table". Is this correct?
The further complication is that I need to have Lookup?? tables (not sure if it's the correct word) tied to each of the secondary tables and 3 tied to the main table. The user needs to be able to select items from the fields bound to these lookup tables via a dropdown box on the field in a query. When the dropdown box is selected a descriptor for each option available need be seen The non bound fields in the lookup tables only needs to be available to a report bar the showing of the Description field during selection. Any ideas?
Sorry about the size of this and annoying you with what is i'm sure not difficult stuff! Pretty new to this.:)
Hi guys Quick question. im running a planning database which has got 3 tables Objectors, Representations and Agents. Ive put these together using a one-to-many join matching up the index fields from Objectors and Representations. Ive just constructed a form which has a main form being the Representations and the sub forms with Objectors and Agents. When I want to update or add records a message saying " you cannot add or change a record because a related records is required in table Objectors.
Can any one tell me how I can get around this or possible solutions to this is? Any comments or feedback greatly appreciated!
I'm trying to hash two scripts I've found into 1 functioning filter, however I'm still relatively new to vba and can't figure out how to get this working.
I'm trying to use Allen Browne's Search Criteria:
with another snippete of code I found here:
Code: 'Purpose: This module illustrates how to create a search form, _ where the user can enter as many or few criteria as they wish, _ and results are shown one per line.
[Code]....
It's the date part I'm having trouble with, the rest of the search criteria work fine without the date, but I can't get it working when I try to modify and merge the date sections of each code.
Also I'm using a listbox for the "Yesterday";"Last 4 days";"Last 9 days" and not a combo box.
I have a report [Report1] that gets its data from a query [Query1]. On [Report1] there is an unbound text box [EnterEndDate] that I want to use as a filter for the report criteria, and have it filter the report to show every report row with the value in the [EndDate] field greater than what the user entered in [EnterEndDate].
There is no need to save the value used in [EnterEndDate]. It will be entered after the report is run and changed on demand while the report is open for the user to see different date ranges.
The layout: I have form1 listed in continuous view. I have about 10 fields being listed. I have unbound text boxes for each field in the form header designated as a filter for each field.
Ideal world: Have each filter update records as you type. But I would also like for a "cascade" effect on the filters as well. Being that I can type in a few letters in FilterField1, and type in a few letters in FilterField2 and it would only display the records where the criteria is met for both filters.
What I'm not looking for: Only applying 1 filter at a time for 1 field. I have this setup now, but would like it to be more versatile allowing several fields to be filtered at once.
I need to add an additional filter to my unbound form and can't seem to get the syntax right -
I have a text field in my form "[B Style]" that has either a "Y" or "N"
I want to add a checkbox on my main form where, if checkbox=True then [B Style]="Y"
Private Sub ckBStyle_Click() Dim stFilter As String Dim stDocName As String stDocName = "Modify_OpenItems" If Me.ckBStyle.Value = True Then DoCmd.OpenForm stDocName, , , ("[B Style]" = "Y") Else: DoCmd.OpenForm stDocName End If End Sub
I am currently creating my first Access database and in the planning stage but have come across one problem area. I need to create a database that holds and reports on information recorded on a clinical form. The section of the form I am stuck on contains a table as below.
123456 Catheter Type Wire Type Balloon Type Type of Stent Size of Stent Target Vessel Successful?Y / NY / NY / NY / NY / NY / N
The user works their way down this form starting in column one but free typing what make of Catheter they will use and then type of Wire and so on. The problem arises when a particular item doesn't work, e.g. a type of Balloon. They then try a different type of balloon and would enter this information in Column 2 and then if this works go back to column 1 to fill in the next field 'Type of Stent'. As below..
1 2 3456 Catheter TypeCathsRUS Wire TypeWire 1000 Balloon TypeBAL001 BAL002 Type of StentST555 Size of Stent10 Target VesselLeft side Successful?Y / N Y / N Y / NY / NY / NY / N
Not only does the user need to record all this information without duplicating anything else from column 1 into column 2 but there are also instances that within the same procedure number, that a second bleed occurs and they need to fill in the next available column, which in the example above wiuld be column 3. They would again work their way down, but this time they may not need to use a wire as they can reuse the last one.
1 2 3 4 56 Catheter TypeCathsRUS OpCa34 Wire TypeWire 1000 Balloon TypeBAL001 BAL002 BA3 Type of StentST555 ST333 Size of Stent10 5 Target VesselLeft side Right side Successful?Y / NY / N Y / N Y / N Y / N Y / N
The access database needs to be able to hold all this information and show that 2 separate episodes in affect have occurred. The user wants to be able to report on various things such as, what type of Stent/Wire/Balloon/Stent size is the most/least used / successful/unsuccessful? Etc
If anyone can help shed some light on the best way to structure this information in Access I would be very grateful!
I’m adding a new function to our Costumer Relations Management system – Time management. Or maybe “time registration” is a better definition for it. The idea is that our consultants can add their hours into our CRM, either from the “Project”, “Tasks” or “Costumer” part of the CRM. Note: This is a tool to keep track of hours spent on a project, not a planning tool.
I’ve made the input-form for the records (Date, ClockStart, ClockStop, TimeSpent, InvoiceHours, etc.). But now I’m basically without a clue on where to start – I want to make a function almost like the calendar in Outlook, where the consultants can input what they’ve been working on, say between 13:00 and 14:00.
It doesn’t have to be fancy graphically, but functional and give a good overview.
I'm new to Access so sorry if this is stupid - I have looked everywhere!
I have an unbound combo box on a form that is populated in the onload event of the form by setting the rowsource to a query based on the companyID on the form. The query gets me a list of contacts at that company.
So, after the form is loaded the combo box (if dropped down) might show:
1 Fred Smith 7 Jim Jones 9 Arthur Askey
The first column contains the ContactID.
My question is .... when the form loads I know the ContactID of the person I am looking at. If that person is 'Jim Jones' and I have a field on the form which contains Jim Jones ContactID of 7 - how can I get the combo box to be showing the 'Jim Jones' row?
(I don't what to bind the combo box to anything as I want to use it just to filter which contact I am looking at - not change the ContactID)
I guess I want to loop through the combo box saying 'If the contactID of this row = the ContactID on the form - select this row' - but I can't find the syntax anywhere.
i really need some help with this... heres the background. I have an unbound search combo box in the header of my form. when users select the PCNo from this combo box, i need the associated container no to appear in a control on a bound form.. below are my tables:
tblShipping ShipID (PK) PCNo
tblContainer ContainerID(PK) ShipID (FK) ContainerNo - (data to be displayed on form)
how can i pull up and display the container no (stored in tblContainer) based on the PC No that a user selects from this combo box?
i have got the combobox working fine - the users can select a PC No that is already stored. what i need now is for the Container No to be displayed on this form, based on the PC No.
I have seen a few articles here and there on using a combo box to filter records in a sub-form and to filter records in another combo box, but I am not getting anywhere. I hope someone can belp
Exercise 1
For this exercise, I have the following tables:
tblClients containing client names tblProjects containing some project details
I want to set up a simple form, so that the user can select a client name from a combo box on the main form which filters the project detail records in the Projects sub-form. Once the user enters project details, I want this info as well as the selected ClientID to be fed back to tblProjects.
Exercise 2
I have the following tables:
tblProjects as above tblWorkstream containiig names of workstreams and some other details.
Every project has one or more workstreams
I have a form where users will enter hours worked on each workstream. They will first select Project from a combo box on the main form. This should then filter records to be displayed in the Workstream Combo box, before they can then enter hours.
Please let me know if you need me to explain any part of this better.
Thanks in advance.
ps. I am a novice, so I'm hoping to do the above using default MS stuff, not with complex programming!!!
I am designing a customer information form, the form has an unbound combo box showing customer names and when clicked opens the customer record.
My problem is when I enter a new record, I have a button that saves current and opens new, but when I am in the new record, the customer I just added previously is not showing up on the unbound control, only when I close the form completely and re-open it.
Is there a code to update the unbound combo box when you click to go to new record.
I'm having a little difficulty setting up a combo box to filter another combo box. I've actually got one working but the second one is giving me all sorts of errors. On the attached database, there's a form called frmAddNewRecord. At the top of the form there's a combo box which allows you to select a name and another combo box beside it which acts as a filter so only names from a certain section are shown (working fine).
The subform which is attached to this form and contains the training details, has another combo box which allows you to select a job...I've tried to add a combo box beside this to filter it but I can't get it to work.
Ive got a combo box linked to fields in a form so when I value (tenantID) i selected in the combo box the record appears in the form.
The problem is, I have changed the form name Unbound (it was blank) to TenantID as I want it to appear when the form is opened, however now I cannot select other items from the combo box.
Ive got a database with a combo box, called "combo1" (with 2 columns). It is unbound but uses a query as its row source. When i select a value in combo1 is places the data from combo1.column(1) into a textbox (text1). I then click the next record button (button1) and it then keeps the same value in combo1 because it is unbound. So i need it that when i go to the next record it displays the value in combo1 that relates to text1.
I have an unbound combo box on a report that looks up to a table of address data.I have a specific requirement where if one of the bound fields on the report is a specific value, I want the combo box to show data. If the bound control on the report is a different value, I want the data in the combo box to be different.
I have tried using the OnOpen method, but it says I cannot assign a value to it.I have tried using the OnLoad procedure, but it doesn't show anything. the code is as follows:
Code: Private Sub Report_Load(Cancel As Integer) If Me.[Dispatch Type] = "Sent to A" Then Me.cboDispatchTo = 15 ElseIf Me.[Dispatch Type] = "Sent to B" Then Me.cboDispatchTo = 8 End If End Sub
First, just want to say I wish I had found this site a long time ago. Lots of good stuff here :) I don't know a lot about Access, but know it can be a great tool for what I need. So I hope I can find the help I am looking for...
Ok, on my form I have three unbound combobox's that I use to filter the information I want. The first two do not need to be used, meaning that the third combobox will show all the records if the first two are left blank. With that being said... I would like the form to show no data until the selection is made in this third combobox. SO, my first question is how do I make the form show no records till this combobox has a selection. And second (at the same time), make that third unbound combobox with the selection load that record to the form fields so they can be viewed or edited??
I'm sure it can be done, but I am lost... Hope I explained it ok :) Thanks in advance!!!!!!
I have a main form frmProperty from where you can go via a button to frmTraining and these two are linked by "Property Code".
On frmTraining i have an unbound combobox which gets it data from the tblTraining table. I want this to only display data for the "Property Code" that the frmProperty is pointing to.
I assumed this would just work if the link criteria was present in the button code, however it appears that no matter what the "Property Code" the combo box displays all the data in the tblTraining table.
I have a form with a record source property set to a query which pulls the data by joining two tables. The form also has four navigation buttons (first, previous, next, last) and a ADD record, SAVE record, EDIT record, DELETE record buttons.
I also have a unbound combo box (control source iset to 'empty') in the form's header section and its record source property set to an SQL (SELECT DISTINCTROW tblProjts1.chrProjectName, tblProjts1.intProjectID FROM tblProjts1 ORDER BY tblProjts1.chrProjectName;). It's columns count is 2, bound column is set to 2 and column width is set to 3.1146";0".
In the edit button, I have the code "Me.AllowEdits = True" so that the user can edit the data.
Also, I have the following code on form's "on current" and "after update" events so that it will flip back to allow edit to 'NO' Me.AllowEdits = False
Also, I have the following code on the combo box's 'after update' event. Dim strSearch As String
'Find the record that matches the control Me.Requery Me.RecordsetClone.FindFirst strSearch Me.Bookmark = Me.RecordsetClone.Bookmark.
My Problem:
When I run the form and select a record from a combo box it populates the fields (all memo data type) in the form with correct linked record, ONLY when my allow edit property is set to 'YES'.
With allow edit set to 'NO', then when a tried to make a selection from a combo box it doesn't allow me to select any record. The combo box seems to be locked up and I can't select any record.
Does anyone come across such a problem with cbo box and 'allow edits' property in the forms.
I'm pretty hopless with VB but I am trying very hard (hours spent on this forum reading code I only slightly get the gist of) to implement a form that takes input from two synchronised unbound combo boxes and then displays records matching the input in a sub-form in datasheet mode. I'm failing at the first part just now: the combo boxes. No doubt I'll fail at the second part too but I'll deal with that when I get this fixed. :)
I was using the northwind DB to learn how to synchronise the combo boxes. I successfully did it by following the instructions and using this code from MS kb article 209595 in the After Update procedure:
Me.Products.RowSource = "SELECT ProductName FROM" & _ " Products WHERE CategoryID = " & Me.Categories & _ " ORDER BY ProductName" Me.Products = Me.Products.ItemData(0)
I then tried to implement this on my database by changing the relevant information to reflect my database info. as shown below, and it does not work. I switched Product references for my Unit references and Category references for my Item references but I don't think I got it right or over-simplified it due to the table structure.
Private Sub Item_AfterUpdate() Me.Units.RowSource = "SELECT Unit FROM" & _ " Units WHERE ItemID = " & Me.Items & _ " ORDER BY Unit" Me.Units = Me.Units.ItemData(0) End Sub
When I compile the code it stops at Me.Items with the error "Method or data member not found". So I assume that I have not referenced the right objects when I modified the code. I thought this was maybe due to the two tables I'm using being in a many-to-many relationship via a junction table, as opposed to the northwind tables which are directly linked by a one-to-many relationship. If this is indeed the case, can anyone help me put this right. I have attached my table structure / relationships in a .jpg to this post.
Any help from all you smart developer dudes would be muchly appreciated.
Selecting a value from an unbound combo box in Access 2000
Hi,
I am attempting to default a combo box to a value I have passed in. I have already populated the combo box dynamically by setting the column numbers and widths and specifying the row source as Value List, supplying the row source string.
Since I am working with classes and collections, I wish to avoid data binding as I would prefer that the classes handle everything, which they can do.
Now my problem is that I cannot find how to select the correct value on the dropdown after populating it with all the values. When I try a line like:
cboAppType.Column(0) = lngValue (where lngvalue corresponds with a value in the list)
it returns the error "object required". It won't recognise the column at all, even though it sees and displays the rows and columns.
I am beginning to tear my hair out as I have been struggling against Access all the way to try and implement unbound combo boxes and listboxes and having overcome everything else, I'm stuck just here.
I have a form that I would like to have blank fields when it is opened. I put the following in the "On Open" Event field of the form properties:
Private Sub Form_Open(Cancel As Integer) DoCmd.GoToRecord , , acNewRec End Sub
However, it does not work. I have an unbound combo box on the form for users to select from, in which all the records on the form will populate with whatever is selected in the unbound combo box. I thought this was the problem at first, but even if I remove that from the form, the form still opens to the first record. If I set the "Data Entry" field to "Yes" on the Data tab, that works, but then none of the fields populate when using the combo box.
