Wierd Prob: Cannot Update SQL Server Text-fields For Some Records, But Can For Others

Feb 22, 2005

Hello,

I have a very strange problem:

I have a table linked to an SQL Server table in my MS Access 2000 Application containing 5 text-fields (SQL Server Type, in Access seen as MEMO-fields) and several other fields (in total about 140 fields). Updating any field works fine except for some records. In those records I can only edit the non-text-fields in my application. When I try to edit one of the text-fields I get a error message telling me that another user has edited the record and i have to discard my changes.

The strange thing is that this problem only crops up for some records. (1 in a 100 or so) All other records work fine.

Does anyone know what happens here? And how this can be fixed?

Thanks.
Kaine

View Replies

Update Two Different Tables (fields) From One Text Box

Feb 9, 2006

Hi All,

How do update two different tables (fields) from one text box from a form?

Thanks

View 2 Replies View Related

Update Query Work On Allowzerolength On Text Fields

Sep 30, 2005

Hi,

I am using a update query to update a table from another table. This query however will works only if I set the allowzerolength to yes in the table design otherwise validation error message will appear. This means that I cannot use the "Is Null " anymore because this empty space " " can appear. Is there any a way i can filter out those empty fields in the query design then? Thank you for your help.

View 1 Replies View Related

Queries :: Update Fields By Splitting Text From Other Field

Nov 28, 2014

I have one field called FULL_ADDRESS. From that field I am going to populate two other fields. One called ADD_1 and the other, ADD_2.

In the FULL_ADDRESS field I have the following text :

"BOX 2747 ROCKY MOUNTAIN HOUSE AB T4T 1L7"

I want ADD_1 updated to:

"BOX 2747 ROCKY MOUNTAIN HOUSE AB"

and ADD_2 updated to:

"T4T 1L7"

While I understand how to do an update if I was using a space as a reference, i.e.

Trim(Left([FULL_ADDRESS],Instrrev([FULL_ADDRESS]," ")-1))
Trim(Mid([FULL_ADDRESS],Instrrev([A]," ")+1))

I can't figure out how to skip the first space from the right, and reference to the second occurring space, so that I get "T4T 1L7" to populate the ADD_2 field, and everything to the left of "T4T 1L7" placed in the ADD_1 field.

View 6 Replies View Related

Concatenating Text Fields From Different Records

Jul 13, 2005

We use two different systems in our office that have notebook facilities. One of them stores only a limited number of characters in the note field per record and as such, some notes actually span several records.
The other system stores the whole note in one record.
We want to transfer all the notes from the first system (1 note over several records) to the second system (1 whole note in 1 record).
I have a table with the notes from the first sytem which looks something like
Note1 - Line1 - Text
Note1 - Line2 - Text
Note2 - Line1 - Text
Note3 - Line1 - Text
Note3 - Line2 - Text
Note3 - Line3 - Text

What I want to do is, for each note, combine the separate lines into one record.
I have been able to do this in Excel with some formulas (see attachment) but am wondering if it is possible to do this in an Access Query (i.e. GroupBy the note number and have a calculated field combining the individual lines of the note, or something similar)?

View 8 Replies View Related

Forms :: Text Field Auto Update When Adding New Records

Jul 22, 2014

I have 2 tables Master table (Jobs) containing the primary key ("Job Reference") and 2nd table (Candidates) with the foreign key ("Job Reference")

2 Forms

frmJobs Form to view job details
frmCandidates form to view Candates information

I have placed add new candidate button on the frmJobs form which opens the candidate form in the add mode as a blank form. This is fine but I'm looking to add a record where it picks up the "Job Reference" text field value from the main frmJobs and update it in the "Job Refernce" text field on the frmCandidate when I click add new candidate.

How can I achieve this? I'm fairly new to access/vba

View 7 Replies View Related

New At Access: Update Simlar Text Fields In One Form Based On Another Form

Jun 13, 2006

I am very new at Microsoft Access. I have two forms, A and B, that are filled out by two different users. Form A gets filled out first than form B gets filled out. Each has a name box as well as a birthdate box and a few other similar text fields. However, each form also has a few distinctive fields. I was wondering how I could input the common information in Form A to Form B so the user of Form B does not have to spend time retyping the name and birthdates again. In other words, I want to synchronize the similar records between the two forms. I would like the values to appear in a table as well. I would greatly appreciate it if someone could help me with the visual basic code. Thank You.

View 1 Replies View Related

Forms :: OLE Server Error From List Box Update

Jun 2, 2014

I have a form which uses a list box to select which record to display. The code is all generated by Access during form design. It is a method I have used numerous times inthe past in various database without problem (even in the current database I am developing).But for some reason on this one form I get the following error;-

"The expression After Update you entered as the property setting produced the ollowing error; A problem occurred while database was communication with OLE Server or Active X Control".

View 2 Replies View Related

Wierd Stuff..

Oct 25, 2005

Recently I've been having a problem with Access (2003) that I can't seem to get around. If I click on "NEW" to create a new query, do some editing, then go to close, it prompts me as to whether or not I want to save, which would be ok, if it would let me choose NO!!

I only get OK or Cancel options. I've NEVER had this problem before.
I have not done any updates added any addons. Is there some hidden trick here or some way to get around this cause its really annoying.

TIA,
Tim

View 3 Replies View Related

Wierd Opening Db Problem!

Mar 5, 2008

Hello

I have a database on a shared network drive. If you try to open the db (when another user already has the db open) from the route "my computer" then selecting the drive then selecting the db the message

"Microsoft Access can't find the database file 'M:lah blah blah'
Make sure you have entered the correct file and path name"

However, if you open the db through the access application it opens fine and there are no problems.

Has anyone come accross this before???
I have contacted our IT department who say that they cannot see why this is happening.

Thanks

View 3 Replies View Related

Wierd Date Between Query

Aug 16, 2005

I have a backwards date between query. I have a table with Date1 and Date2 fields. On the form I have a calender control. I want the user to click a date on the calender control and then look at the rows in the table to see if that date is between the Date1 and Date2 fields.

So the query will be (in english) get all rows that CalenderDate1 is between Date1 and Date2

Im stuck. Someone give me direction.

View 11 Replies View Related

Wierd Subform Behavior (Please Help)

Dec 29, 2005

When I open my mainform through an onClick event of a button, the form opens up with the subform on it is disabled/closed -- Just a blank white box shows in place of the subform on my mainform. Here:
http://img426.imageshack.us/img426/4672/shot29sj.th.jpg (http://img426.imageshack.us/my.php?image=shot29sj.jpg)

However, when i go through the objects window and open the mainfrom directly. I am able to enter and update data and the subform shows up and works fine. Here:
http://img420.imageshack.us/img420/5650/shot19ig.th.jpg (http://img420.imageshack.us/my.php?image=shot19ig.jpg)

This is the code attached to the onClick event on the mainMenu

Private Sub New_Project_Click()
On Error GoTo Err_New_Project_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Project Status - Full Details2"

stLinkCriteria = "[projectId]= 0"
DoCmd.OpenForm stDocName, , , stLinkCriteria
[Form_Project Status - Full Details2]![Proj ID].Enabled = False

Exit_New_Project_Click:
Exit Sub

Err_New_Project_Click:
MsgBox Err.Description
Resume Exit_New_Project_Click

End Sub

This is the record source on the mainform:
The projectInfomation table is the record source
SELECT *
FROM projectInformation;

This is the record source on the subform:
The projectStatusCommentary table is the record source

SELECT projectStatusCommentary.projStatusId, projectStatusCommentary.projectId, projectStatusCommentary.statusCommentary, projectStatusCommentary.commentaryDate
FROM projectStatusCommentary;

The link fields are:
projectInformation.projectId
projectStatusCommentary.projectId

The tables are in sybase and my application has an ODBC connection to them.
I can update any of the tables directly without issues.
I have ran the queries independently and updated tables through the query and it works fine also.

I have been pulling my hairs out on this for over 3weeks now. cos i can't seem to find what's wrong.
I am not able to attache the db cos it's very huge. But i have copied the problematic form &subform, to a new db so you may see my design, and record sources.

If you have any suggestions, ideas, or you've come across this kind of issue before, anything...please make my new Year a happy one and help :)
I would really appreciate it!
Have a happy and prosperous new year.

Yours truly,
Frustrated one

View 10 Replies View Related

Wierd Date Picker!!

Oct 12, 2006

Well this is bizzare,
I am using the microsoft date picker in my form. I have made it non editable (i.e. editable=false) when the form loads.

I am making it editable on the click of a button... Seems all so simple huh..

But my problem is that when I click the button, the date picker is displaced at the top of the form..

when I move the slide bar of the form, it again comes back to its original position..

Can anyone explain this?? and how to make it simple?

View 6 Replies View Related

How To Get Full Permission:Add, Delete, Update, Edit In The SQL Server Database

Jul 26, 2007

I wrote the following statements to update a SQL server database:

Dim MyDb As Database
Dim MyRs As Recordset

Set MyDb = DBEngine.Workspaces(0).OpenDatabase("AMD", dbDriverPrompt, False, "ODBC;DATABASE=AMD;DSN=Remote")

Set MyRs = MyDb.OpenRecordset("SELECT AllAttendanceEvents.* FROM AllAttendanceEvents ORDER BY AllAttendanceEvents.EntryTime DESC")

MyRs.MoveFirst

While Not MyRs.EOF
MyVal = MyRs!EntryTime
Debug.Print MyVal
MyRs.Edit
MyRs!Ix = 50099
MyRs.Update
MyRs.MoveNext
Wend

When it execute the red line statement, it generates the error:Cannot update. Database or object is read-only.

This error is generated isnpite that I already checked the boxes: SELECT, INSERT, UPDATE, DELETE in the Permissions page of the tables?

Please advise.

View 3 Replies View Related

Modules & VBA :: Update Data In A Bunch Of Tables Of SQL Server Database

Apr 25, 2014

I need to update data in a bunch of tables of a sql server database. The database has 300 tables which I have linked via odbc. I'm hoping there is a simple way, using vba, to loop through my linked sql server tables and determine which of them are views as opposed to tables.

View 1 Replies View Related

Wierd Slow To Open Problem

May 22, 2007

Hi,

I have a database here that is run by users who use either Access 2002 (XP) or Access 2003.

I have noticed something which to me seems odd and dunno if any of you guys could explain why or how to resolve it?

If one of the 2002 users opens the database it opens fine first time (I use 2002 myself for development) however if a 2003 user opens it it can take 30s+ to open, but then if they close it it will re-open in just a couple of seconds like for the 2002 users.

However if a 2002 user opens it once a 2003 user has just closed it they will then take 30s or so to open it, but then again if they close and re-open it's almost instant.

Is that normal?

Incidentally office 2007 doesn't seem to suffer from this, but it's dead slow at running the database anyway so I'm not going down that route for the forseeable future!

Thanks in advance for any input!

I did try the recommended method of searching this forum using google but it didn't seem to work for me:(

View 1 Replies View Related

Wierd Sorting Situation (excluding Record)

Jun 29, 2006

I'm trying to sort a combo box alphabetically except for one value which is always at the top of the list.

The row source of the combo box is a query. record to remain at the top of the list is static- same value and ID number always. I've fiddled around with a few things, but at the end of the day, i'm just not smart enough! (or its not possible -which i doubt)..
any suggestions?
cheers.

View 2 Replies View Related

Updating Records From 1 Server To Another

May 17, 2006

Hi,

I need some help, I�m trying to update 1 database which is in SQL on one server with data from another database which is on another server There are around 8000 records. It is taking for ever updating the data via MS Access Append Query and Update query. Do you know if there is any other faster way in code, which can be updated? Maybe in a loop doing 100 records at a time or any other suggestions.

Reason I need a faster way is the data on the SQL database is needs to be updated is linked to a website with data in , while the data is being updated the website crashes.

I would really appreciate your help.

Thanks

View 1 Replies View Related

1 Prob

Jul 12, 2006

in 1 table there are are 3 feilds
folwer_name
no_of_flowers
total_cost

another table (2 fields)
name
price

eg:
name price
rose 15
tulip 20

Now I want o make a form
with 3 fields

folwer_name
no_of_flowers
total_cost

eg: if some one select 'flower_name' 'rose' and quantitly '10' then total_cost = 150

How can I do this ? using query or anything else plz gimme detals

View 3 Replies View Related

Form Prob!

Jul 21, 2005

Hi, i have created two tables, containing the same fields. One table if for '1 day tests' and the other is for '2 day tests'. I need to keep these two in seperate tables.

At the moment i have two seperate forms, one for each type of test, but as the fields are all the same, i was wondering if i had a combo box, so the user could choose whether they wanted a one day or two day test and filled in the rest of the fields, the record would then be stored in the appropriate table. Ie 2 day test table, if the chose 2 day test on the combo box

Thanx

View 2 Replies View Related

Date Prob!

Sep 15, 2005

Hi all,

I have two date and time fields which format are like: 15/09/05 09:48:43,I have another field which calculates the time difference between these two fields. I am doing a calculation on the time difference field and want the time to be displayed as for example 03:00, but at the moment it just shows 3:00.

This is the control source which has some formatting in it:

=Int(CSng((([txtDateDel]-[txtdateRO])-[closed])*24)) & ":" & Format(([txtDateDel]-[txtdateRO]),"nn")

I have tried adjusting this but am having no luck.

I would be grateful of any help,

Thanks :)

View 1 Replies View Related

Query Prob - Please HELP

Sep 30, 2005

How do I use a wildcard in a query, see attached .mdb

Table "myDATA"
Sam and Zeb have serial number 23a4224A and 23a4224 in their records.

I want to locate a serial number(s) in multiple fields of a table, based on the users input with a wildcard.

query "Serial Number query"
Enter Serial Number: the user enters: 23a4224*

What am I doing wrong?

Thanks

View 4 Replies View Related

Could Someone Look At The Prob With Form On This DB?

Sep 28, 2005

My combo box problem is still at large!

I've tried every way i can, and i cant get the rowsource query right.

If someone could look at this and post a fix, so that when one uses the "training record" form it allows you only to select course dates that are already in the tbl_course_dates table.

Thanks so much

http://www.suicidenotesmusic.net/nonworkingcombos.mdb

View 5 Replies View Related

Prob. With Dates

Dec 7, 2005

:( I changed my input mask in my form to 99/99/0000;0;_, but my form dates still look like 1/1 /2005. I want the 0's to be visible. Please help.

View 1 Replies View Related

Prob With Many2many Dbase..

Dec 27, 2006

I'm trying to create a little dbase for personal keeping track of a number of companies I deal with, so that i store their details and also the products they distribute.
In more detail, I want to have 'fixed' product categories, which will be assigned to supplier, and each category will include the product models I have set, including their details (such as price, manufacturer, country of origin, etc).

My point is to be able to create several customised reports and listing:
1. All product models I have in a category including their details.
2. Report per supplier to view their details and the products they distribute
3. Certain product models in a list with their local distributor (companies I deal with).

I assume it involves a couple of many2many relationships, however I have created a file in MS Access but never managed to have it work properly.

Any1 have an idea, or template maybe?

Regards ;)

View 2 Replies View Related

Help - Deleting Relationships - Cause A Prob?

Jan 4, 2008

I want to delete a relationship temporarily so that I can delete a record. After the record is deleted I will restore the relationship immediately. But I am concerned that this will ruin any queries I have already established or ruin my other records.
Could you let me know if this is possible without making a mess of things that have already been established.

Thanks
Carrie

View 11 Replies View Related