Queries :: Inserting Values From A Listbox Into A Table
Jun 28, 2013
inserting values from a listbox in to a table. I have a table with a field named: PrefReports. This field will hold data from values in a list box that the user selects.
Table: Profiles
Field Name: PrefReports Type: Memo
The user utilizes a form to input the values necessary for the query. I then have the query tied to a command button. When the command button is clicked, the query is executed.
How can I take what the user selects in the list box, and insert into my TblProfiles.PrefReports field? When I run the query, all other information makes it to the table except the list box values.
I have a calculated field (Bhours) with the following IIf function:
=IIf(Fields!BDate.Value<=Parameters!Pdate.Value and Fields!EDate.Value>Parameters!Pdate.Value,Fields!H ours.Value,0)
It works perfectly, but I don't like the fact that it has a zero for the false part as I like the field to the blanc instead of filled with a zero.I can't use a space as I need to calculate with this field later on. I tried using "", but then the field displays #error".
I am a newbie to ACCESS. I am stuck with inserting values of combobox directly into a record in table.
I have five different tables(each has only one cloumn) and a complete full_details table(contains 5 cloumns).
I have a simple form with five comoboxes and a save button on it. The five comboboxes are populated from five different comboxes at the time of form load. Now, I want to add values selected from these comboxes in fulll_details table.
My form is unbound. I tried with VBA code, on the save button click, I have written ,
Private Sub save_Click() CurrentDb.Execute "Insert into Full_Details(Col1,col2,col3,col4,col5) Values ('" & Combo0 & "','" & Combo1 & "','" & Combo2 & "','" & Combo3 & "','" & Combo4 & "');" MsgBox "Record Saved !!!", vbInformation, "Success" End Sub
It gives me some error : "You can't reference a property or method for a control unless the control has the focus property set"....
See, above query works fine , if I set focus of one combobox,but it adds only 1 combobox value in full_details table as only 1 column value.
I am getting method of data member not found for the following code when inserting values to a table. Form has three frames. Add/Insert is on OnClick Event.
Firstly, is it possible to hide unchecked values in a listbox? I have a user with several roles and I want to only show the ticked roles in the listbox.
Secondly, can you create a hyperlink on listbox values? i.e, if I click on "Manager" in the roles listbox, it follows that to another form and opens the record about managers?
I have a listbox inside a subform. I want to use the 'click' Event on a "search" command button (found on the main form) to trigger a query from inside that listbox and fill it with values from a table. The following is the argument I have written for that event procedure:
Private Sub CommandSearch_Click() Me.GS_SearchSF.Form.listSF1.RowSource = _ "SELECT MaxSize1 " & _ "FROM GS_Ctg_Float " & _ "WHERE Supplier = '" & Me.GS_SearchSF.Form.SupplierQuery.Value & "';" 'THIS ONE ISN'T WORKING End Sub
Except as you might imagine, it is not working. It works when I just type a name from that table into the argument in place of the text seen above in red. For instance:
Private Sub CommandSearch_Click() Me.GS_SearchSF.Form.listSF1.RowSource = _ "SELECT MaxSize1 " & _ "FROM GS_Ctg_Float " & _ "WHERE Supplier = 'Viracon';" 'THIS ONE WORKS End Sub
What am i doing wrong?
Here's some background:
GS_SearchSF is the name of my subform
listSF1 is the name of the list box in the SubForm that the query is supposed to fill
SupplierQuery is the name of the list box in the subform I want to extract the value from and put into the query
In the above example student 1234 has a missing record in year 2004 which supposed to be Term2 in the previous year (i.e. 003) and Term2 supposed to be Term1 value in the following year (i.e. 004). Similarly for student 3311
I have a form in which users can enter data in several textboxes to filter the listbox below it, this works great except for the fact that when a record lacks certain data it doesn't show up
Basically there are 4 filters, one for the name/id which works great, as the entries without id's show up just fine but this filter needs to be checked against the other 3 filters, for their group, education and type.
Lets use group as an example.
When a student has no group it should only show up when the group filter is an empty string. AFAIK the wildcards should see to that.
Currently however, when a student has no group, it doesnt show up at all unless I remove the | Like '*' & [groepFilter] & '*' | part from the query.
I've never used complicated WHERE's like this so it might be something very simple. I think I could get it to work using VBA and modifying the rowsource of the listbox from there but this would be a lot easier and I'm curious as to why this doesn't work.
The SQL:
SELECT Student.Studentindex, Student.studentid, Student.studentvn, Student.studenttv, Student.studentan, Student.Groepcode, Opleiding.opleidingsnaam, Opleiding.type FROM Student LEFT JOIN Opleiding ON Student.opleidingid = Opleiding.opleidingid WHERE (((Student.studentid) Like '*' & [naamFilter] & '*') AND ((Student.Groepcode) Like '*' & [groepFilter] & '*')
I have a form that functions as information display/update for a table. It also needs to display a list of relevant codes from another table. The codes in the second table have an ID that will match an ID field on the main table. However when I do this I either get every code in the code table, or nothing.
Here is the SQL that I'm trying to use:
SELECT table_code_lookup.code_text, table_codes.code_extra, table_codes.alum_id, table_codes.ID FROM (table_code_lookup INNER JOIN table_codes ON table_code_lookup.code_id=table_codes.code_num) INNER JOIN table_alumni ON table_codes.alum_id=table_alumni.ID WHERE (((table_codes.alum_id)=me.id));
However anything I can think of putting for me.id doesn't work. I've written a sub that will modify the query and insert the correct ID num, but I can't figure out how to get it to run when I change records with the record selector.
I have the nagging feeling that I'm missing something totally simple, but can't figure out what it is, where am I going wrong?
I have a form, has some fields, one of them is the current date, so when the user click (save )button , which make (add new record )to the only table I have the problem that .all fields are inserted in the table , except the current date !! it is a text box ( Now() )!!
I'm trying to insert 10% of a dataset from dbo_billing into another table Random_Temp. Another form is open when this query is to be ran that passess in the billyear and billmonth... I'm sure it's a syntax issue as I can isolate the random number part and it displays the appropriate data, I just can't re-write it to insert into the other table:
INSERT INTO Random_Temp ( indx, peopleId, audited ) SELECT TOP 10 PERCENT b.indx, b.peopleId, b.audited FROM dbo_Billing AS b WHERE (((b.billYear)=[Forms]![billing]![billyear]) AND ((b.billMonth)=[Forms]![billing]![billmonth]) AND ((b.recertifying)=-1)) ORDER BY Rnd(-(1000*b.indx)*Time());
Whenever I create a combobox, Access asks me if I want to grab the values from another table, or if I just want to manually input the possible values.
My question is: is one of the two solutions "better" than the other?
Let's imagine I am creating a Form where I can create a new Contact, and in a listbox (with multiple selections allowed) I want to list the possible character traits, so I have
I think that if I need to store in the Contact records these possible traits, I will have to create a Many-to-Many relationship (therefor creating a junction table) between Contact and Traits.
On the other hand, could I do everything without grabbing values from an extra table and directly inputting the values in the listbox? How would these elements be stored in the Contact record if I allow multiple values in one single field?
I am currently trying to make a form which will generate a report for the users. Currently the form has 3 combo boxes where users select which fields they want displayed from the table in the report. They also have sorting options next to them (Asc or Desc). I am now trying to put in a multi select list box under each combo box to allow users to filter the report to their liking. Currently I am trying to pull distinct values from the specific field of the table and display them in the listbox for users to select.
I have tried using vba to set me.filter1.rowsource = SELECT DISTINCT me.combo1 FROM EVAP_Database, but this doesnt seem to work. I have also tried a few other codes and still no luck.
HI, I would like to insert values to differetn tables using a form, is that possible with just access? or does it need VB script or SQL, if so, is there any example to follow. my form made of a combination of two tables attributes ad when I enter say:
name tel number address car car reg booking time
then I would need that info to go to the table it belongs to, as there is three tables, one is the car, the other is customer and the last is booking. thanks
I'm using Access 2010. I need to calculate a score based on values selected in a table by looking up corresponding values in other tables. I have a "Project" form to create new entries into the Project table (see Table 1). When I create a new project record, I will select values for the Payback and Need fields by selecting options from a list. The Payback list is pointed at Table 2 and the Need list is pointed at Table 3. In the below example, I created the "ABC" project and selected "1 year" for the Payback field and "Repair" for the Need field. Pretty simple.
Now that I have the "ABC" project loaded to my Project table, I'd like to create a report that will show a "score" for this project. The score should be calculated as follows: Payback Impact + Need Impact. In this example, the score should be 30 (Payback Impact of 20 + Need Impact of 10).
I have a query with an INNER JOIN and ORDER BY that is working great. Now, using the same JOIN, I need to update values in one table with the values in another. I thought it would be simple until I learned you can't do an ORDER BY with an UPDATE. Is there another way to achieve the same result? If you remove the 'ORDER BY', the statement below doesn't produce an error but the results are not correct:
UPDATE TableA INNER JOIN TableB ON (Left(TableA.CDN,6))=(TableB.CDN) SET TableA.HCC = TableB.HCC WHERE TableB.HCC Like '241*' AND TableB.BBB = 'X' AND TableA.CCC = "1234" AND TableA.HCC IS NOT NULL ORDER BY TableB.HCC, TableA.CDN;
I am using an unbound form to insert data into several tables, all related, at the same time. Please let me know if what I am trying to achieve is too ambitious!
I am developing a material sample library... any sample could be one material, two materials or three materials. Let us say that A, B, and C are three primary materials. I have three Tables: tblX, tblY, tblZ. tblX stores details for each individual material. Its structure is as follows:
tblX XPK | ID | value1 | value2 | 01 | A | asdfas | asdfdf 02 | B | dfasdfa | sdfaf 03 | C | asdfffd | asdfg (here, XPK is the primary key, ID is the name of the material and value1, vlaue2 are other descriptive fields...)
tblY stores materials that are combinations of two primary materials: tblY YPK | 1st | 2nd | ID | value1 | 101 | 01 | 03 | M | keiury | 102 | 02 | 03 | N | kjgeiih | 103 | 03 | 01 | P | djlkgoi |
(here, YPK is the unique ID for a sample, the '1st' and '2nd' fields are simply the primary keys from the tblX. ID is the name of this new material and value1 has descriptive values for samples M, N, etc.)
tblZ is the next level of material, made of combinations of materials from both tables tblX and tblY. it is like this:
(here, ZPK is the unique identifier, the primary key. as you can see, 1st, 2nd, 3rd, 4th are references to materials from either tblX or tblY or none, ID is the name of the material and so on.)
So, going back to the original primary materials, a sample could be a combination like A + (A+C) + B...
I hope I am clear enough till this point.
My unbound form (which is a very complicated one by now!) is where a user will start with such a sample and start putting in details for each of the primary materials. As he/she logs each individual material, I store that data in a temporary table (tbltemp). When all the primary materials are fed in, the user hits a 'Save' button, which is supposed to do the following:
1) save each primary material from tbltemp to tblX 2) extract their primary keys and then insert that into the next level table, either tblY or tblZ.
I have been working with ADO recordsets to 'addnew' data to tables and am adept with that. I have never simultaneously extracted data from one table and inserted into another. similarly, i have never extracted more than one record, and inserted them into a single record in another table, etc.
I will appreciate any guidance, both at conceptual level and at operational level, that I can get. If you would like to see my database, I could arrange to have it accessible; there is nothing confidential (only higly complicated, I think!).
If you are still reading, I already owe you a ton of thanks!!
I have a form (all_trucks) with a listbox (qryNames). The values of the Listbox are Query Names. When I click on of of the Query Names in the Listbox it runs the Query.
I have created a table of nothing but Query Names. What I want to do is for the Listbox to show the Query Names in the Query Names Table and then run the query from the Listbox whenever I select the Query Name.
I do not know how to make the ListBox access the Query Names Table and then execute the Query when it is selected in the Listbox.
I am just beginning to manipulate Access from Excel.
I am trying to insert a row into a table. I am adding values for every field (8) except an Autonumber.
I have 2 Insert strings which are identical except for the fact that the one that doesn't work, doesn't specify which fields I am entering data into, which I presume shouldn't be necessary in this case.
When I try to use the second version I receive the error message...
Number of Query Values and Destination Fields are not the same.
I have two tables (Table 1, Table 2). Table 1 is blank and has the fields "Field1, Field2, and Field3." Table 2 has data and 10 different fields ("Field1, Field2", etc). I want to copy all data from the matching fields in Table 2 to the corresponding Table 1. I've tried an Update Query in design view. Did not join any records, and just a simple:
Field: Field 1 Table: Table 1 Update To: [Table2]![Field5]
This returns 0 records to update. I thought about a Make Table to override the entire Table, but that isn't working either.
I have designed a form with a Listbox which pulls information from a query. The listbox multiselect vaue is set to NONE.
As I select a specific record, I have an update query which runs silently in the background through an implemented macro. The subroutine then requeries the listbox to update the latter with the latest values.
Here is a description of my problem:
1. After running the update query and upon requerying the listbox to update its values, I lose my initial listbox row selection. It is imperative that the listbox defaults to the user initial selection after the update requery action occured (note that this would be the same record).
I have searched the forum, but did not find a specific answer.
I have a load of data in an excel spread sheet that I'm trying to put into an access using Excel VBA (Jet/ACE SQL I think!?). I do this by constructing INSERT INTO strings and looping through my spread sheet. Let's say the primary key is associated with the field [Company] which holds the company names. A simplified version of what I was using (it all works great ) is below:
Code: Set rs = New ADODB.Recordset Set cn = New ADODB.Connection cn.ConnectionString = GetConnectionString() cn.Open cn.Execute strSQL
where strSQL =
Code: INSERT INTO tblTestMDB ([Company], [Date]) VALUES ('BP', '30/09/2012')
Unfortunately, I am having issues with users adding the same company twice. I'd like it to:
- update the existing company info if the company already exists - add the company if it is not already there.
I'm trying to do it in two stages. First an update, then an add. The first part works great :
Code: UPDATE tblTestMDB SET [Date]='21/03/2015' WHERE [Company]='BP';
The second part: (I have tried numerous variations on the syntax):
Code: INSERT INTO tblTestMDB ([Company], [Date]) VALUES ('BP', '30/09/2012') WHERE NOT EXISTS (SELECT * FROM tblTestMDB WHERE [Company] = 'BP');
I find the error messages rather cryptic but they include things like "Query input must contain at least one table or query." or "Missing semicolon ( ; ) at end of SQL statement." (<== No I'm not!?
