You are the only people can help me .my problem is with a ict project and as you can see i have a database as in the picture .want to do is that when i enter a value in the dvd id in the rent table i also want the dvd Title to be automatically updated with the correspondong value . Hope you can save the day thnx in advance.
View RepliesI am trying to remove random characters from a field. The field [assycode] contains a string similar to say, FGEJBF1 or ABFGYRUKC I want to remove any occurrence of "F1" normally at the end of the string but not always at the end. I used: Like "*f1*" to find the correct records, that worked fine, I then used [Assycode]-" f1" in the update to box, It wants to update 146 records I click ok then it says It couldn't due to a type conversion error. Just messing around I tried adding "F1" to these records using [Assycode]+" f1" and it worked fine. Can anyone point me in the right direction?
Thanks in advance
Wayne
hello..
I have this problem that when I update the value in one of my tables, MS Access hangs up. I don't know why. I dont know if it's because of the updating or because of something else.. Any help would be greatly appreciated.. Thanks in advance! ;)
here the code that's executed when the approve button is clicked..
Private Sub Command43_Click()
Answer = MsgBox("Are you sure you want to approve this timesheet? Note that only projects approved by respective project managers will be approved.", vbYesNo)
'if cancelled
If Answer = vbNo Then
Else
Dim rs As Object
Dim x As Integer 'will be used as a flag for do while loop
x = 1 'initialize flag
Set rs = Me.Recordset.Clone
rs.FindFirst "[weekending] = #" & Format(Me![weekending], "mm/dd/yyyy") & "#"
'finding the first record wich matches the weekending doesn't give the right record
'right away. it may give a record with the right weekending but different eno. that is
'not supposed to happen. so once a weekending match is found, check if the eno also
'matches. this do..while loop will do the trick =)
Do While x < 18
If rs!eno = Forms!TimesheetDetailsFrm!eno And rs!statusPM = "approved" Then
rs.Edit
rs!status = "approved"
rs.Update
x = x + 1
End If
rs.FindNext "[weekending] = #" & Format(Me![weekending], "mm/dd/yyyy") & "#"
Loop
'close this form and refresh the 'timesheets for approval' form
Answer = MsgBox("You have successfully approved the timesheet. Close this window to continue viewing other submitted timesheets.")
Forms!SupervisorFrm.Requery
End If
End Sub
Thanks again..
ps. How do I mark a post as 'resolved'? Coz I have a previous post which has already been solved.. Thanks to all of you guys. ;)
Used to use these forums quite regularly and always found people to be of help, even started chipping in occasionally with the odd pearl myself :rolleyes:
Anyhow, having forgotten my username i'm back under a different guise and have a problem that i'd like some help with.
My company has a website running asp (not .NET) with an sql database backend. Basically we want a localised system to deal with order processing with daily updates to customer, order tables etc. I started to build something in VB and SQL but time is critical and once I work out this problem I can churn something out in access double quick time.
I can get the ODBC connection open to the sql database and import the data to Access 2007 table (ie dbo_tblOrders), but I then need to find/write some code to fetch extra records daily and append them to the EXISTING tables. At present data is fetched and added to a new table. Is it merge queries I need? If so how, and can I set it to only fetch new records?
I have two tables that are identical in format. They contain different primary keys. I want to combine them into one table. I have tried to set up a table with all the primary keys of both tables and do an update. I keep getting invalid argument. The sql statement is valid. The problem appears to be the field with OEL object. In access 97 I have know problem. Does any one know how to solve this problem? The table in 2003 is too big to convert to access 97.
View 1 Replies View RelatedI am trying to update the tables of 1 access database from another access database. Does anyone know the call function or how would I go about doing that?
Thanks
I have a simple table "Line" with Columns Key, OrderNumb, Panels, Gates, Posts, Color, Memo, Comp. A user enters the information into the powder.asp page. It is then added to the database. The Comp Column is populated NO by a hidden input type. The data is then displayed on a page in a table and the last column has a submit button. When the user Presses that button I would like it to update that record in the database to change Comp to YES. Some code snipits are below. The first is where the data is presented and where the submit button is. It then will go to the next snippet that hypotheticly will change just the Comp on that record to YES. I can't get it to work. Thanks Code:<form method="POST" action="powder_comp.asp"> <tr> <td valign="top" align="left"><%= rs("OrderNumb") %></font></td> <td valign="top" align="left"><%= rs("Panels") %></font></td> <td valign="top" align="left"><%= rs("Gates") %></font></td> <td valign="top" align="left"><%= rs("Posts") %></font></td> <td valign="top" align="left"><%= rs("Color") %></font></td> <td valign="top" align="left"><%= rs("Memo") %></font></td><td valign="top" align="left"><input type=hidden name="OrderNumb" value="<%=rs("OrderNumb")%>"><input type="submit" value="submit"></form></font></td>
Code:<%OrderNumb=request.form("OrderNumb")accessdb="Powder" cn="DRIVER={Microsoft Access Driver (*.mdb)};"cn=cn & "DBQ=" & server.mappath(accessdb)Set rs = Server.CreateObject("ADODB.Recordset")sql = "SELECT * FROM Line Where OrderNumb=OrderNumb ;"rs.CursorType = 2rs.LockType = 3rs.Open sql, cnrs.Updatesql = "UPDATE Line Set Comp=Yes"rs.CloseSet rs = NothingSet cn = Nothing%><META HTTP-EQUIV="Refresh" CONTENT="0; URL=powder.asp">
I'm still very new to this so please go easy on me.
Hi Guys
Help needed on probably a newbie issue.
I am trying to update a field in my DB called USER_TYPE this can be either a 0 or 1. below is code I am trying to use... stop s******ing lolol
Code:SQL = "Update CustRecords SET CUST_TYPE=1 WHERE id="&id
Help truly needed!!
Ian
I have a database it contains data more than 4 million line items but while i try to run macro or any query the database reaches the maximum limit of 2 gb , then the particular query not functioning and then i need to compact and repair. After compact and repair also the same issue is continuing.
View 1 Replies View Relatedfirst i'm sorry on my english.
I have in my work some computers with network.
I want to send mail to one computer, the mail is like form in HTML
have 3 things:
1. textbox
1 select
1 submit
is it possible to get the mail and to write in the mail and send it (by submit button) then to update the access db.
I have no idea.
if there is an easy way to do it, I will happy to hear.
[I think to do it with an asp page in each computer that the HTML send the items to the ASP page (by request.get) then the asp page update the db].
Hi
1. I need to know how to update the field list of the query after
adding a new field into the table and the query?
2. I need to know how to update the records table after adding
calculated filed?
I've found myself having to start using Access due to record limits. My reports come to me in Excel and go out in Excel, but by sheer quantity I need to store records in Access.
My problem is in an Update Query:
I import records into a 'DoNotDelete' table and then run an update query to the 'CallsEntered' table. After that I run a Delete query to clear the 'DoNotDelete' table for next time.
The problem is that 'CallsEntered' table is updating the 'DoNotDelete' table.
I know this is simple, but I've done what the "manuals" say to (as far as I can tell). Your time is appreciated.
UPDATE DoNotDelete INNER JOIN CallsEntered ON DoNotDelete.[Work Order Nbr] = CallsEntered.[Work Order Nbr] SET CallsEntered.[Wo Status] = CallsEntered![Wo Status], DoNotDelete.[Date CheckIn] = CallsEntered![Date CheckIn], DoNotDelete.[Assigned Installer] = CallsEntered![Assigned Installer];
Hi,
I'm new to access (wiz at excel) and I'm having a problem updating some records. I have table named "Updated 01-22-07". Within that file I'm trying to updated the Shipper_Name field. The file I'm looking to update it from is called "Party City Offical Vendor List 01-22-07".
The common thread in both files is the Vendor #. Where the vendor # is the same I would like the Shipper_Name field in "Updated 01-22-07" to up to the data in the Vendor Name fileld of table "Party City Offical Vendor List of 01-22-07".
I have banged around several queries and can't seem to get it to work. Any thoughts?
Thank in advance for your help.
Eddie.
I'm new to access and I'm trying to learn the functionality by creating a relational DB for US zip codes from an excel document. I've imported everything and I have the tables setup. Is there a way to run a MYSQL query like this:
UPDATE tbllocation, tblzipcode SET tbllocation.ZipID = tblzipcode.ZipID WHERE tbllocation.ZipID = tblzipcode.ZipCode;
in access 2007?
Thanks
I have summarized a table(a) of 589 records into a table of 119 records(b).
I am trying to calculate a factor in (a) as a percentage of the total in (b) for each record in (a).
(a) can have multiple records for each corresponding, summarized record in (b).
I do this by dividing each of the many records in (a) by a summarized record in (b) with a common field in both.
Basically, I am dividing
Round(([(a)]![netchrg] / [(b)]![netchrg]),8)
All I get is 0.00000000
I have checked my join relationships.
I started by checking the 2nd bubble. It did not work. Eventually, I have tried selecting all three bubbles on the joins and that does not get me any different values.
I would have thought that the 2nd bubble would have given me all of (a) and match it to the the records is (b) for the calculation.
I have even tried eliminating the joins completely but I still get a 0 value.
I have verified that both tables have values in the netchrg field.
Suggestions?
Thanks.
I have a form that has 3 fields each representing a different table. I want the sum of 2 of the fields to update the third field. The third table has an existing value in it, but I need it to change when the formula is executed. I have a formula embedded in the form and can make the formula work, but it doesn't update my table. How can I make it do this? :confused:
View 1 Replies View RelatedI have an asp.net application where the user can add a new client name and password to a database, but I want to do a check to make sure that client name is not already in there. I was working with and INSERT INTO statement this here:
INSERT INTO [clients] ([clientName], [passWord]) VALUES (@clientName, @passWord) WHERE NOT EXISTS (SELECT [clients].[clientName] FROM [clients] WHERE [clients].[clientName] = clientName)
But I guess you can't use a WHERE clause with an INSERT statement.. So I am trying to use an UPDATE statement. This here:
UPDATE clients SET clientName = @clientName, passWord = @passWord WHERE NOT EXISTS (SELECT [clients].[clientName] FROM [clients] WHERE [clients].[clientName] = @test)
When I run it in access, it doesnt add a new row if I try to add a client name that is already in there, but if I try to add one that isn't it tells me it's going to UPDATE 13 rows which would be all the rows in there. Anybody have any ideas how I can do this?
is anyone has idea about how to undo update for subform?
i have a parent form(linked to A table) and a subform (linked to another table). the parent table has two button-save and cancel. i was wondering how the subform cancel update if the parent form's cancel button has been clicked? the code Me.undo will undo the parent form's record only, since the subform record has been saved into access.
Can someone tell me how to get that inline frame inside the message for the code so it doesnt take up too much spaces.
I could not get the MS Access database to update from ASP when a user update the moditfied information. When clicking the 'save' button, it suppose to update the changes. Also, the 'delete' button is suppose to delete a record but it doesn't delete. It just reload the page with the original information and no error message is produced. I checked the permission of the database to 'read/write' but didnt seem to have any affect. Then I checked the code to match up to the field name in the database. I am moditfing the code that was used for same concept except this will have long list of people.
[CODE]<%
dim RstSave 'As ADODB.Recordset
dim frmItem 'As Request.Form.Item
'*** Debug Line ***'Response.Write Request.Form("cmdTopic")
select case Request.Form("cmdTopic")
'***** Paragraph Text *****
case "Edit Paragraph Text"
if len(Request.Form("cmdSaveRec")) > 0 then
Set RstSave = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT * FROM tblText ORDER BY ID;"
RstSave.Open SQL, Conn, 3, 2
if RstSave.BOF and RstSave.EOF then
else
RstSave.MoveFirst
'Step thru Recordset
'*** Debug Line ***'Response.Write "Starting Recordset<BR>"
do While not RstSave.EOF
'Check each Item returned by the Form
'*** Debug Line ***'Response.Write "ID: " & RstSave("ID") & "<BR>"
for each frmItem in Request.Form
'Is it a Record?
'*** Debug Line ***'Response.Write "IsNumeric Results: " & Isnumeric(mid(frmItem,6)) & "<BR>"
if isnumeric(mid(frmItem,6)) then
'Does it Match the Current Record?
'*** Debug Line ***'Response.Write "ID: " & RstSave("ID") & "-" & mid(frmItem,6) & " = 0 ?: " & (RstSave("ID") - Cint(mid(frmItem,6))) = 0 & " <BR>"
if RstSave("ID") = Cint(mid(frmItem,6)) then
'*** Debug Line ***'Response.Write "Saving Record with ID: " & RstSave("ID") & " | " & Request.Form(frmitem) & " | " & Clng(Request.Form("txtHT" & mid(frmitem,6))) & "<BR>"
'Write the Form Field to the Recordset
RstSave("Text") = Request.Form("txtID" & mid(frmitem,6))
RstSave.Update
end if
end if
next
RstSave.MoveNext
loop
end if
RstSave.Close
set RstSave = Nothing
end if
'***** Patient Services *****
case "Edit Patient Services"
if len(Request.Form("cmdSaveRec")) > 0 then
Set RstSave = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT * FROM tblPatientServ ORDER BY ID;"
RstSave.Open SQL, Conn, 3, 2
if RstSave.BOF and RstSave.EOF then
else
RstSave.MoveFirst
'Step thru Recordset
'*** Debug Line ***'Response.Write "Starting Recordset<BR>"
do While not RstSave.EOF
'Check each Item returned by the Form
'*** Debug Line ***'Response.Write "ID: " & RstSave("ID") & "<BR>"
for each frmItem in Request.Form
'Is it a Record?
'*** Debug Line ***'Response.Write "IsNumeric Results: " & Isnumeric(mid(frmItem,10)) & "<BR>"
if isnumeric(mid(frmItem,10)) then
'Does it Match the Current Record?
'*** Debug Line ***'Response.Write "ID: " & RstSave("ID") & "-" & mid(frmItem,6) & " = 0 ?: " & (RstSave("ID") - Cint(mid(frmItem,6))) = 0 & " <BR>"
if RstSave("ID") = Cint(mid(frmItem,10)) then
'*** Debug Line ***'Response.Write "Saving Record with ID: " & RstSave("ID") & " | " & Request.Form(frmitem) & " | " & Clng(Request.Form("txtHT" & mid(frmitem,6))) & "<BR>"
'Write the Form Field to the Recordset
RstSave("Service") = trim(Request.Form("txtServID" & mid(frmitem,10)))
RstSave("Description") = trim(Request.Form("txtID" & mid(frmitem,10)))
RstSave.Update
end if
end if
next
RstSave.MoveNext
loop
end if
RstSave.Close
set RstSave = Nothing
end if
'***** Physician Directory *****
case "Edit Physician Directory"
if len(Request.Form("cmdSaveRec")) > 0 then
Set RstSave = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT * FROM tblDoctor ORDER BY ID;"
RstSave.Open SQL, Conn, 3, 2
if RstSave.BOF and RstSave.EOF then
else
RstSave.MoveFirst
'Step thru Recordset
'*** Debug Line ***'Response.Write "Starting Recordset<BR>"
do While not RstSave.EOF
'Check each Item returned by the Form
'*** Debug Line ***'Response.Write "ID: " & RstSave("ID") & "<BR>"
for each frmItem in Request.Form
'Is it a Record?
'*** Debug Line ***'Response.Write "IsNumeric Results: " & Isnumeric(mid(frmItem,13)) & "<BR>"
if isnumeric(mid(frmItem,13)) then
'Does it Match the Current Record?
'*** Debug Line ***'Response.Write "ID: " & RstSave("ID") & "-" & mid(frmItem,13) & " = 0 ?: " & (RstSave("ID") - Cint(mid(frmItem,13))) = 0 & " <BR>"
if RstSave("ID") = Cint(mid(frmItem,13)) then
'*** Debug Line ***'Response.Write "Saving Record with ID: " & RstSave("ID") & " | " & Request.Form(frmitem) & " | " & Clng(Request.Form("txtHT" & mid(frmitem,13))) & "<BR>"
'Write the Form Field to the Recordset
RstSave("LName") = trim(Request.Form("txtLName" & mid(frmitem,13)))
RstSave("FName") = trim(Request.Form("txtFName" & mid(frmitem,13)))
RstSave("Title") = trim(Request.Form("txtTitle" & mid(frmitem,13)))
RstSave("DEPT") = trim(Request.Form("txtDEPT" & mid(frmitem,13)))
RstSave("Phone") = trim(Request.Form("txtPhone" & mid(frmitem,13)))
RstSave.Update
end if
end if
next
RstSave.MoveNext
loop
end if
RstSave.Close
set RstSave = Nothing
end if
end select
%>[CODE]
I am relatively new with access and this is probably pretty easy for the pros, but here it is.
I have a table with 4 fields. Field A, Field B, Field C and Field D.
I have a form that the user will enter Data into A, B, C from combo boxes or List boxes. A, B, C fields in the table are update.
In Field D, as the user is entering data into A, B, & C, I would like to concatenate A,B,C ( I have an expression that all ready does all that, so don't worry about that) and write back out to the D field in my table.
The concatenation expression works great, but I just can not update the D field on the table.
I know someone will figure this out. Thanks for your help.
Hi all,
I am generating a multi source concatenated code with an update query in Microsoft Access. I am wondering if there is a way to cut the update to only new entries rather than updating all records each time the query is run.
Any helpers for this problem?
Hi, I have a very simple database with 2 tables:
table: animals
1. Cat NA
2. Dog NA
3. Rat NA
4. Bat NA
5. Garfield NA
table: felines
1. Cat
2. Garfield
3. Simba
what I want to do is:
UPDATE animals.field3 (which starts off as NA's) to "meow" if animals.field2 matches any item in felines.field2.
it is a recursive operation and I'm not sure how to do it
here is what the animals table should look like if the UPDATE query runs as it should
1. Cat Meow
2. Dog NA
3. Rat NA
4. Bat NA
5. Garfield Meow
Any help would be great! I prefer SQL view of the UPDATE query if anyone knows how to do this. Note: I like cats and dogs equally, this is just to learn how to do such an update.
Sasi
Hi,
im new this forum and this is my first post, ive tried searching but if anyone could direct me in the right direction that will be great,
bassically i have created a basic Ms access databse for a hotel reservation system, with queries, reports etc. The database works fine.
I need to create an website which will connect to the database so that i can enter,view, query records from the website.
I have very little or no programming knowledge so whats the best solution. I was going to make the website in either dreamveaver or frontpage but ive read that these static websites cant connect to an database.
im sorry if this question problably has been asked a million times before but i need some advise as im a beginer.
any help much appreciated.
Thanks
From Excel 2007 to Access 2007 I want to keep my Access database sync with my Excel SpreadSheet. I not to often change Excel but when I do Access will be updated.
Error #: -2147217887
"Field cannot be updated."
My connection string works fine and recordset is up and running!In my Excel file:
Code:
rs.MoveFirst
For k = 1 To Sheets("2013").Cells(Rows.Count, "A").End(xlUp).Row - 4
If rs!Index <> k Or rs!total <> Sheets("2013").Cells(k + 4, 5).Value Then
rs!Index = Sheets("2013").Cells(k + 4, 1).Value
rs!DatePaid = Sheets("2013").Cells(k + 4, 2).Value
rs!WhatPaid = Sheets("2013").Cells(k + 4, 3).Value
[code]...
I have a database that deals with rented movies. I want a means in Access to enable the stock values of the movie to be updated whenever there is an order for a certain film and whenever its returned. I am not sure if an update query or a macro is needed for this to work and how each would work.
View 3 Replies View RelatedHi ALL,
I have 2 tables, trying to update one table filed by using max(onefiled) from other table.
my qyery is as follows:
my tables are tblSubFlowForecast and tblPickCalendar
I have to update one field in tblSubFlowForecast by taking max(onefiled) from tblPickCalendar.
UPDATE tblSubFlowForecast INNER JOIN tblPickCalendar ON tblSubFlowForecast.Delivery=tblPickCalendar.Delive ry SET tblSubFlowForecast.[Latest Replen Date] = ( SELECT max(Pick) FROM tblPickCalendar WHERE Type="Replen");
I am getting error saying that "Operation must be an updateable query" :(
I have also tried in other way:
UPDATE tblSubFlowForecast SET [Latest Replen Date] = (SELECT max(Pick) FROM tblPickCalendar WHERE tblSubFlowForecast.Delivery=tblPickCalendar.Delive ry AND tblPickCalendar.Type="Replen" ) FROM
tblSubFlowForecast,tblPickCalendar
This is giving me syntax error (missing operator) :(
Can any body tell me why it is giving error?
Thanks in advance