Unbound Combo Boxes And A Form With Allow Edit Set To 'NO'
Oct 29, 2005
Hi folks,
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 have a form that utilizes a combo box called cboProgAddr (for Program Address). The cbox has 5 columns each of which fills in an adjacent text box. Since I needed the option of making corrections to these text boxes once they were filled in I need to set them up as unbound via the following code in the After Update event of cboProgAddr:
Private Sub cboProgAddr_AfterUpdate() Me!txtProgStreet.Value = Me!cboProgAddr.Column(1) Me!txtLocStreet.Value = Me!cboProgAddr.Column(1) Me!txtProgCitySt.Value = Me!cboProgAddr.Column(2) Me!txtLocCitySt.Value = Me!cboProgAddr.Column(2) Me!txtProgZip.Value = Me!cboProgAddr.Column(3) Me!txtLocZip.Value = Me!cboProgAddr.Column(3) End Sub
As you can see it puts each field of data into two separate text boxes. Here's the issue: If I have to go back to the form after the original data entry the following boxes are blank...
txtProgStreet txtProgCitySt txtProgZip
while the following retain the orginal data...
txtLocStreet txtLocCitySt txtLocProgZip
The original cboProgAddr maintains its original data. All six fields above are stored in the same table as text field. I'd like all the boxes to maintain the data from the original input.
I'm totally baffled why this is so. I've attached images of the combo box control functions if that helps.
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.
I'm creating a form that has combo boxes pertaining to each of the following fields from table "Documents": DocumentID (primary key), DocumentTitle, DocumentAuthor, and DocumentYear. No combo box is used with any priority over the others. The working form will allow a user to retrieve the full document record (data for all fields) by using any combo box they want, as well as any combination of combo boxes. This means that if a selection is made in one field's combo box, the drop-down lists in the other boxes need to update based on that preliminary selection. The filtered results for each field, based on any and all combo box selections, are always shown in a single datasheet on the form.
The kicker is that when a user starts filtering records by making selections from the drop down list in a combo box, but then decides to TYPE in another field's combo box, I want the combo box they typed in to do two things: 1) filter the records for that field based on what they typed, if any records meet that criteria (e.g. they typed "B" so only records beginning with B are shown), and 2) if no records match the typed criteria, the search starts all over (at the top of the cascade), filtering all library records based only on what they typed in the most recent combo box.
Does that make sense? Any tips welcomed.
I don't know how to cascade combo boxes in this way.
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.
I have a form setup that has 5 combo boxes where a user will select one item from each combo box. Each combo box is from a differant table. Once this is done I want to be able to append or update the results into a new table I created. My problem is I don't know how to submit the data from the combo boxes to the new table. Can anyone help me with this?
I have set up a query to give me details of employees names, the business they work for and where they work.
If I run it without criteria I get all of my records, as you would expect.
However, I want to be able to select parameters from combo boxes and then run a specific report of employees by location. I did have this working by accident a while ago but have changed quite a lot since then.
I have set up an unbound form with three combo boxes - Business, Business Unit and Location. Then a button for preview and print report linking to my report template.
I have refernced the controls in the relevant columns of my query as: [Forms]![FormName]![Control].
When I run the query I get nothing back and I don't understand why.
As a part of reducing traffic on file server, I am planning to read less record on the form and hence I tried removing forms record source. (Attached sample db)
I kept all unbound textboxes onto the forms and save new record to the table using codes back of the command button. This is working great with main form. But doesn’t with sub form. I tried removing record source of sub form and keeping unbound text boxes to appear record if it exists.
If you enter 1 in InvNum text box on main form, you will find it brings record. I don’t want to show the record on the sub form unless I call it from main form.
How can I use the same trick of unbound text boxes with sub form too?
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:
Attached I have a database that I've been working on which has a form called "frmCriteriaSearch". It is based off of the qryCriteriaListBoxUpdate query. I am trying to get the listbox in the second tab of the results section to work. It queries fine for the checkboxes, but I cannot get the comboboxes to affect the query (unless a checkbox has already been selected)
I am trying to make a search option in my form header. Right now I have two unbound combo boxes (CboAccountsfilter and cboCourseName) that I can use to filter my records. Currently, I can use the drop down for CboAccountsfilter and a list of accounts will appear. When I select one, the corresponding Course Names will appear in cboCourseName. This works fine...Code below. I would like to take the filtering a step farther and add checkboxes to filter the data. I my form, there currently exist several check boxes (yes/no)...(Priority, Rep Top Target, Manager Top Target, ect). I would like to have the option to use a check box to filter. I.E if I had a checkbox in my header called PriorityFilter, if checked it would only bring up those records that met the two combo boxes criteria and was a priority.
Below is the code I have so far...it doesnt have anything for the checkbox because I am at a lost of how to get started.
Private Sub CboAccountsfilter_Change() Me.Requery Me.cboCourseName.Requery Me.Check178.Requery End Sub
I have a form with 1 unbound listbox as drop down list (entypolst), an unbound text box(entypotxt) and a command button. The list box reads items from a table. I want to change a value (text) on listbox, input a text on textbox so pressing the button add a new record in a table (Table1 fields Category,Product) showing in my form as subform (SFTable1) in datasheet view. For that reason a made the following code:
Code: Private Sub AddBtn_Click() Dim ans As Integer Dim strSQL As String Dim db As DAO.Database Dim rs As DAO.Recordset Dim ctl As Control
[code]....
I take the error msg for 0 items selected in listbox and exit the sub.
Hi i have a shared database and have a form with a string of records. Each record has its own unbound checkbox and I want to make it so you can select multiple records, then be able to hit a button on the top of the form to edit certain fields of the records selected. Please keep in mind that this is shared by 10 users, therefore a linked yes/no field would cause problems between the users.
While the form itself is bound to tbl_Orders I decided to use unbound text boxes to enter the data (knowing I was in for some work!). Reason is that if textboxes were bound would need to use a subform with multiple combo boxes and that UI did not seem suitable for the application.
I’ve completed the code to add new records to tbl_Orders and tbl_Junction from the unbound textboxes. I'm using the VBA .AddNew method and is working well. Now I need to integrate code that will allow users to edit existing records while they are viewing them on the form.
I have the code to look up the record set I want to edit in tbl_Junction and am familiar with the .Edit method but the issue is that the required changes MAY include not just editing existing records but also deleting and adding new records. For example, the initial order may have been for Apples and Oranges and the revised order may change the number of Apples, eliminate Oranges entirely, and add some Bananas. So, it seems I need to .Edit Apples, .Delete Oranges, and .AddNew Bananas?
Should I try to write code to determine where I need to .Edit/.AddNew/.Delete? -- not looking forward to that. Thought maybe could delete the entire junction record set and then just .AddNew for everything per the revised order. That should work as the display on the form would be correct for the whole order. But maybe would cause a problem as would remove the FK in tbl_Junction that corresponds to the PK in tbl_Orders?
I have a Main form with a combo box that pulls names from a table. Alongside the combo box is a command button that opens a form which allows the user to edit the names in the table.
The problem: When a user edits a name and closes the edit form, the edit is not immediately displayed in the combo box. However, when the Main form is closed and reopened, the edit is properly displayed. Can I somehow refresh the combo box in the On Close event of the edit form?
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.
I have added a combobox to one of the forms in my customer database using the "find a record in my form based on a value in my combobox" section of the combobox wizard.I have a number of columns in the combobox (the primary key is the bound value, and I have unbound columns for first name, surname etc).
I sorted the records in the combobox by column 3 (surname) so I can scroll through the list and choose surname instead of having to memorise customer numbers. Now I have more than 1500 records (with more on the way), scrolling through takes ages, and I was wondering if there is a way to adapt the combobox so I can (for example) type the first letter of a surname into the box and be taken to surnames beginning with that letter, whilst retaining the primary key as my bound column?
My initial thought was to base the combobox values on a query, and make the query prompt for a starting letter (or string of letters) each time it runs, so it only displays the query results in the list, but I was wondering if there is a neater way to do it?
I am running in to a brick wall with this. I have an unbound text box with the control source set to =IIF([text42]=0,0,[text42]/[text44])*100 and in continues to return a #name? error.
I am not sure how to get this expression to work. I have even tried to put =[text42]/[text44] and I still get the #name? error.
I have a database I am using to record financial transactions. I have a transaction edit/entry form that uses combo box lists to select the different segments of my 32 digit account numbers. The issue I am running into is that when I enter a value value in the first box/field the form jumps to the very first record.I then can use the navigation buttons to get back to the last record, and all the subsequent boxes/fields work fine without jumping to a different record.
I have a serious issue to solve and I would like to ask for your help, as I just don't seem to know how and where to start from.
I need to make a database that will have 2 tables, say tbl_item and tbl_offer. The items table will have different cardboard dimensions and other characteristics (width, height, color, photo, price etc) while the tbl_offer will have different combinations of clients' cardboard requests.
The workflow is:
A client comes and asks for:
- two pieces of cardboard of 120x100 cm - five pieces of cardboard of 135x90 cm - one piece of cardboard of 110x125 cm
I want to be able to enter the client's request in a form and the form will do 2 things:
1. Store the clients request as a record in the table tbl_offer (for current or future referrence) 2. Create a report (out of the above record) that will be printed out and given to the client (but I suppose that's easy...)
Now tbl_item has different types of cardboard (their code names) along with different dimensions and price for each, for example:
1. I don't know exactly how I can create a form (say frm_offer) that will give me the possibility to add many different cardboard types and save them as one offer to the tbl_offer. What I would like is to choose from a combobox (or any other similar function) the type (say 002) and by this selection a second combobox would be filled only with the available Width dimensions for this type (165, 140, 170). By choosing what I want from the width combobox, a third combobox would be available that would give me the available height dimensions for this width (if for example I chose Type 002 and Width 140, the last combobox would give me two choices, 105 and 130). After choosing the Height I want, I would fill in a text box of how many pieces the client wants and the form would also fill automatically the cost of all the pieces for that dimension (by taking the price/item from the tbl_item perhaps?). If 1 piece then it should say i.e. 18 for the 140x130 piece, if 10 pieces it should say 180. Finally, I want to add the photo of the cardboard automatically (pls note that same type of cardboards share the same photo) not at the form but at the report to be printed.
I have already looked in the Access FAQs section of the forum for the combobox techniques but I can't understand how I can apply them to three (or more) comboboxes plus the photo.
I can use Access only when little/no programming is required, and I am very much afraid that all the above will require a great deal of it, so I apologise in advance for my non-knowledge of programming :(
2. How do you think would be suitable to split all this data to tables? Is my thinking correct (2 tables, tbl_item and tbl_offer) or would you suggest something better? Will the tbl_offer get a lot of data that way? Mind you also that every once a year there is the need of updating the cardboards' prices (all will rise/fall at the same percentage) and, furthermore, we are talking for about approx. 100 different types of cardboards, each with about 15 different dimension combinations.
I am not sure if I had to post this message to the Tables section of the forum or here but I think here is better as the main problem is the form part.
Thank you all in advance and my apologies once again for the low level of my programming skills :(
I need a form that has multiple categories, 7, listed one after another vertically. I also need the categories divided in to 2 columns. I need to have 2 combo boxes, 1 each controlling the info for each column. Can I do this on 1 form. Suggestions would be appreciated.
I have a continous form that once the combo box filters are selected, shows the applicable records. Included in records, I've put a bound combo box that allows the user to fill in that particular field. This works with the first record but when you attempt to select the other records, all the combo boxes disappear. What am I doing wrong here??
I have two combo boxes on the form and print button. In one combo box showing numbers 1,2,3,4 and 5. Other combo box showing logical operator =,<,> User is going to select from one combo box logical operator and from other number "means class field". Print button prints the report. Report is based on the query. In the query in criteria line I put the code [FORMS]![MAINFORM]![CMB_CLASS] It works for one combo box. Problem is to get criteria from second combo box or concatenate the both criteria’s. In query criteria line it should show like <2 or =3 or what ever user is selecting.
Second problem is in the same line. Three text boxes and 2 combo boxes showing "OR" and "AND" For example in one text box user enters last_name and then he select "OR", second text box enter city and then he select "AND" from second combo box and third text box enter department. After selection he is going to click the print button to print the report.
I really appreciate if anyone knows the solution or they used this type of thing in there application.
Hi all! :D I'm trying to make a form to autofill with customer data (from a database), with 4 columns (= 4 customers). Each column contains on top a combo box and below 5 text boxes with address, phone etc. So i created a form and placed a combo-box (from wizard) with the option "Find a record on my form based on the value i selected in my combo box". The problem is that this one combo box controls all the text boxes. Even if i create an other one, it still controls all of them.
So i'd like to ask: How can i link a combo box, to autofill specific textboxes?:confused:
I have re-written this question as I think I managed to cause confusion!
(I really do need help!) My problem involves three tables. The first table is NAMES with an auto-number key field called NamesID.
The second table is a list of Dance Classes, called CLASSES again with an auto-number key field called ClassesID.
The third table is a list of MEMBERS in each dance class.
Obviously, the MEMBERS can belong to many CLASSES, and each of the CLASSES can consist of many MEMBERS.
My aim is to add a new record in MEMBERS by using a ComboBox containing records from the NAMES table.
On the form, I have a combo-box which shows me the list of CLASSES
(ComboClasses). I click on the class.
I then click on the NAMES combo-box, click on a name and want it to appear in the table of MEMBERS for the class shown in the CLASSES combo-box.
The problem
I am selecting the name using “ Select * From NAMES where Names.NamesID=[Forms]![FormName]![ComboNames] (supplied by supersubra) but how do I get the ClassesID into MEMBERS record.
At the moment, if I add a name that member appears in every class, or I have to manually insert the ClassID.
