Append Query Using The Current Record

Append Query To Current Record Only
This is my first time posting to a forum so don't hesitate to correct me if I am doing something wrong.

I am trying to create autofill templates based on the worktype.

How do I append data to the current record only!

I have a table that contains all of the job information including a record autonumber.

I have made a form using this table and it includes a subform for charge items from a separate table.

My users are complaining about having to key in every line item and want the form to autofill the subform for charge items based on the worktype field.

I have written an append query which is correctly extracting the worktype field and updating the charge items fields but it is doing so for every record with the same work type. I just want it to update the current record I am looking at.

View Replies !   View Related

---

Append String To Current Record
hello

does anyone know how to append a string to an existing text field so i have a record and the value of this text field is say "abc" i run an update query which pulls the string "def" out of another table and then now the value is "abcdef"

I have tried the following sql

UPDATE TBL_TmpSubmission
LEFT JOIN TBL_PropertyType ON TBL_TmpSubmission.PropertyType = TBL_PropertyType.PropertyType SET TBL_TmpSubmission.ErrorCode = [TBL_TmpSubmission].[ErrorCode] & "property"
WHERE (((TBL_PropertyType.PropertyType) Is Null));

which I expected to append the string "property" to the field which already had the value "measure" but it just overwrites the string having the same effect as

UPDATE TBL_TmpSubmission
LEFT JOIN TBL_PropertyType ON TBL_TmpSubmission.PropertyType = TBL_PropertyType.PropertyType SET TBL_TmpSubmission.ErrorCode = "property"
WHERE (((TBL_PropertyType.PropertyType) Is Null));

i have a temporary table in my db which is checked for errors, that is to say there are three fields in a further lookup table and three of the fields in this temporary table TBL_TmpSubmission are checked for nonexistent property types, fuel types and measure types by queries with the structure given above

it all has the effect i need of filling in this error code field so the error types can be displayed using a later select query - except when there is say a measure error AND a property error because then one query just overwrites the effect of the other

what i want is to be able to use say letter codes "P", "F", "M", append them to one another and then in my later select display that code so the user can see all the fields which need to be corrected

View Replies !   View Related

---

Append Current Record To Another Table
Im trying to make a button that sends the current open record information to another table. I created an append Query but it is pulling nothing. Can someone help me figure out how to send the current open file to a seperate table?

View Replies !   View Related

---

Append Query To Current Active Form Page
I'm trying to use an append query to copy data to be further modified leaving the original tables in tact.

The data is to come from two tables 'orders' & 'orders details' and append to two tables 'delivery' & 'delivery details'.

Assume the following:
‘orders’ has ‘orderID-(AutoNumber)’, ‘customer’ & ‘cust_order’
‘delivery’ has ‘deliveryID-(AutoNumber)’ ‘orderID’ (combo), ‘customer’ & ‘cust_order’
‘order details’ has ‘orderID’, ‘item’, ‘description’, ‘qty’ & ‘price’
‘delivery details’ has ‘deliveryID’, ‘item’, ‘description’, ‘qty’

Both ‘orders’ & ‘delivery’ have forms that show the respective details tables in a sub form (i.e. for item 1,2,3...).

Both ‘orders’ & ‘delivery’ have (AutoNumber) on their primary index (required).

‘delivery’ form has a combo for selecting the relevant orders.orderID value to select the set of records to be appended.

I have created two append queries, one that appends required data from the ‘orders’ table to ‘delivery’ table, the second to append the data from the ‘orders details’ table to the ‘delivery details’ table. (The thought being to run these together in a macro via a command button)

The second one seems to run OK as it will append the required data from ‘order details’ into new records in the ‘delivery details’ sub form and is linked back to ‘delivery’ by ‘deliveryID’.

The problem I'm encountering is that when the first append query is run, I need the selected data from ‘orders’ to go into the current active records in ‘delivery’.
The result I'm getting is that the selected data does append into the correct table & fields, however in the next new record.

i.e. if I’m in ‘delivery - form’ and creating a new entry, all text boxes are blank exept ‘deliveryID’ which is (AutoNumber).
I select the ‘ordersID’ combo and enter or pick the required value from the dropdown list which sets the criteria for data in ‘orders’ to be appended (lets say ‘ordersID’ = 1234), after selecting this the ‘deliveryID’ will be given a value automatically (lets say ‘deliveryID’ = 9876).

At this point the idea is to have a command button run the append query so that the corresponding values in ‘orders - customer’ & ‘orders - cust_order’ are appended into the current open records in ‘deliveryID’ = 9876 not the next/new records.

The result I'm getting is the values in ‘orders - customer’ & ‘orders - cust_order’ are appended into the next or new record set, ‘delivery’ table, ‘deliveryID’ = 9877

‘deliveryID’ = 9876, ‘ordersID’ = 1234, ‘customer’ = blank & ‘cust_order’ = blank

‘deliveryID’ = 9877, ‘ordersID’ = blank, ‘customer’ = appended data & ‘cust_order’ = appended data.

Can this be fixed so that the appended data for ‘ordersID’ = 1234 goes into the text boxes of ‘deliveryID’ = 9876 ????

View Replies !   View Related

---

Refer To Previous Record In Current Record Query
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

View Replies !   View Related

---

How To Set Query On The Current Record
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.

View Replies !   View Related

---

Append Query - Single Record
Hi,
I am trying to run an append query, but instead of appending the whole table, I would like to only append a single record.

I have an append query, and it works like a charm. I can also get it to work with prompting the user for a parameter (in my case LeaseId which is a primary key). When the user is prompted and enters the LeaseId it only appends the single matching record. Works like a charm.

However, I don't want to prompt the user for the paramater. I want the user to generate the LeaseId based on the selections in two combo boxes.

So far the I do get the right LeaseId, but I have no idea how to actually pass the LeaseId to the query. I've tried the following, but I know it's completely wrong.


stDocName = "approveLease"
DoCmd.OpenQuery stDocName, acNormal, acEdit, "[LeaseId]=" & Me.buildingCombo.Column(0)


Any ideas on how to actually accomplish this?

View Replies !   View Related

---

Query Not Picking Up Current Record
Hello,

Can anybody see what I'm doing wrong here. I have the following query

SELECT Contacts.Cust_FK, Contacts.ContactNr, Contacts.FirstLastName
FROM Contacts
WHERE (Forms.ComLogDetail.Cust_FK=Contacts.Cust_FK)
ORDER BY Contacts.ContactNr;

The "Forms.ComLogDetail.Cust_FK" part should render the current record in the form ComLogDetail but its not. Any ideas?? I'm an amateur programmer so my apolgies if the solution is all too simple.

View Replies !   View Related

---

Refering To The Current Record In A Query
It is possible to refer in a SQL statement to the "current" or "actual" record?I want to base the querry in the present value of two related tables. I will try to show you what I have to do, maybe you can give an alternative solution.These are the tables:tblTeam (idTeam, TeamName, DayCreated)tblTeamPerson (idPerson, idTeam)tblPerson (idPerson, PersonName, MusicPreference)Every person have a music preference, and the teams have to be formed by people with similar music preference.So, one way to filter that is to use the following method: everytime I create a team, I choose a person, ok. That firts guy is the one that will give the team his "MusicalPreference". The next members with different MusicalPreferences will be excluded from the query.But in order to do that, I need to refer to the current team that have 1 member, and use the value of the field MusicPreference in that Person in particular.I hope you have understand what I have to do, and I hope you can help me with this.P.S.: I´m using a combo-box with a special query in a subform to select the team members. In the main form I create the team. So it would be great to be able to use that "idTeam" value in relation with the firts team member of that team.

View Replies !   View Related

---

Query Based On Current Record
I am designing a contact management system and have hit a bit of a wall. I am farily new to Access so please advise if I am going about this the totally wrong way.

I have three tables; Contacts, Companies, Company_Addresses

Contacts:
ContactID (P)
CompanyID
OfficeName

Companies:
CompanyID (P)

Company_Addresses:
CompanyID
OfficeName (P)

What I want is when adding a contact, to have a drop down list in the CompanyID field showing all the companies in the Companies table, and then in the OfficeName field, have a list of only the offices related to the company selected in the previous field.

I can do the first list, but cant work out how to list only data related to the company selected in the previous field. Any help would be greatly appreciated.

Sorry if this is in the wrong forum.

Thanks. James

View Replies !   View Related

---

Query Based On Current Record Selection
right, struggling here.
Basically I have continuous form listing all the standard letters sent out when I double click on a letter, I need to to run a query for me. The code I have put behind the button is:
If ([StandardLetterType] = "Homework") Then
DoCmd.OpenQuery ("qryHomeworkClearMailMerge")
DoCmd.OpenQuery ("MergeHomework"), , , , , acDialog, Me.[StandardLetterID]
DoCmd.OpenReport ("rptHomework"), acViewPreview
Else
'
end if

and in the query I am first clearing the mailmerge yes/no field out which works fine and then running the merge to put the yes back in the current record so the query looks like this

Its an update query with update to yes for mailmerge and under the standard letter ID i have the criteria [Me].[OpenArgs]

is this completely wrong tried the [Forms!][frmFullStudentReport].[standardletterID] but it kept popping the box up asking for the number.

On the form I have the standardletterID and its hidden but there.

Any help would be greatly appreciated

View Replies !   View Related

---

Compare Current To Previous Record In Query
I have a table of customers who report trouble on their equipment. I would like to compare the date in the current record to the previous record to see if it occurred within 30 days. Can I do this with an SQL query or expression?

View Replies !   View Related

---

Append Query Key Violation (as Used In Create Similar Record)
I have a database of Assessments, each record having multiple subforms.

I am working on a button, which creates a "similar" (same) assessment, copying over all the subform records/selections.

So, after I actually insert a new assessment, pasting all the values from the original Assessment:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend

I need to copy over subform contents, for which I use an Append Query (actually, I use its SQL in VBA code to pass it the right parameters).

Now, naturally, I ran into Key Violations, because taking 50 sub-records from the original they would have their own AutoNumber Keys (ID's) and I can't append them into the same table.

My question: how do I copy over the same records (appending FROM and TO the same table) but cause the table to insert AutoNumbers for the records being appended?

Thanks!

View Replies !   View Related

---

MS Access Can't Append All The Records In The Append Query
hi Guys,
I have been looking at different post and checking Microsoft help files as well, but still can't seem to fix this problem.

I am having 2 tables. The first table is connected to a form for viewing and entering data, and in the second table i am just copying 3-4 fields from the first table.

I am trying to use the insert statement to insert records in the second table, and everytime i click on the "Add" button to add the records i get the following error "MS access can't append all the records in the append query ... blah blah blah"

However if i close the form and reopen it, and goto the record (as it is saved in the first database) and now click on the add button to add the fields to the second table/database, it works.

What am i doing wrong???

Any inputs will be greatly appreciated.

View Replies !   View Related

---

Dynamically Update Field Of A Current Record Based On Previous Record
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.

View Replies !   View Related

---

Population Current Record With Information From Next Sequential Record
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 Replies !   View Related

---

Can I Use Combo Box List Out My Record With Going To The Current Record ??
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)

View Replies !   View Related

---

Need Form For New Record/edit Current Record
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

View Replies !   View Related

---

Faxing Current Record Or Record Set
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

View Replies !   View Related

---

Append Same Record
I know when you see these you will think, "What are you doing?" So, I will tell you. I have to append certain records to another table multiple times. I have a field that stores the number of times the record needs to be appended. I thought that I could do it with SQL and a loop, but I can not get it. Any ideas?

View Replies !   View Related

---

Append To Next Record?
Is it possible to create a query and/or macro to copy fields 6-10 of the current record seen in a form to fields 1-5 of the next, already existing record in the table? The typical append query wants to create a new record, but I want the fields to be copied to the next record which already exists. Thanks!

View Replies !   View Related

---

No Current Record
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!!

View Replies !   View Related

---

Sum Until Current Record
I have a form in continuous forms mode and fields with amounts.
I want to have a field belonging to the Detail, where will be presented the sum of all above recs until current one.
E.g.

Name MyValues Sum
fwe 10 10
gfgerg 2 12
gegrere 4 16

and so on.
Any suggestions would be very helpful

View Replies !   View Related

---