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...
Any help is appreciated.
--patrick-->
I have a form with a Listbox which lists Customers and a variety of customer information
I also have a table on the form that lists the donations that customers have made.
How can I populate the Table based on the customer that is selected in the Listbox?
There is an ID field that links Customers with Donations
I'm guessing I need some code in the ListBox_CLick() event that triggers the table to query or to Load with a query based on the ListBox.Column(0) which contains the ID?
I'm trying to populate a listbox from a recordset. I will explain what I'm trying to achieve.
I have a (continuous) form with some data from a table. On the form header I have a listbox, showing all (distinct) customers from that form.
The user is able to do some filtering. That all works great. But I want to populate the listbox with ONLY the customers on the form AFTER filtering.
One way I thought might me the solution was using RecordClone. But I'm stuck there. It must be something simple for an expert. I'm not entirely a newbie, at least not to VBA. Is there an easy way to refer the listbox' recordset to the actual recordset on the form?
Hi
I have a DAO recordset(rcdSearch) which contains a number of fields(eg
UNIQUE_NUMBER, AREA and SITE_NAME). Assuming the recordset is populated(in this case I know it is), how can I populate a listbox with the data? I have tried the following with no joy
me.listBox.RowSource = "rcdSearch" 'puts string "rcdSearch" in column header
me.listBox.RowSource = rcdSearch ' returns type mismatch
me.listBox.RowSource = rcdSearch!UNIQUE_NUMBER ''puts unique number in column header row
me.listBox.Column(0) = rcdSearch!UNIQUE_NUMBER ' object required error msg
I have even tried using the SQL string that generates the Recordset, this does not return any results. :confused:
I would be grateful for any help on this issue, as it has been doing my head in for days!
Graham
Hi
I've created a few listboxes before, which have all worked fine, but this one however keeps throwing up an error message.
Run-time error '3464': Data type mismatch in criteria expression
Can someone help with this, here is my code, and the highlighted section it debugs.
Dim db As Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim EmpID As Integer
Me.List35.RowSource = ""
EmpID = [Forms]![frmWork_Placement]![Emp_ID]
Set db = CurrentDb()
sSQL = " SELECT Object_ID, Emp_ID, Name1, Name2, Name3, Name4, Name5" & _
" FROM dbo_tblEmpContacts WHERE Emp_ID = '" & EmpID & "'"
Set rst = db.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)
Me.List35.AddItem rst![Name1]
Me.List35.AddItem rst![Name2]
Me.List35.AddItem rst![Name3]
Me.List35.AddItem rst![Name4]
Me.List35.AddItem rst![Name5]
rst.Close
Set rst = Nothing
Thanks
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.
http://forums.aspfree.com/microsoft-access-help-18/menu-list-values-won-t-pass-to-db-20951.html
Here's a page that shows almost what I am trying to do, but my question is how do I make selecting an item in my listbox pupulate date in multiple field on my form?
For instance, I want to click on a line in a textbox and have the fields "City" "State" and "Zip" all changed on my form, not just "City"
in my report I have table made from 25 fields (5x5) and they all have the Can Grow Option turned on.
However the lines and boxes separating the fields do not grow with my fields and I get plenty of intersections and over lapping.
Is there a way to make boxes and lines grow with the text box?
I use Access 2003. I am trying to populate a list box with the names of files found in specific folder in my computer. The problem is that if the file name has a comma (,) in it, so the name is being separated in two parts - first what is before a comma and second what is after. I need them exactly as they appear in directory in order to open the files from this list box.
View 2 Replies View RelatedWhat I am trying to do is populate the specific fields on excel sheet based on access query. so say I have 3 separate fields Name, Job, Salary on excel sheet (already designed excel sheet). and my access query has all 3 fields pulled up for all employees. Now if a person selects his "Name" on access form person should be able to see prepopulated excel sheet with his name, job and salary. I know I have to go recordset path.
View 1 Replies View RelatedI'm writing to an excel file from two separate Access tables, for which I'm using two separate DAO.Recordsets in VBA.The first table has 190 columns, the second table has more than 66 columns, so more than 256 columns will be written to the excel file.So I'm not expecting any error due to any maximum of 256 columns, but still I'm getting an error while trying to populate the 257th excel-column:
Run-time error '1004': Application-defined or object-defined error.(The same VBA works fine when I use another table with less columns.)Even more strange: on another computer I did not get the error and I was able to populate more than 256 excel-columns.
Code:
Option Compare Database
Option Explicit
Dim xlApp As Excel.Application
Dim xlWkb As Excel.Workbook
Dim xlWks As Excel.Worksheet
Dim FileNameOut As String
Dim RowNr As Long
Dim ColNr As Long
[code]....
Hello,
Propably I am asking too much but know that Access and VBA can almost do anything.
Is it possible to add a list box on a form to list the spreasheet in a excel file and by clicking on the select record make a print only of that specific spreadsheet?
Thanks.
I have a Listbox named List5 and a search textbox named txtProperty and a table name sms , after i search in textbox the results in listbox . i would link to inport the results in listbox to excel but the code i have export the whole table to excel .
here is my code
''''''''*''''''''*''''''''*''''''''*''''''''*''''' '''*''''''''*''''''''*'
''''''''*''''''''*'''''''' BUTTON 3 ''''''''*''''''''*''''''''*'''''''
''' EXPORT THE LIST TO EXCEL AS List5.XLS ''''''''*''''''''*''
''''''''*''''''''*''''''''*''''''''*''''''''*''''' '''*''''''''*''''''''*'
''''''''*''''''''*''''''''*''''''''*''''''''*''''' '''*''''''''*''''''''*'
Dim outputFileName As String
Dim oXL As Object
Dim oExcel As Object
Dim sFullPath As String
Dim sPath As String
outputFileName = CurrentProject.Path & "List5.xls"
[Code] ....
I am looking to export my listbox values to a single workbook BUT a new sheet is created per export. There are 6 listboxes in total and I already have the code to export a single listbox but if I try to use this code its going to overwrite the csv each time .
Code:
Dim i As Integer
Dim n As Integer
Dim strLine As String
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("D:DatabaseHomeExportCombinedHrs.csv", True)
[Code] ....
I am looking for a way to export my database and all linked files into one folder with automatic updated links. I would like to transfer the complete project to an other computer. Is this possible? How to do? Thank you.
View 1 Replies View RelatedWhich is better fo running a website which is small?
MySQL or Access?
Sathu
Hi i've got a table with a couple of fields
What's in the fields is irrelevant but i'd like to
change that to *****
the original value should stay there
but when you view the record it should only give *****
for example
field value : HELLO
when viewed: *******
Does anyone know how this could be done I ones knew how it was done but can't find it anymore
plz help
I only want the active screen on the toolbar. and all other screens like the DB screen and forms that are not active to be hidden on the toolbar.
I hope you can understand it. else i wil make a screendump of what i want.
I'm not brilliant at using MS Access - can't use the VBA codes or anything like that.
basically, I want to set up a database of photogrpahs where you store thephotograph in Access, and then run a query to search for specific photos, which brings up a form showing the picture.
I seem to be OK at doing all of this so far except I can't get the photo to appear in the form - it just displays a link which you double click on to get to the photo.
I've tried to look up how to sort this on the web but don't understand the way people speak about access :confused:
can anyone help???
I have completed a db and am satisfied with the structure and forms. The object window is hidden when the database is opened, and the main entry form automatically opens for the user. This db will be in a network folder and several users will have access to it. I want people to be able to enter data and run reports, but not make any changes to the database itself, or accidentally delete the database file or some other change to the structure. What is the proper way to set up the folder rights and anything else associated with the previous question?
Thanks.
Need importing just 1 column from excel file into vba !
View 1 Replies View RelatedHi,
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?
Many Thanks
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).
Any hints or suggestions are welcome
PS: using A2K db's in 2003
Thx :)
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;
View 1 Replies View RelatedI have a form that I am working on.
When you open the form, it asks for a tool # (which will be a drop-down box). There will be a command button that will bring up a sub-form. In the subform, there are 2 combo boxes. One picks the group that the tool falls under, and the other has the section of the group for the tool.
When the user picks the section, I need it to filter the questions that pertain to that section (which is listed in a list table), and pull those questions, and populate the master table with them. (i.e., Pulls "where is tool?" from tblQuestions, and puts it in the tblQuestionMaster when section is selected from combobox).
:confused:
I'm getting an error (SQL command not properly ended) but main thing is, I can't get it to recognize the ms access db.
In a nutshell...I want to query the oracle db for a number and count, then scan a number into msaccess and have it 'find' the matching number in oracle and return the count only to ms access. More comments below between the code and here are table names for both oracle and access:
ORACLE: Table name is: batch_status bs
Field names are: batch_num and Batch size
MS ACCESS: Table name is tblbatchstatus
Field names are: batchnum and idcount
CODE in PASSTHROUGH QUERY:
select batch_num,BATCH_SIZE
from batch_status bs
(this works and selects all batch numbers and sizes from oracle)
(If I say 'where batch_num = 60024 -----this works also....)
However...when I add the below part...I get errors...it can't seem to match to access database....
SELECT tblbatchstatus.Batchnum, tblbatchstatus.Idcount
FROM tblbatchstatus;
where batch_num = tblbatchstatus.batchnum
And I want to just say: where batch_num = <<<scanned batch number>>> and return the id count.