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.
Any ideas?
Thanks
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?
TQ
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
Any Ideas on this
Regards
Sam
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?
Thanks for any ideas!
Hi all,
Is it possible for one field on a form to write to 2 tables?
e.g. i type in field "Owner" on my form which is sourced to "Owner" in table A but i would also like it to populate "Owner" in Table B.
Hope that makes sense.
Thanks
Mark
When does form data get written to a table?
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.
Thanks,
Mike
Hello, I need help writing a formula in a query
I have a database where I need to be able to pull different prices according to type.
For example, if it is a SH then I will need it to pull a certain price of an item under the SH level
if it is a DD, then I will need to pull a price of the item under the DD level
How can I write this formula in my query as an expression or do I need to write it as SQL dlookup.
If it is the dlookup, how do I write that.
Help!!!
Hi there.
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.
Thanks for your help
(I m using access 97)
Hi,
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
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.CWSubform.Requery
Me.PlanWeight.Value = Me.PlanWeightCalc.Value
End Sub
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.
Hello All,
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?
Thanks to anyone who helps,
Bakerboy
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));
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!
Any help appreciated.
Thanks
Hi,
I've had a customer request to have a larger area to type into a memo field. I've removed the memo field from the form and have added a button to bring up a form with just the memo field on it. A problem has arose from this that now when closing out of the form I get a write conflict error. I've never seen this before and there is only me logged into the database. How can I stop this from occuring?
Thanks
Not being good at codes.
What I attemping to do is edit a email address, upon entering that field.
As of now I right click in the field and then edit the email address.
any ideas
I am an estimator for an industrial and commercial insulation contractor. I have compiled an enormous amount of data over the years but I am still figuring all of my estimates by hand.
For instance, I might need to figure that a 3 pipe will be insulated with 2 thick fiberglass pipe covering. This pipe is outside, so it will need to be jacketed with .016 thick aluminum secured every 4 with sheet metal screws.
The 90 and 45 degree elbows need to be insulated with preformed fiberglass elbows and jacketed with preformed aluminum elbow covers. Every 15 feet there will be a pipe support. This line is a schedule 40 steel pipe and it has valves on it and each valve has a flange at both ends so the insulation for the valves will have to be big enough to fit over the flanges. Where the 3 pipe might require 2 of insulation, a 1 pipe might only require 1-1/2 of insulation. Each size and thickness has its own production figures. This is the same with prices for the types of insulation and jacketing.
I have compiled all of this information into tables. Where before I had to get out my books and look each up, no I go to my tables, find the amount per day, and calculated that by the number of items. I can look up each type of insulation for each type of fitting or valve and I can calculate how much time all of this will take. I can also look up to see what size covering will fit on a 3 flanged valve. I have another table that will tell me how many square feet of jacketing the pipe, fittings and valves will require. I have prices for all of the associated materials.
The same happens when I try to figure equipment or ductwork. We deal with many kinds of insulation and just as many jacketing and finishes.
I bought Access 2007 and have brought my tables in. I want to have a spreadsheet or form that will allow me to link this information together and make the proper calculations. I want to be able to change the pipe sizes or materials from a list and have the calculations adjusted accordingly.
Again, I have all of the figures; I just don't know exactly how to go about getting all of this linked together in one place. I have only dabbled in computer code writing and I really don't know very much about it. That is why I picked Access because I figured it would have about everything in it that I need. I know the layout of the form I need, I just dont know where to go from there.
Any guidance would be appreciated.
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! :)
View 1 Replies View RelatedI searched this one and found that someone else had posted the same problem 2 years ago, but since no one replied to that one... I'm still stumped.
I have a set of subforms, each on a separate tab of the main form and pulling from the same table. I have code triggered on after update so that changes to a control on subform A will make changes to several controls on subform B (specifically, disabling the control and deleting any value). When I switch to subform B, I get a message saying:
"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. Save record, copy to clipboard, drop changes"
Please help!
Hi all,
I have followed the advice on this page of 'how to update stock levels automatically' in access.
http://support.microsoft.com/default.aspx?scid=kb;en-us;252813
I implemented this in the Northwind database and everything was fine. I implemented this in my database (which is very similar to the northwind in structure!) and receive this error:
"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."
I have no idea how this is happening. Can anyone throw some light on this? Any advice will be greatly appreciated.
Regards
the answear probably is here somewhere but as i dont know what the word for a protection where u cant write is in english as i have a swedish access.
i found it when making a macro but who wants loads of macros when u can use VB.
so whats the code for opening a form with writing protection?=)
a have a form with a combo box that displays 3 values (columns)
in a bound form i can display the second value in a text box with the following command
=Forms!MAIN!field_2.COLUMN(1)
but i can't write it to the table
my main table has 3 fields
field_1
field_2
field_3
i have a secondary table that holds some values and is been used as a lookup list to field_2
my secondary table has 3 fields
field_a
field_b
field_c
the field_2 of main table takes value from field_a (the lookup list shows all the fields (a,b,c) and i want in text field lets say field_3 to take the value of field_b with this code
Forms!MAIN!field_2.COLUMN(1)
The problem is that i want the value of Forms!MAIN!field_2.COLUMN(1)
to be written to a field of the main table :( and i cant
Thanks in advance
please help me
:eek:
Hi there!
This is a question that you might scratch your head while reading, but what the hell... :-)
Imagine a form with 4 blank textboxes (name, input1, input2, input3). When you add the value in the textboxes, the value in the table changes as you write. Instead I only want the values in the table to be added once an "OK" button has been pressed.
When the button is pressed I want the current date written in a field called 'date' and the inputs from the 4 textboxes added to fields with the same name...
How is that done???
John
I have a report rptEnvelopes to print envelopes. In the report I have text box called txtRecipientName .
I have a qryAddress with fields notNo, IDCard, Fname, Lname where notNo is a field that takes its value from a combo box called cboSelectEnvelope from a form frmPrinting. I then wrote the following code:
Private Sub Report_Open(Cancel As Integer)
Dim dbLet As Database
Dim rsLet As Recordset
Dim sqlLet, criteriaLet, txtRecipientName As String
Dim stLet as String
Set dbLet = CurrentDb
sqlLet = " Select DISTINCT IDCard FROM qryAddress " & _
"WHERE notNo = " & Forms!frmPrinting!cboSelectEnvelope
Set rsLet = dbLet.OpenRecordset(sqlLet, dbOpenSnapshot)
If Not rsLet.EOF Then
rsLet.MoveLast
stLet = "rptEnvelope"
criteriaLet = "notNo=" & Forms!frmPrinting!cboSelectEnvelopeReports
'This is where I go wrong:
'How to I declare txtRecipientName, on rptEnvelope, to get the value rsLet!Fname&" "& rsLet!Lname
DoCmd.OpenReport stLet, acViewPreview, , criteriaLet
End If
rsLet.CLOSE
NOTE: Private Sub Report_Open is called from the On Open Event of rptEnvelope
Hi folks,
I have a query which queries another query. I need to re-write the whole thing in SQL, but struggling with incorporating the nested query.
here's the main query:
SELECT tblProductSubCategory.SubCatDesc AS Products, qrySummaryStage3.[Early Cash Sales], qrySummaryStage3.[Late Cash Sales], qrySummaryStage3.[Cash Variance], qrySummaryStage3.[Cash Variance %], qrySummaryStage3.[Early Credit Sales], qrySummaryStage3.[Late Credit Sales], qrySummaryStage3.[Credit Variance], qrySummaryStage3.[Credit Variance %], qrySummaryStage3.[Early Total], qrySummaryStage3.[Late Total], qrySummaryStage3.[Variance Total], qrySummaryStage3.[Total Variance %], tblBudgetNew.Amount AS [Late Budget Amount], [Late Total]-[Late Budget Amount] AS [Budget Variance], qrySummaryStage3.[Early Cash Margin %], qrySummaryStage3.[Late Cash Margin %], qrySummaryStage3.[Early Credit Margin %], qrySummaryStage3.[Late Credit Margin %]
FROM (qrySummaryStage3 INNER JOIN tblBudgetNew ON qrySummaryStage3.SubCatID = tblBudgetNew.SubCatID) INNER JOIN tblProductSubCategory ON qrySummaryStage3.SubCatID = tblProductSubCategory.SubCatID
WHERE (((Month([date]))=1) AND ((Year([date]))=2005) AND ((tblBudgetNew.Site)=[Enter Site ID]) AND ((tblBudgetNew.Type)="Monthly Breakdown"));
and here's the nested query , called "qrySummaryStage3":
SELECT tblProductSubCategory.SubCatID, tblReportTemp1.[Early Cash Sales], tblReportTemp1.[Late Cash Sales], [Late Cash Sales]-[Early Cash Sales] AS [Cash Variance], IIf(nz([early cash sales],0)=0 Or [Early Cash Sales]=0,0,[Cash Variance]/[Early Cash Sales]) AS [Cash Variance %], tblReportTemp1.[Early Credit Sales], tblReportTemp1.[Late Credit Sales], [Late Credit Sales]-[Early Credit Sales] AS [Credit Variance], IIf(nz([Early Credit Sales],0)=0 Or [early Credit Sales]=0,0,[Credit Variance]/[Early Credit Sales]) AS [Credit Variance %], [Early Cash Sales]+[Early Credit Sales] AS [Early Total], [Late Cash Sales]+[Late Credit Sales] AS [Late Total], [Late Total]-[Early Total] AS [Variance Total], IIf([Early Total]=0,0,[Variance Total]/[Early Total]) AS [Total Variance %], IIf([Early Cash Sales]=0,0,([Early Cash Sales]-[Early Cash Cost])/[Early Cash Sales]) AS [Early Cash Margin %], IIf([Late Cash Sales]=0,0,([Late Cash Sales]-[Late Cash Cost])/[Late Cash Sales]) AS [Late Cash Margin %], IIf([Early Credit Sales]=0,0,([Early Credit Sales]-[Early Credit Cost])/[Early Credit Sales]) AS [Early Credit Margin %], IIf([Late Credit Sales]=0,0,([Late Credit Sales]-[Late Credit Cost])/[Late Credit Sales]) AS [Late Credit Margin %]
FROM tblReportTemp1 RIGHT JOIN tblProductSubCategory ON tblReportTemp1.ProductSubCatId = tblProductSubCategory.SubCatID;
Thanks in advance!
Hello All,
I have created a form which updates the values in a table when the submit button is clicked. I get the following error when I do this:
" 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?
Thanks to anyone who helps,