I can write an UPDATE statement fine, what I don't quite understand though, is updateable statements involving forms.
To populate a form you have the form record source set to some SELECT statement and then you place your controls and set the control sources to the fields in the SELECT statement. This I understand, what I don't understand is the mechanism that allows updates.
This may seem obvious but do I need to reference the key for that particular record as well? How does this work?
I having been going mad for a week trying to write a query, please help me.
I am trying to get the MaxServicedate for each Product (PIDFK) for every Location (LID), but then I also need the MaxServicedate regardless of Product use, for each Location that is smaller than the previosly obtained MaxServicedate.
I hope that makes sense.
I have tried this query ---------------------------------------------- SELECT T.LID, T.PIDFK, T.MaxOfServicedate, Max(tblTakings.Servicedate) AS NextMax, T.Installdate FROM (SELECT tblLocations.LID, tblTakingsProductMM.PIDFK, Max(tblTakings.Servicedate) AS MaxOfServicedate, tblLocations.Installdate FROM (tblLocations INNER JOIN tblTakings ON tblLocations.LID = tblTakings.FKLID) INNER JOIN tblTakingsProductMM ON tblTakings.TID = tblTakingsProductMM.TIDFK GROUP BY tblLocations.LID, tblTakingsProductMM.PIDFK, tblLocations.Installdate) AS T LEFT JOIN tblTakings ON T.LID = tblTakings.FKLID WHERE (((tblTakings.Servicedate)<[T].[MaxOfServicedate])) GROUP BY T.LID, T.PIDFK, T.MaxOfServicedate, T.Installdate ORDER BY T.LID, T.PIDFK ------------------------------------- But I am missing some records!!
The subquery gives me all 90 records with the MaxServicedate for each PIDFK in each LID
The main query is missing the records that dont have a service date previous to the MaxServicedate in the subquery!
I have a form (frmTraveler) in which a user is given a selection of levels for a type of trip from a drop down box (TripLevel). Basically, if a user picks level A, then I would like a value to be written to the corresponding record in another table (tblHotel). The PK is an autonumber (TravelID) and the tables have a one to many relationship. Hope that's enough information for some tips! :)
I have problem in creating conditional expression for query table. I want to add IF Else statement in the statement so that if the value is more than 50, a new field called grade will display the grade of "A" in the new field.
Can anyone teach me how to write conditional expression.
I am trying to write a query that will return records from multiple tables. I currently get an error suggesting I create a subquery or else I get far too many records.
The query is EditAttendanceQuery (I left it in a bit of a state). The fields I need are shown in the query. The records I need are based on the Edit AttendanceQuery (Form). I can get the records I need without the CourseNumber and Section, but it all goes downhill when I include them.
The VEH and POS are just going to be Labels in a form....no prob. Each Soldier's Squad and Team (for mounted Vehicle and Position) are stored in the Personnel Table. The below query is for vehicle A7 (ACTUAL would be the same as TC above). The query works. I just need to know how to get the result to display in a text box. What I'm planning on doing is creating text boxes for each posistion with these small select queries, so when I update the SQD and Team in the Personnel Table it updates on this form. Or is there an easier way to do this?
Dim strSQL as string strSQL="SELECT [tblPERSONNEL]![RANK] & " " & [tblPERSONNEL]![LAST_NAME] AS NAME FROM tblPERSONNEL WHERE (((tblPERSONNEL.SQD)="A7") AND ((tblPERSONNEL.TEAM)="ACTUAL"))"
I have a query with many fields. One of them is the combination of two fields and is called "Components Reference". What I try to do is, every time this query is updated, the records of only this field "Components Reference" to be written to the field of another table which also takes records from an another query. I wish I was clear enough.
all using access 2010. I have a date field. I need to write a query to pull out dates with current year only. ex data: 1/1/10, 1/1/11, 2/1/12, 2/1/13. Need to pull all dates with current year which would be 2/1/13. Tried in query criteria of the date field: =Format(Now(),"yyyy") I received data mismatch. this is a date/time field.
see below the code . The select statement searches the Printpoolno value from the top to bottom in table tblmaster. As in my table tblmaster there are thousands of records and it takes long to search for that Printpoolno from the table . Is there anyway we can write a query that will search the table from bottom to top as the Printpoolno will always be in the bottom records and not in the top records.
Code:
Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim r As Long
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 ?
I need to display the year, Date, agent name, amount field, note and sub total for each year.I need to write the SQL query code in VBA and i designed the report with proper field positions.
YEAR column: Display Value only once for the FIRST ROW for Maximum value of Date field value for each set of YEAR. If it has only one record, it display that year.SUB TOTAL Column: Display Total Value for Amount field only once for the LAST ROW of each set of year record (Mininum value of year). How to make it available the values for first and last record alone ?
I just wrote my SQL code based on your inputs in my editor..Based on the input from Plog (from this forum), i wrote a code which listed below.
Code: Private Sub Report_Open(Cancel As Integer) Me.RecordSource = "SELECT CessioneCredito.Anno, CessioneCredito.Data_Movimento, CessioneCredito.Note_Liq_Cessione_Credito, CessioneCredito.Importo, " & _ " Agenzie.Denominazione, DCount("[Anno]","CessioneCredito","[Anno]=" & [Anno]) AS GroupSize,
[Code] ....
While saving it, it shows the code in RED COLOR due to compilation error.As I am writing the query in Me.Recordsource = "SQL query", where i should place the below listed code in the query as per Plog???
Change the Year Control Source to this: =IIf([GroupPos]=1,[Year]) Change the GroupTotal Control Source to this: =IIf([GroupPos]=[GroupSize],[GroupTotal])
If this is not possible in Me.recordsource, then its advisable to use recordset like writing in two different queries.
Code: Dim ds1 As Recordset Dim ds2 As Recordset myquery1 = "SELECT CessioneCredito.ID_Agente, CessioneCredito.Data_Movimento, CessioneCredito.Importo, CessioneCredito.Anno, CessioneCredito.Note_Liq_Cessione_Credito, Agenzie.Denominazione FROM CessioneCredito INNER JOIN Agenzie ON CessioneCredito.ID_Agente=Agenzie.ID_agenzia WHERE (((CessioneCredito.ID_Agente)=[Reports]![R_StoricoCessCredAg]![ID_Agente]))"
i need to update some textbox value when insert some value at another textbox.. like when i add 3 at 1st textbox, 2nd text box will calculate it auto matically wit this formula, 2ndtextbox = 1sttextbox - 1 ...
so where should i type ? query? how? default value? if need to wrte code, which 1 i need to write it? after update? before update?
on my laptop, i have an access front end with tables linking to sql server personal edition.
i open a linked table, edit a field, and i get a write conflict error message 'this record has been changed by another user since you edited it', and the save record button is not enabled
i have many linked tables, but this is the only table that gives me this error. i have deleted the table in sql server, and made a new table, and started the link process again, but still the write conflict.
also, as i dont know if this is related.
when i get tothe screen to link the tables, i see 2 table names prefixed with a "~", however, when i go into sql server, i cannot see any tables prefixed with a "~"
any responses would be most appreciative, as my project has effectively stopped until i can resolve this.
Hi I have a small database written in access 2000 which has be compiled as a mdb, this resides on a file server and can be run from a number of workstations on the network . It has been running ok for 2 years until a couple of weeks ago, on one particular pc the database opens and you can do reports etc view exist data as normal ,but you cannot write to a table ( these are contained in a second table only data base ) if you run the program on any other pc workstaion it works fine as it has for 2 years on this problimatic PC.
it must be something on the particular pc , we have reinstalled Access 2000 to no advail
What is the best way to go about doing a 2.0 version of a database? When I wrote ours I was a newbie. Not that I am an expert now or anything but things could definately use an upgrade!!!
I need to clean up the forms, etc. Do I just dump all the tables in from the old one and start over?
I also want to completely revamp a couple of things. How would I make sure that all the purchase order numbers (auto number field) still matched?
users are using a hand held scan gun to enter data into a field. if the barcode is 12 characters long, I only want the 1st 7 to be written to the table.
I was thinking a validation rule using TRIM or LEN but can't seem to get it to work.
I want to use the value from a textbox as openargs for another form. If I don't do a write to the table with SQL, then I get a NULL error on the getOpenArgs on the form. If I do do a write, then I get multiple occurrances of the record.
I have a contact table, and a mailing list table Each contact can belong to one or more mailing lists. Hence a created a intermediate table to join the contact and mailing list tables, containing contactID and mailinglistID.
The problem is that when I want to register a contact in several mailing list, I need to be able to do it in one time : For example, selecting the contact in a combobox, then affecting him to mailing lists by making multiple selection in a drop box or ticking checkboxes.
For example, I want contact 1 to belong to mailing list 3,6 and 8, wich would result in the jonction table in 3 records 1-3 1-6 1-8 without having to repeat 3 times the same operation for each mailing list.
I am keep on getting Write Conflict whenever I check a check box called chkHCE. There are over 3000 records and it's happening for every records. I would understand if it's happening once in a while but it's happening for every records.
Please suggest. I attached the error print shot. The data souce is a qry but updatable query. The query has two tables tie together. I never had any problems. Can anyone share their idea Please ???
Thanks
Code behind the chkHCE
Private Sub chkHCE_Click() If Me.chkHCE.Value = True Then Me.HCEInitialPrepared.Enabled = True Me.HCECompletionDate.Enabled = True Me.HCEType.Enabled = True Else Me.HCEInitialPrepared.Enabled = False Me.HCECompletionDate.Enabled = False Me.HCEType.Enabled = False End If
I am getting the following error: Write Conflict--This record has been changed by another user since you started editing it....
I think i know why, but I am sot sure what to do about it.
I have a main form with a sub form, each is based on a stored procedure with only one table in it (not the same table) and only one parameter.
When I make a change to anythign in the subform I update the main form's "last changed date" field then when I hit save on the main form I get the error.
How do I resolve this?
I am using Access 2002 SP 3 with an .ADP not .MDB and MS SQL Server 2000.
I've created a query that resets a table entry called "Drum" to null if I change the record "Cable". This process works, except I get the following error when I do so:
" Write Conflict
This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.
[Save Record] [Copy to Clipboard] [Drop Changes] "
I have set warnings to false but this still comes up.
Does this mean something is wrong with my method of updating the table? If not, is there any way I can stop the warning coming up?
I am new to using sql statement in my codes. I need help in writing a sql statement using one sql statement (sql_1) in another (sql_2)? In the sql statements below, I am trying to replace "query2" with the sql statement (sql 1). Please help
sql 1: SELECT tbl_HourTypeMaping.CustomerCode_HTMP, tbl_HourTypeMaping.HourTypeCode FROM tbl_HourTypeMaping WHERE (((tbl_HourTypeMaping.CustomerCode_HTMP)="htmp_default"));
sql 2: INSERT INTO tbl_HourTypeMaping ( CustomerCode_HTMP, HourTypeCode ) SELECT Query2.CustomerCode_HTMP, tbl_HourType.HourTypeCode FROM tbl_HourType LEFT JOIN Query2 ON tbl_HourType.HourTypeCode = Query2.HourTypeCode WHERE (((Query2.CustomerCode_HTMP) Is Null));
