I am using and UPDATE Sql statement to update a firld in a form to a table. However, I get the message updating 140 records etc. How can I get the SQL/Macro to simply update teh record I am currently working on?
Beeky
Combo box on continuous form should have the control source listed as the field on the form that will be updated. The Row Source, however, is a query that includes 2 things: the field on the form that will be updated (this part will be nonvisible in your form) and the table/query of selections you want to show up in the combo box (visible). Then ensure the Bound column is set to 1 and the Column count is set to 2 with Column Widths as 0";1"
View 1 Replies View RelatedI am OK creating a SQL query to update a table in a second database from a look-up table in the second database, and I can create a query to update a table in the current database from a look-up table in the current database - simple.
However, I am trying to create an Update Query to update a table in a second database from a look-up table in the current database.
(and I want to avoid copying the look-up table to the second database.)
Does anyone know how to do this?
i'm getting this message when i try and change a record in a table via a recordset... but i am using CursorType = adLockOptimistic which i thought let you make changes to the table
here is my code so far
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.CursorType = adLockOptimistic
rst.Open "TBL_TmpSubmission", CurrentProject.Connection
If rst.RecordCount > 0 Then
Do While Not rst.EOF
MsgBox rst!PropertyType, vbOKOnly, "debug"
If DCount("[PropertyType]", "[TBL_PropertyType]", "PropertyType = '" & rst!PropertyType & "'") <> 1 Then
rst!PropertyType = DLookup("[PropertyType]", "[TBL_PropertyType]", "IDPropertyType = " & rst!PropertyType)
MsgBox "property changed", vbOKOnly, "debug"
Else
MsgBox "good property", vbOKOnly, "debug"
End If
rst.MoveNext
Loop
End If
rst.Close
am i using the wrong combination or cursor and lock type here? reading the help it seems i should be able to make changes to the table.
I have a form named 'Race'. It uses table 'Tasks'. I want to click a command button and have the 'Completed Date' field updated to the current date.
Can anyone tell me how to code the command button?
Thanks!
Sunny
I'm having difficulties creating a sql statement which updates data in another Access database from a current database.
I'm able to do an insert and delete statement just perfectly, but not an Update. :confused:
For example, here is a dummy sample (which does not work):
Update tbl_test1 IN '\C:Tempabc.mdb', tbl_test2
set tbl_test1.Name = tbl_test2.Name
Where tbl_test1.ID = tbl_test2.ID
I tried a couple of variations, however, I keep getting an error.
Also, I don't want to create a database link (due to the sheer complexity of my project, which I'll spare you the details..)
Does anyone know how to do this? I searched the forums and came up with zilch! =(.. Help??!
Hi I have a forrm (Orders) , with a subform (Order Details)
Depending on a selection of a list field it makes certain field visible in the subform.
If I go the next record and select the list field it updates fields in the subform ( Visible ( True or False )
But if I go back to the previousl record it doesnt update the fields that are relavent to the option picked in the List Filed ( Which I have set to after update ). It works fine if I re-select the option in the list field
How do I set it to update the subform field automatically as I scroll through records?
I tried OnCurrent property but I dont think this is the correct one.
please help
I am creating a database that tracks current projects for my team at work.
Some projects are only due once (e.g., mailed brochures due on 1/1/14) and some are due at scheduled intervals (e.g., status report due monthly, quarterly, etc.)
Ultimately, I'm hoping that my end result will allow us to click on a form and look at what everybody has due that day, in the next 7 days, and so forth.
I need a way to dynamically store a particular value in "field_2" of the CURRENT record depending on whether or not the value of "field_1" of the CURRENT record is identical to the value of "field_1" of the PREVIOUS record within the same table. The table is sorted on "field_1".
So, if the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is also "ABC", then store a value of "PPP" in "field_2" of the current record. IF on the other hand, the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is "XYZ", then store a value of "WWW" in "field_2" of the current record.
I have a report that will use these results to count only the number of records that have a "WWW" in "field_2".
Is this doable, maybe in a query somehow?
I should add that whatever the solution, it needs to be compatible with Access 2000.
I have two forms both with separate tables
(1) Register and
(2) Payments.
One of the common denominators between them is the URN which is auto-populated as it is an auto number field. My issue is that when I want to add a new record to the payments table using the forms (I can get to the payments form via the register form), I want to be able to identify the record that I am currently viewing within the register and auto populate the URN field with the same number. This is what I have done so far,
Option Compare Database
Option Explicit
Private Sub AttachPaymentDetails()
Call PerformInsert("tblFinancialBudget", "frmFinancialBudget")
End Sub
[code]....
I'm trying to make a form that shows what the last record was next to the empty space where you enter a new record.
This is so the user knows that what they are entering is roughly in line with what has come before.
So for example if I was recording temperature every May, I would like a form that has a field called temperature and next to that field I would like to see last year's temperature.
Records:
Date | Temp
2014 | 20.5
2013 | 18.5
2012 | 19.0
2011 | 22.7
2010 | 15.2
So when I enter the record for 2015 I have a box that says: Temp and next to that box is "Last year was 20.5" or something like that.
I'm struggling with a query to evaluate current and prior record data. I have a query producing 5000 records. I need to group records by Case ID and compare current date record to previous date record to determine if a team and worker name has changed during the year. I need to count how many times a cases is transferred to and out of a particular team from the beggining of the year. i.e., On 1/1/2014-Team1 has an inventory of 500 cases.
During the month 25 cases are transferred into Team1 and 15 cases are transferred out of Team1. So on 2/1/2014 Team1 begins with an inventory of 510 cases. Throughout the year cases come and go from and to Team1 each month so need to figure out how to create a query to count each change. See attachment displaying how the data is listed and how I invision it to work with the In/out column counts.
Specifically, at the begginig of the year (1/1/14) for case ID 1003 you can see it belongs to Mary in Team1 for January and February. Then in March the case is transferred out and went to Joe in Team 3. So for Mary a "-1" is recorded as a negative count for that Case ID. If later in the year the case is transferred back to Mary a "+1" would be recorded. Respectively evaluated for each of the 5000 records to get a total count for each of the teams by Case ID throughout the year.
I've started with DLookup, tried comparing current month to previous month using DMax. It works as long as I only select one case ID used in a separate query but if I use the whole subset (5000 records) it fails. I can't figure out how to group each set of case IDs and then apply the query.
My attempted query: DLookUp("[Team]","[Team_qry]","[Team_qry]![Date]=#" & CStr(DMax("[Date]","[Team_qry]","[Team_qry]![Date] <#" & CStr([Date]) & "#")) & "#")
In my query, i want to automatically display the value of another field from the previous record in a field in my current record. i.e.
Name Value Previous
Record 1 1
Record 2 2 1
Record 3 7 2
Record 4 1 7
Is it possible to create some kind of simple expression to refer to data in another record?
Cheers,
Ben
I need to write a query which populates an empty field in the current record with information from a specific field in the next sequential record. Any ideas?:confused:
View 3 Replies View RelatedI have a query to bring in values, I need to select 2 rows of data but the criteria is as such:
x= starting value on form
now the row of data must match the following criteria previous row to current row(ref temp)<=x And Current row(ref temp)>xnext row to current row(ref temp)>=x And Current row(ref temp)<x
I have dealt with SQL before but how to do the above.The isolated 2 rows of data will then go into unbound boxes on a form from which I will do intercept and gradient calculations.
Hi all,
Another problem bothering me. Wish someone can help me to solve it. I am doing the 'Edit' Option right now ... As a user friendly propuse, I had try to use a combo box to let user to choose my employee record with all the detail on but the problem is the program 'JUST' list the record they can not go to the current record!! When i try to edit my record, the program is not allow (for sure because they can't have a duplicate record on it). The reason is because the record just stay in the FIRST record but can not go to the current record. I do believe is the problem of i using the way of listing the record as below:
Me.EmployeeName = Me.EmployeeName.Column(0)
Me.IC = Me.EmployeeName.Column(1)
Me.Nationality = Me.EmployeeName.Column(2)
Me.Race = Me.EmployeeName.Column(3)
Me.Sex = Me.EmployeeName.Column(4)
Me.FMU = Me.EmployeeName.Column(5)
Me.Position = Me.EmployeeName.Column(6)
Me.SectionField = Me.EmployeeName.Column(7)
Me.DOEmploment = Me.EmployeeName.Column(8)
Me.DOResign = Me.EmployeeName.Column(9)
Me.Remarks = Me.EmployeeName.Column(10)
So, Could someone help me to solve it like when i can use the combo box to select the Employee record with the details are on as the same time can go to current record... Thanks alot!!!
i do have the confident if i can go to the current record then my edit option can be working properly.
P/S: I had attach the picture... showing my program problem (with the red circle)
I have a table called tblFinishedGoods. There are 3 fields in the table:Serial Number, Model Number and Location.
I would like to have a form that will take input from my barcode reader and input the data for the 3 fields, if the serial number is not already in the table.(I have this already working).
But now I would like the same form to also check the table for any serial number that is scanned in...and if it is in the table already, have it bring up the model number and location for that record.
I'm thinking maybe there is something I can do with Serial Number afterupdate. Basically I'm looking for a way to not require the user to input anything via a mouse or keyboard.
If the 1 form could accept the 3 scans:Serial Number,Model Number and Location, and either 1)enter a new record if that serial number isnt in the table or 2)find that the serial number already exists and overwrite the model number and location with whatever the next 2 scans are.
Thanks
hello,
I have faxing setup through the XP wizard, I setup my report in Access to use the Fax as specific printer. So When I select a record, it generates the Fax wizard which then prompts for the senders name Fax # etc. and away it goes.
that is ok but It would be nice if I could use the Fax number from the record I am sending, either current record or all. I guess the main trick is how would I pass the fax number to the Fax Printer interface?
Is it possible? I've read a few articles on Microsoft Fax for workgroups to work with Outlook, Outlook help says to go to Office update and download it but I can't find it there? Also do the newer versions of Outlook/Access have a better means of doing this? I am running office 2000, but I can get 2002 installed if that is the case.
thanks
Ziggy
I have a query that pulls all orders out of 3 tables that fit specific criteria.
tblLotInfo.WOSD = Between ([tblLotInfo].[WOSD])<=Date() And [Forms]![frmRaisedPanelLots].[EndingDate]
tblDelivery.Status = <>"On Floor" And <>"Floor Hold" And <>"On Floor LV Losee" And <>"On Floor LV N. 5th" And <>"On Floor ONT Cucamonga" And <>"On Floor ONT Locust" And <>"Shipped" And <>"Installed" And <>"Invoiced" And <>"Completed" And <>"Cancelled" And <>"Returned to Floor LV Losee" And <>"RETURNED TO FLOOR N. 5th" And <>"Returned to Floor ONT Cucamonga" And <>"Returned to Floor ONT Locust"
tblLotInfo.DoorStyle = Like "*eagle*" Or Like "*H/E*" Or Like "*RP*" Or Like "*F/E*" Or Like "*CC-23R*" Or Like "*AR-756*" Or Like "*Deco*"
This query works fine through 12/29/05. Anything date entered after that gives me "NO CURRENT RECORD". I don't know why. We have orders through Feb of next year scheduled. Any help would be greatly appreciated!!
I need help to get current record with different ids:
Dim strDocName As String
Dim strWhere As String
strDocName = "16kanaler"
strWhere = "[skjema_16_gr1_ID]=" & Me!skjema_16_gr1_ID
DoCmd.OpenReport strDocName, acViewPreview, , strWhere
How can I use more Ids in the wherecondition? I want to get the current record for skjema_16_gr1_ID and skjema_16_gr2_ID, not only the first.
Please help me...
Håvar
Hi, I am a complete newbie to Access. I want to be able to double click on the field in the table, form or a query and have, as a result, another form pop-up with all the data from that record displayed. I have tried macros, but the doubleclick property only has things like goto first, last, etc. records. and always brings the first record in the table. Any hep in the right direction is greatly appreciated. Thanks.
View 2 Replies View RelatedI have got a tricky problem, for which I can find no answer - but then I am a bit of a novice here.
I have created a (probably too-) complicated relational database but what I really need is to be able to make abutton on the page of my address book form, so that when I hit it it creates a report of an address label for that current record on the screen only.
I hope this makes sense. If this is possible, I then need to extrapolate this technique to print a report of a subform (of samples sent) of a current client record.
All help gratefully received
Xenia
I have a form with data in formview. When I select a record I want it to highlight with a specific color.
How can I do that?
I am creating a query based on two tables: 1) tblClient (only one primary key "ClientID") and 2) tblContactDate (with two primary keys "ClientID" and "ContactDate").
Each client may have more than one contact date.
In the query, I only want the records from the tblContactDate showing for the MOST RECENT contact date of the client only; however, right now the query is producing more than one record for those clients with more than one contact date.
Not sure if I need add some special code to the "criteria" area in the Design View, or somewhere else. Your help is appreciated. :confused:
hi all, i create a form which have a button to append the current entry to another table but i only want to append the current entry tat is open. So how do u set the query to append one entry(the current open entry on the form) using the criteria.
i notice the query only have sum, aver, +- ...etc..
tks.
I am a relative Newbie so if my question sounds stupid, bear with me:
I have a form with a subform linked to a query.
On the subform i have a checkbox[Recieved] and when checked adds a date to
another field [Year] on the subform which in turn updates the record in a table[Orders].
I then click a command button on the form which runs a crosstab query based on the
updated fields in the table [Orders].
This works well except for the last record ammended which does not update the [Year]
field in the table [Orders]. When the command button is clicked it is still the current
record in the subform.
clicking another record in the subform solves this or closing the form and reopening it before running the crosstab query.
Is there an easier way to automate the update? So that the user does not have to select another record first.