I have a DB that that is growing slowly. To keep it simple I have 2 tables and one form. One of the tables is a “ASSET_lookup” table. The other table (ASSETS) is the real meat and potatoes. I have a form for the assets table that will populate specific fields that are looked up based on a single combo box linked to a query from the assets_lookup table. Everything worked fine until I needed to add some additional items to the asset_lookup table.
The new “records” I added to the lookup table will not show up in the combo box after it updates (a selection is made). I did some experimenting and if I recreate after adding the new records to look up table, the combo box will show the value, but I am curious what I am missing.
I have a small database with 3 tables. tblBilltoCustomer Fields - Key - BillCustID , companyname, address1, address2, city, state
tblOrders
tblCustomers
I have a form that has tblCustomers as the main form then tblOrders as a sub form.
I want to place a combo box on the form that will lookup a company name from the tblBilltoCustomer table then brin in the address1, address2, city,state , into the form for that record. But then I need that same info to print on a rpt.
I can get the lookup to work using =cboCompanyName.Column() but the addresses , city and state will not show on report Is there any good samples of lookup fields
I'm using a lookup table to populated a combo box on my form. I use a provided list of input strings (hundreds) in my combo box and the list is provided in all caps. How can I convert these provided words (strings) to first cap from either the quarry level or form level.
So here's my problem. I'm trying to update a listbox in excel with values that are being pushed in a table in access. The problem is that there are repeated values in the column in access, but I only want to show each value once, regardless of how many times it appears in the table. I would give a code sample but I'm having trouble getting started...
I have a form with a combo box (unbound) that gets its values from a table. I also have another combo box which is supposed to get its values from a query:
SELECT Vehicle.Registration, Vehicle.Make FROM Vehicle WHERE (((Vehicle.Customer)=Forms![New Job]!Customer));
I have made the first combo box refresh/requery the second one onChange, but I get no options in the combo box.
I am pretty sure that the first combo box is Forms![New Job]!Customer. and there are matching values in the Vehicle.Customer field.
The database can be downloaded from: http://www.tapestriesdirect.com/Garage.mdb (996KB) or if you can unzip (who can't?!?) http://www.tapestriesdirect.com/Garage.zip (86.1KB)
Field "City" is connected to a combo box and field "country" to a textbox on the form.
I would like the user to select a city from the combo box. When this is done the textbox linked to the country field should automatically update to show the correct country. This should update without user intervention.
I would find it better to have the text box look up a value list rather than use a table for its source. the form is being used as data entry. The combobox linked to field "city" is also a value list.
I would like all of the text in my database to appear in upper case. I used the > symbol in the format of all controls and in my tables also. However, on my form's lookup combos it doesn't work. When I click the dropdown it shows all selections in upper case, but after I select it turns into lower case again.
I then tried using Ucase and strConv but those don't seem to work with my combos either.
I have searched and these are all the suggestions I could find on how to do this, does anyone know another or possibly what I am doing wrong?
Have just created an access 2000 database which when a user logs on to the network as herself and tries to lookup any combo boxes or click a button to run a query from a form it shows or does nothing, yet when I log on I can.
Our IT Services have looked at folder/share permissions which appear to be OK and I've not set any permissions/workgroups in the database.
Can anyone shed any light as to why this is happening please?
I have two tables A and B. There is a 1 to many relationship between A and B. Table A's data is controlled by FormA, Table B's data is controlled by FormB which is a subform on FormA. I already have a combo box for navigation on FormA, however I would also like to be able to have a combo box on FormB (FormA's subform) for navigation, so that when I choose an record from the subforms (FormB) combo box, FormA then jumps to the 1 record from FormA that contains the related record choosen in the SubFormB's combo box (along with the subforms choosen record on the subform) .
Kindalike the combo box on FormA, except it's a 'reverse lookup' if that makes sense. Is this at all possible, does this even make sense? :confused:
TblCat Contains fields CatID (index) & CatText (Text) TblType Contains field TypeID (index) , CatID (index) , and TypeText (Text) Relationship of tbleCat.CatID (one) to tblType.CatID (many)
Forms!Input!cboCat bound to Tables!Inventory!Category Forms!Input!cboType bound to Tables!Inventory!Type
Intent: Choice from cboCat to provide from list for entry into Inventory.Catagory plus filter list choices for cboType for entry into Inventory.Type.
cboCat : RowSource : SELECT [tblCat].[CatText], [tblCat].[CatID] FROM tblCat; Event: Private Sub cboCat_AfterUpdate() Dim xxx As String
xxx = "SELECT [tblType].[TypeID], [tblType].[CatID], [tblType].[TypeText] " & _ "FROM tblType " & _ "WHERE [CatID] = " & Me.cboCat.Value Me.cboType.RowSource = xxx Me.cboType.Requery End Sub cboType: No special RowSource or events
cboCat works fine but cboType has a popup box that asks for the Parameter value for the choice made in cboCat. ( Me.cboCat.Value ???) Where did I go wrong ??
What I want to be able to do is in a sub form and a combo box named ACCOUNT that looks at the active CLIENT_ID and pulls into to combo box JUST the ACCOUNTS set to that CLIENT_ID example if the CLIENT_ID is 900001 is just brings in ACCOUNTS 4000 & 4001.
I can get the whole list to come in to the combo box but not a filtered list.
I have a main form which has a button to View one of the Reports related to my primary table.The View Report works fine.On close of the Report the program returns to the Main form, and it returns to the record of the Report that was viewed with all of the data for that record showing in all of the Fields, except the Combo Box that I use to look up a record, it is blank.Other than that, the Look Up Combo Box works fine.How do I get the Look Up Combo Box to show the information for that look up field.In the On Close item for the Report I have the following code to get back to the record I want to return to.
Private Sub Report_Close() DoCmd.OpenForm "ENLARGED PROP INFO", , , "[Name]='" & Me![Name] & "'" End Sub
I have an MS Access database. It contains tables linked to tables on SQL Server. Therefore MS Access is really only acting as a front end to the SQL Server database.
I converted the MS Access database to a .mde file.
I noticed over the past few days that the .mde file is growing in size. I don't understand why this is the case. The data is being stored in SQL Server and the database only contains links to the tables so I wouldn't have expected the size of the .mde file to change.
Does anyone know why the .mde file is growing? Is there anyway I can stop this of growing?
I have a problems which seems simple, but it's made me doubt everything I have learned about this; I suddenly find myself unable to grasp what I belive is a very small and easy solution, but I cannot!! (Either that or I have stumbled upon a real conseptual problem without realising it :) )
This is what I had working nicely
Supplier supplyer_ID : ID and PK of a supplyer more boring attributes
Ingredient ingredient_ID: ID and PK of ingredient Name Supplyer_ID: FK to who the supplyer is
Recipe Recipe_ID: PK and ID Recipe_unit_weight: How big each unit should be other stuff
When making a recipe it is obvious that there can be many ingredients and that an ingredient can be used in many recipes, thus it's a many-many. I therefore created a junction table:
Recipe_Ingredients Recipe_Ingredient_ID: PK (I was not completely sure if this one would come in handy, but it has not so far caused any problems (that I can see) Recipe_ID: FK to which recipe it is for ingredient_ID: FK to which ingredient Receipe_ingredient_amount: How much of the ingredient Receipe_Ingredient_order: In which order to put into recipe
I had a nice form where the Recipe was the parent and the subform contained the ingredients. The subform was driven by a query that had the columns from the Recipe_Ingredient table plus the name of the supplier as well as the version of the recipe (as there might be many). Using a combo to get the name of the Ingredient and binding on the Ingredient_ID I didn't need to also bring in the name of the Ingedient in the query.
Now enter problem: For professional people this lacks one thing; Being able to use another Recipe as an ingredient. After a lot of thinking the solution I envisioned was;
Add a column in the Recipe to flag that is a base recipe (true/false). Then adding a new column to the Recipe_Ingredient table, namely Base_ID, which is the Recipe_ID for which recipe to use. As there is already a Recipe_ID referring to which the ingredient belongs, I needed a different name. Additionally I had to change the relationships in the subform query to be left-joins (always include all columns in the Recipe_Ingredient and join in whatever you can find in the other two.
This works for diplay purposes (when I hand edit the join table). The problems I get is when adding a new recipe as ingredient in the subform;
When adding a base (recipe) at the point of saving the record my Recipe table validation kicks in saying you need to have Recipe_unit_weight > 0!!! I am not planning on adding any recipes here. This does however not always happen, as another error also kicks in: Microsoft Jet database engine cannot find a record in the table 'Supplier' with the key matching field(s) ''. As it's a recipe there is no supplier and I'm assuming it is trying to find a supplier.
If I strip away the extra columns from the query powering the subquey (like supplier and base (recipe) version) it works like a charm. In effect it means that the query only consists of the junction table.
It seems that having a two-way junction table with left-join with both "parents" is causing problems. Am I doing something fundamentally wrong here or is it Access that don't get the picture (and how do I tell it).
Okay,This will probably sound confusing, but I'm going to try my best to hone down the issue.First:There is an imported DBF table that I have to perform calculations on, primarily of the string variety, but also with a few type conversions (such as: String "06" = CDate("1/1/" & "06") -> into a Date/Time field)Okay, given that, I was doing many things all at once, but found by doing it in several stages allowed me the ability to: Narrow down the actual amount of items in the DBF that I will import Speed up some of the calculation in the TransferSo I have two tables:tbl_OriginalTape_ID: Text(10) - {Primary}Title: Text(50) - {Primary}Title_Rem: Text(50)Category: Text(3)TitleLen: NumberActionQuery on Tbl_Original: INSERT INTO tbl_Original (tape_id, title, title_rem, category, TitleLen) SELECT qry_dbf.tape_id, qry_dbf.title, qry_dbf.title_rem, qry_dbf.category, Len(RTrim([qry_dbr.title])) as TitleLen) FROM qry_dbf;tbl_CalcTitlesTapeID: Text(10)MovieTitle: Text(100)AltTitle: Text(100)Remarks: Text(50)Paren: Text(20)Bracket: Text(30)Orig_Id: Text(10)OrigTitle: Text(50)Now to take the data populated into tbl_Original, there are many rules that I have to follow. Example: if Original.Title = {text}>>{text} the the left of the double ">" signs, is the MovieTitle, to the Right is the Alt Title. If in the MovieTitle there is a (*) {parenthetical} the parenthetical is removed and the text insde the () is instered into Paren. Similar rules apply for Bracket, just with [] square brackets instead of Parentheses (). Also, if the Trimmed Title Length from the Original insert =40 (the maximum for the DBF field, AND the Original Category field = "SEE" then the title_rem field is appended to the AltTitle field of tbl_CalcTitles, and the the tbl_CalcTitles.Remarks field is set to "" (emtpy string), otherwise the title_rem field is populated into tbl_CalcTitles.Remarks. Also for attempted relationships for later use, I directly transfer the tbl_Original.title and tape_id fields to the Orig_Id and OrigTitle fields of tbl_CalcTitles.Yes, This is very annoying, and i've spent some time writing all the little funcitons to do exactly this, and be accurate every time I run my action query. (It is pointless to post here, since it involves way too much vb back end code to post it all.)qry_dbf: 33099 recordstbl_Original: 33098 recordstbl_CalcTitles: 33098 recordsNow, given this setup, we know that in my insert into tbl_Original, 33098 records of qry_dbf were unique in the paired tape_id and title fields. So now I want to link qry_DBF to the tbl_CalcTitles, so that I get exactly 33098 records from qry_dbf that match up witht he Orig_id and OrigTitle in tbl_CalcTitles. Yet, no matter what i do, I do not get this result. My last attempt was a LEFT JOIN from tbl_Original to qry_DBF and I got 33138 records returns. How did grow extra records. if I ONLY have 33098 in tbl_Original, and 33099 in qry_dbf, where are the 40 extra coming from?So I tried to link tbl_CalcFields to qry_dbf on an INNER join on the same fields, and now, this is purely mind-boggling:Each Time I alter the number of fields DISPLAYED from qry_dbf, the total number of records return alters. Example:tbl_CalcTitles.TapeID, tbl_CalcTitles.MovieTitle, qry_dbf.Title: 33092 recordstbl_CalcTitles.TapeID, tbl_CalcTitles.MovieTitle, qry_dbf.Title, qry_dbf.category, qry_dbf.format: 33072 recordsNow this is completely beyond what one would expect. I already know that there are 33098 unique records in qry_dbf, on the pairing of fields, Tape_ID and Title, of with all 33098 records are contained within tbl_CalcTitles with the EXACT replica of those two fields. so If I am doing an Inner Joing of tbl_CalcTitles and qry_Dbf on those two fields which produced 33098 unique records, why would altering the number of DISPLAYED fields affect that outcome. Why am I not getting all 33098 records from qry_dbf that are contained in tbl_calcFields?Thanks a lot, this has been extermely nerve-wracking.Jaeden "Sifo Dyas" al'Raec RuinerPS - qry_DBF is SQL source defined as:SELECT tap001.*FROM tap001 IN 'tap001.dbf'[ODBC;DSN=Visual FoxPro Database;SourceType=DBF;SourceDB=D:Quarterly]ORDER BY tap001.tape_id, tap001.title;
I have a table with some combo selections with row source No;Yes, bound column 1, column count 1, and default value "No", limit to list set to yes, required = yes, allow zero length = no.
When I create a new record though, all of the combos show 0, and I have to select No or Yes manually. The requirement is that No is the default value..
I have two combo boxes in a subform that use lookup queries. I can get the combo boxes to work correctly out of sub form in a regular form but can not get the combo boxes to work in the sub form. I have narrowed the issue to the logic in the queries in the secondary and tertiary combo box queries. Here is the part of the secondary query. I think I have to add the primary form name to this part to correct my issues. How would I do that?
primary form = Lookup_fm sub form = master_tbl_sub_fm
Hello Everyone, Why does my database grow so large during the development stage? After compacting the database it is 64 megs, but only after a small number of changes it grows to 500 plus and more if I dont re-compact it.
Hi, i am new to access. Anyone know why my .mdb file size Growing. Even when i delete the data from table and done with the compact and repair. i am doing my first small project with one table and 10 querys and 5 repots. each time i edit the query or report the file the size goes on increasing. i have not more then 100 records in my database. i only edit my records when ever i edit my records .mdb file size grows so any one could help me to make it constant. plz help
I'm not a programmer, I am infact a 17 year old College Student from the United Kingdom - Studying ICT!
For my major project I have to design, build and test a camera loans system for my photography department. Except, I am having problems with Combo box lookups. It's been over 6 months since I learnt this, and it's all a blank. Something as simple as this is simply not working...
e.g. in the Studentloans table, I wish to enter the Student ID number from the student ID card inside the combo box, and hit enter so that it brings up the relevant specific loans of that student. I enter the id, hit enter and the student name, class etc does not change. Why? The same goes for my Camera Maintenance Table, Camera + Maintenance tables are combined to a CAMERAMAINTENANCE TABLE - Therefore I wish to enter the Camera ID number so that if i hit enter, it brings up the relevant service histories of that specific camera...
The relationships are all ok, the fields for Student ID are lookup, etc.
I don't understand. It's driving me insane. I need help with this, I'm struggling so much.
I have a sales form that contains a combo box lookup to tblCUSTOMERS. It allows me to select a previous customer if that person is already entered into the system. If the person is NOT in the system, a form pops up to add the customer into the table.
What I need to know is, if the person IS already in the table BUT their address etc. has changed, what is the best way to get to that record to edit it? I'm THINKING a button with a command to go to that customer's profile.
What is the code to open a form to the customer selected in the drop down.
tbl_Retainer tbl_Retainer_Grant_Funding tbl_Retainer has the field, Retainer_ID
And tbl_Retainer_Grant_Funding has the fields, Retainer_ID (a lookup field from tbl_Retainer) Agreement_Num (a lookup field from tbl_Grant)
I have a form based off of a query(not sure if that matters), that uses that tables, tbl_Assignment and tbl_Assignment_Grant_Funding. These tables have the above fields as lookup fields.
So...what happens is, if an Assignment has a Retainer, I want the Agreement_Num box to show only the Agreement_Num's associated with that Retainer, otherwise just show all the Agreement Num's.
In my form, I have Retainer_ID with the row source, SELECT tbl_RETAINER.Retainer_ID FROM tbl_RETAINER;
And Agreement_Num with the row source, SELECT [tbl_GRANT].Grant_ID, [tbl_GRANT].Agreement_Num FROM tbl_GRANT ORDER BY [Agreement_Num];
In my AfterUpdate event for Retainer_ID I have,
Private Sub Retainer_ID_AfterUpdate() Dim strSql As String strSql = "SELECT [Retainer_ID]," & _ "[Agreement_Num]," & _ "FROM tbl_RETAINER_GRANT_FUNDING" & _ "WHERE [Retainer_ID] = " & Me.Retainer_ID.Value
Me.Agreement_Num.RowSource = strSql Me.Agreement_Num.Requery End Sub
When I am in my form and choose a Retainer ID, the Agreement_Num box goes blank, and there are no choices to choose from. I am wondering if this is because the Agreement_Num's are sourced from tbl_Grant and not from tbl_Retainer_Grant_Funding.
