Deleting Selects Field
Oct 7, 2005
I have this code that works fine exept for one thing. If I have two names that are the same it deletes them both.
currentdb.execute "insert into charmer91 (last,first) values ('"me.list0 & "','" & me.list0.column(1) & "')"
currentdb.execute "delete table1 (last, first) from table1 where last ='"&list0 &"'"
How can I change the delete statement so it only deletes the selected name I have in list0, ie.. the highlighted one?
Basically Im moving data from one table to another via listboxes.
thanks in advance
View Replies
ADVERTISEMENT
Oct 30, 2012
I have created a database.On opening the [Company] form opens up.After pressing the 'Add new company' button it creates a new record. when typing immediately into the 'Company Name' field, access highlights all the text whilst your typing making you delete what you have just typed.I have tried several macros and vba at various different events to try and solve including 'Gotocontrol', 'refesh'. I have even tried SelStart.
View 1 Replies
View Related
Aug 31, 2006
i how i can get some help with this one, i have imported a txt file, once input into a table it has the fieldname inserted along with the data so for example record_id is the name of the field and in each column i have "record_id=###" the # respresent unique numbers. but what i am looking at doing is creating a "create table query and using an expressions like... "left([record_id],"=") but this won't work.
All the data in the columns has the same thing all have the field name with a = then the data i really need.
How can i separate?
PLEASE HELP ME!:confused:
View 2 Replies
View Related
Sep 19, 2004
I am trying to remove a "sort and grouping" field from my report. I delete the field from the menu by using the "backspace" key on my key board. I am getting any error, invalid sort field. When I put something into the field, the error goes away. How do I delete a sorting field I do not want??
thanks for your help
newbie and learning
View 1 Replies
View Related
Nov 16, 2004
Hi all!
I'm rapidly beginning to get a little confused... I'm trying to mass-delete records based on the content of a field.
I want to run either a query or a button on a form (or anything, for that matter) that will delete any records when there is an X in the N_Disp field.
Any ideas?
Thanks,
Barry
View 3 Replies
View Related
Nov 21, 2011
At present I am running Revit Architecture with DBLink to access to edit fields for shelving etc.
What i am doing is fitting out warehouses with 600+ shelving units and they are all numbered.
These numbers are tagged in the drawing.
What I am hoping to do is export the data to access (completed) then amend any changes within access.
Normally, we would put say 600 shelving units on a drawing all tagged 1-600. But then the client may want to delete shelving unit 321.
Leaving the drawing with all shelving units tagged 1-320,322-600.
How would i go about it so access renumbers all data after the deleted field (ie number 321)?
View 2 Replies
View Related
Mar 17, 2007
Is it possible to create a query which selects selects fields in a table, not just data in a field? If so, how?
TIA
View 1 Replies
View Related
Aug 9, 2014
So instead of having all of my data on a single table which is filtered out as I make selections in my initial 3 combo boxes, I'd like for each set of data to be on it's own table. Instead of filtering out the irrelevant data in a single table, I'd like the initial 3 combo boxes to instead filter out the irrelevant tables. My main reasoning for doing this is that I figure it would first off save me much trouble in the future when editing data within the tables and also that one huge data table would slow down Access eventually.
View 9 Replies
View Related
Aug 26, 2014
I am setting up an Access database for a small school. The database contains a record per person. When we get a duplicate lastname field we set a family_id field to lastname followed by a number.
So for example the first Smith family needs to family_id. But the second Smith family gets a family_id of smith1. To select an ordered list of Families I need a query that will use the family_id if it is set and if not use the the lastname to collect family members.
My query uses a presorted table, CurStuByGrdTbl , of current students to produce the families directory by grade and alphabetized.
The WellSchoolCommunityAll table is the entire database. So if the student entry has a valid family_id, matching pattern "*#" then
I perform a Like with family.id otherwise I perform a Like with lastname.
This query gets me nothing, no records.
IIf(([CurStuByGrdTbl].[family_id]="*#"),[CurStuByGrdTbl].[family_id] Like [WellSchoolCommunityAll].[family_id],[CurStuByGrdTbl].[lastname] Like [WellSchoolCommunityAll].[lastname])
View 4 Replies
View Related
Mar 28, 2014
In my Access2000 db, I have a table, on which many different queries are based. Many forms are based on those queries. I want to delete some redundant fields from the table. I tried deleting one, but I found I could no longer open any of the associated queries and forms. Is there a simple way to delete table fields so that it doesn't stop me from opening associated queries and forms? (There are hundreds of them, and I have a lot of table fields that I want to delete).
View 1 Replies
View Related
Dec 21, 2005
I'm creating an Access data entry form for computer installations.
Basically the entry form will choose a software and license, then select one or more computers to install the software on.
Then later we can create a report that can track what softwares are installed on the computers, or track what computers has what softwares.
Now my question is for each record of the installation entry form, after we select the software, then we will choose a computer name or more computer names.
If just one computer is easy, but how can I choose multiple computers, which control should I use? and how can I store the computer name list in the installation table, do I have to make each computer installation a record in the table or put multiple computer names in one record in the field like memo, but if I use memo, later it seems i cannot group or search memo field?
Can anyone give me some ideas about what is an easy design solution or samples about these?
Thanks a lot
View 1 Replies
View Related
Oct 31, 2006
I'm using Access 2002 and when I try to scroll up or down it selects the row and then scrolls 1 row. Is this a bug?
thanks
Paul
View 1 Replies
View Related
Aug 22, 2013
why this query pulls every record twice and what I can do to fix it?I did it in design mode because I'm an amateur, but here is the SQL.
SELECT [Patient Data Table].[First Name], [Patient Data Table].[Last Name], [Patient Data Table].[C MRI Facility], [Patient Data Table].[C MRI Sched Date], [Patient Data Table].[C MRI Scan Done]
FROM [Patient Data Table], [Date Range]
WHERE ((([Patient Data Table].[C MRI Scan Done])=Yes) AND ((Month([C MRI Sched Date]))=[Date Range]![From Month]) AND ((Year([C MRI Sched Date]))=[Date Range]![From Year]));
View 6 Replies
View Related
Sep 7, 2005
Hi,
I'm trying to run a pass thru query into a sybase server from Access. The query contains multiple selects which Access doesn't appear to like very much. The query works fine when run in Sybase.
Is there any way of circumventing this problem?
I could split the three selects into three seperate queries to view the individual results but the third set is dependant on the previous two sets and so therefore is difficult (impossible?) to seperate out as a third individual query.
Thanks for any suggestions?
Select
s.id_prtf Portfolio_ID,
s.id_imnt Instrument_ID,
s.id_imnt_swap Instrument_Swap_ID,
s.id_ccy_std Currency,
s.am_posn Position,
s.am_pnl_eqty PNL_Eqty
into #temptable1
from
SET_VALUATION s, INSTRUMENT i
where
s.id_imnt = i.id_imnt
and s.id_posn_ref is NULL
and s.id_imnt_swap is NULL
and s.am_pnl_eqty > 0
and i.id_typ_imnt != 'SFX'
Select
s.id_prtf Portfolio_ID,
s.id_imnt Instrument_ID,
s.id_imnt_swap Instrument_Swap_ID,
s.id_ccy_std Currency,
s.am_posn Position,
s.pr_imnt_mtm_prev,
sy.pr_imnt_mtm,
s.am_mv_prev,
sy.am_mv
into #temptable1a
from SET_VALUATION s, SET_VALUATION_HIST sy, REFERENCE_DATA r
where
s.id_prtf = sy.id_prtf
and s.id_imnt = sy.id_imnt
and s.pr_imnt_mtm_prev != sy.pr_imnt_mtm
and s.am_mv_prev != sy.am_mv
and r.dt_bus_lst = sy.dt_bus
select
t.*,
tt.pr_imnt_mtm_prev,
tt.pr_imnt_mtm,
tt.am_mv_prev,
tt.am_mv
into #newtemp
from #temptable1 t, #temptable1a tt
where
t.Portfolio_ID = tt.Portfolio_ID
delete from #temptable1
where #temptable1.Portfolio_ID in (select Portfolio_ID from #newtemp)
delete from #temptable1a
where #temptable1a.Portfolio_ID in (select Portfolio_ID from #newtemp)
select "Where id_posn_ref is NULL and id_imnt_swap is NULL is the only criteria"
select * from #temptable1
select "Where SET VAL's pr_imnt_mtm_prev does not equal SET VAL HIST's pr_imnt_mtm, is the only criteria"
select * from #temptable1a
select "Where the output satisfies both Where id_posn_ref is NULL and id_imnt_swap is NULL AND Where SET VAL's pr_imnt_mtm_prev does not equal SET VAL HIST's pr_imnt_mtm"
select * from #newtemp
drop table #temptable1
drop table #temptable1a
drop table #newtemp
View 2 Replies
View Related
Apr 29, 2008
Simple query, but I need to roll the results up with others and a null return is hurting me. I have been chasing my tail around with the nz(), input masks, IIF statements, only to prove my ignorance and possible insanity for expecting different results.
Here we go:
SELECT Count([LDB LOG].DSTHTL) AS ["tot120"]
FROM [LDB LOG]
WHERE ((([LDB LOG].[LDB Rcvd]) Between Now()-120 And #1/1/2008#))
GROUP BY [LDB LOG].[T-7 Date]
HAVING ((([LDB LOG].[T-7 Date]) Is Null));
This is being used to count open items into age buckets, the above is the greater than 120 days (discounting anything prior to 2008). If the T-7 Date is null the item is open. What is happening is that no records are being selected, resulting in the null value back.
Any help would be greatly appreciated as I have spent an unrealistic amount of time on this.
Thanks
Brendan
View 6 Replies
View Related
Feb 23, 2015
I have a field that is pre filled in with a value. The name is "Business".Then there is a combo box with various values. If the user would select a value that does not match with values that can be selected if in "business" a value is selected, it should show a message that the selection is wrong and user needs to select the correct one.
Example:If in the field "Business" the value "Food" is selected, then the possible values to be selected in the combobox would be: Fruit or Meet or Fast Food. If the value in "Business" would be "Wood" only selections could be made: Talble or Chair or Cabinet.So, if a user select "Food' in "Business" and in the combo box "Chair", it should show a message that the wrong selection was made and don't let them use the wrong one.
View 11 Replies
View Related
Aug 24, 2014
I open a form (EntryBasic) for users to enter data in, which is then saved to the only table in the database (MasterTable). The form (EntryBasic) has a list box with four choices: red, white, blue, orange. The button that opens the form is placed on another form (MainForm) The form (EntryBasic) record source is MasterTable.
A user is on the MainForm and needs to add a new record related to red. When user clicks on the button (NewRedEntry), I need the form to open and the list box pre-selected to red.
Is there a macro or VBA code that might simply execute this action, preferably something that can be attached to the NewRedEntry button?Even better, if there are two list boxes, code such code be applied to same NewRedEntry button, where both list box values are pre-selcted with the click of this button?
View 5 Replies
View Related
May 11, 2012
I am trying to find out if there is a way to track the order in which items are selected from a list. I am a dabbler and any keyword searches that I can think of don't bring up what I am looking. So, here is what I am trying to do:
I have a table of symptoms with 3 fields (ID, Category, and Symptom).I have a combo box that will allows the user to pick a category (using select Distinct on category field). I then have a list box populated with all the symptoms that have a category of whatever the user selected. The problem I am having is that I need to somehow track the order in which the user selects symptoms and then save that order for future reference and to be printed on a report. The order is important because the most severe symptom needs to be listed first.
View 7 Replies
View Related
Apr 12, 2008
I have two tables.
1. Persons (list of persons)
2. Job history (list of jobs)
each person have their own job history. all these jobs are stored in the job history table. when i delete a person i would like the job history for this person deleted as well. each job stored in the job table have a field with person name, so that it is linked to this person.
how can i do this? vba or simple properties options?
- Roy
View 1 Replies
View Related
Nov 3, 2014
I have a query that selects the "Active" records for three different selections, A, B or C.. There may be 1, 2 or 3 results for a particular selection. That is Selection A may have one result or active records, but Selection B may have three results.
I want to use data from the query to populate a field on a form. For example, if the results for Selection A, having one "Active" record would be RecordAData. But for Selection B with three "Active" records the result would be RecordBData & " " & RecordBData2 & " " & RecordBData3
My query is:
SELECT tblSomething.ID, tblSomething.D1, tblSomething.D2, tblSomething.D3, tblSomething.D4, tblSomething.D5, tblSomething.D6, [D3] & " " & [D4] & " " & [D5] & "-" & [D6] & " " & "SomeText" AS Header
FROM tblSomething
WHERE (((tblSomething.D1)=Forms!frmSomethingHeaders!D1) And ((tblSomething.D2)=True));
The concatenation in the query is labeled "Header". I want to be able to Concatenate the "Header" which in itself is a concatenation in the query.
I thought that this might be a looping through the query results, but I cannot figure out how to do it. But then, that is only my uneducated guess.
View 11 Replies
View Related
Aug 14, 2013
I have a list box whose data is inserted in a table named as "test0" ,now in a macro of vba i want to select all the items in the list box and create there pdf files in a folder at my desired location. So far i have managed to create a pdf file of single item ,but i want to select multiple items at once ,
Code:
SELECT test0.ID, test0.item FROM test0 WHERE (((test0.item)=[ItemNumber]));
So, in this query itemNumber are multiple and i want to create there pdf files at once ..just on a click of one button ?
View 2 Replies
View Related
Oct 24, 2007
i would like in a form for a combo box to be able to select an item from a table and input relating information automatically into other boxes in the form..
I have 3 tables: Table 1 has product code and product description.
Table 2 has invoice number company details, address etc.
Table 3 has product code and product description qty and invoice number..
Table 3 relates to table 2 by the invoice number and table 3 product code looks up the product codes available in table 1 and also table 3 looks up the list of products descriptions in table 1 using the combo wizard.
This means the wrong code can be put with wrong description.
What i would like to know is how i select a product description and the product code in the form fills out automatically?? i hope this makes sense please helppppp!!
View 5 Replies
View Related
Jul 31, 2014
I have a database for mutli users which is split. Front end is available separately for 3 users. These users make data entry in the same form from their front end form. This particular form has combo box that retrieves data based on selection in combo box and the use starts filling the data in the subform. Upto this, form is Ok, no problems seen.
But when the second user opens the same form from the split database front end stored in his computer, and once the second user selects another record from the combo box, first user's record also changes immediately to the record of Second user. Both users now have the same record even though the form is separately accessed from front end.
View 1 Replies
View Related
Feb 21, 2005
I have a problem about deleting record from a form, it will be grateful if anyone can help me with this.
Here are my tables:
Par_tor: some personal details such as ID, name, phone NO., etc
Par_tion: the record of who has participated which activities.
Activity: name of the activity.
My problem is: the main form of the my form contains the stuffs in table Par_tor, and the subform contains the stuffs in Par_tion, so the user can view these participator's details and the activities they have participated.
In the main form, I also wanted to created a delete button to delete an entire record (including a participator's detail + his/her participation record) at once, but it didn't work since the participation was in the Par_tion table not in the Par_tor.
So is there any way that I can delete these records at once?
View 4 Replies
View Related
Jun 15, 2005
Hi
I have a form with a "Clear Form" button on to allow the user to delete the record they are working on without updating the database. A question window pops up asking if they are sure they want to delete the record, if they answer yes the form will clear and re-load without updating the database.
The OnClick Event Procedure will not allow me to delete the data, it always saves. I've tried
DoCmd.SetWarnings False
If MsgBox("Are you sure you want to abandon changes to this record?", vbExclamation + vbYesNo, "LogBook 2002") = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
Else
DoCmd.SetWarnings True
End If
but I get the error "2046 - The command or action DeleteRecord isn't available now." Is there another way, a better way?
View 9 Replies
View Related
Aug 4, 2005
I have two tables (same data but slightly different attribute structure) with a one-to-one relationship (the join field is "ID"). There are 69 matching records in these tables. How can I delete these matching records from table A, while leaving them alone in table B?
I'm confused because I brought both tables into a select query, created a join from ID to ID (within in the query's design view), and then added all fields from table A to the query. I then ran the select query and saw the 69 records from table A in the query's data view that I wanted to delete, highlighted all records and clicked delete. However, this action deleted the 69 matching records from TABLE B, not Table A!!! How is this possible? What should I do instead? Thanks.
View 3 Replies
View Related
