Append query to different table


Friends, I would like some help with a code that allows me to run an append query only if a specific field is not already stored into another table.

Example.
I add a new customer to my database using table1.
I type his SSN. The code should check if this SSN is already present in another table (table2). If yes, so the code should skip the append query and just update any modified record included the SSN.

If not, the code should run the append query.

this should be done in the background while working in a form with its recors source to the table1.

Can anyone help me
Posted On: Monday 22nd of October 2012 05:40:55 AM Total Views:  276
View Complete with Replies

RELATED TOPICS OF Microsoft Access PROGRAMMING LANGUAGE




Import is appending data to table

I want to import an Excel spreadsheet into a table in my Access DB. I am using following: DoCmd.TransferSpreadsheet acImport, 0, "tblSchedule", "C:\mail\in\import.xls", false This code works but it is appending to the table. What is the best way to update the table - or would it be best to delete the old table and then to create a new one to import tha data into If the latter would be most elegant solution, can anyone help with some code that would do this
VIEWS ON THIS POST

86

Posted on:

Thursday 18th October 2012
View Replies!

Do I use an append query??

I am not sure if I am going about this update the correct way or not. Looking for some input on it. I have two tables, Lot Info and Lot Data. In Lot Info I have information from original assays, not wanting to change. In Lot Data I want to dump new assay data. I am inputting new data in with a form created from these two tables. The form recalls the data from Lot info to view and allows me to insert new data, the data to be dumped. If I use an append query will it just dump this new data to the table Or do I need to use another sort of query
VIEWS ON THIS POST

96

Posted on:

Thursday 18th October 2012
View Replies!

Displaying query results in a form

Hi , I have a query which totals the amount of all invoices for a specific project number. I would like to take this total and have it displayed on the form from where overall project information is inputed. Right now I have a table which shows general project information and a table showing invoice infomation (linked to the general table via a project #) I also have two forms, one for general info and there's a button which opens the invoice form. When the invoice form opens, it opens an invoice with the corresponding project number shown on the general form. I would like the total invoice amount (from the query) displayed on the corresponding general form. ex: in the general form for project# 100, I click invoice once and enter $50, I click invoice again and enter $100. The query automatically shows a total of $150 for project# 100. I would like this $150 displayed on the general form for quick reference.
VIEWS ON THIS POST

114

Posted on:

Monday 22nd October 2012
View Replies!

How do i pull subform data to a query?

, I have a form with a subform. I have the subform running a update query on a command button in the subform. The subform has the textboxes and I cant seem to get the query to pull the data from those textboxes. I have used the normal [forms]![form_name]![textbox_name] in the past but everything I try to pull the subform textbox data is not working. I have tried [forms]![form_name]![subform_name]![textbox_name] [forms]![form_name]![subform_name].[form]![textbox_name] [forms]![form_name]![subform_name].[form].[textbox_name] Nothing works. What is the correct syntax for this
VIEWS ON THIS POST

115

Posted on:

Monday 22nd October 2012
View Replies!

[Reports] Help with query-reports

, I have made a custom form to open up when I start a query which then enters the information I enter in the form, into the query. The query then produces the results in a report. My problem is, I wanted it to display all the records when I leave a field blank. BUT when I enter a "Location/Contract", it brings up the records I want with that location/contract but it also brings up some records that don't have an entry there. Am I making sense If not, here is some screenshots: I enter "Bridge Cross Rd" And it brings up those without a location/contract... Any Ideas The code for my report is: Code: Option Compare Database Private shadeNextRow As Boolean Const shadedColor = 13356495 ' Const shadedColor = 15726583 ' alternative shade colors ' Const shadedColor = 14078404 ' Const shadedColor = 13356495 ' Const shadedColor = 14281974 Const normalColor = 16777215 Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) On Error GoTo Detail_Format_Error ' Choose a color based on the shadeNextRow value If shadeNextRow = True Then Me.Section(acDetail).BackColor = shadedColor Else Me.Section(acDetail).BackColor = normalColor End If ' Switch the color for the next row shadeNextRow = Not shadeNextRow Detail_Format_Exit: Exit Sub Detail_Format_Error: MsgBox "Error " & Err.Number & ": " & Err.Description Resume Detail_Format_Exit End Sub Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer) If PrintCount = 1 Then txtPageSum = txtPageSum + Cost End If End Sub Private Sub PageHeaderSection_Print(Cancel As Integer, _ PrintCount As Integer) txtPageSum = 0 End Sub Private Sub Report_Close() DoCmd.Close acForm, "Master" End Sub Private Sub Report_NoData(Cancel As Integer) MsgBox "There are no records to report", _ vbExclamation, _ "No Records" Cancel = True End Sub Private Sub Report_Open(Cancel As Integer) ' Set public variable to true to indicate that the report ' is in the Open event bInReportOpenEvent = True ' Open Sales By Category Dialog DoCmd.OpenForm "Master", , , , , acDialog ' Cancel Report if User Clicked the Cancel Button If IsLoaded("Master") = False Then Cancel = True ' Set public variable to false to indicate that the ' Open event is completed bInReportOpenEvent = False End Sub And my Form: Code: Private Sub cmdCancel_Click() DoCmd.Close End Sub Private Sub Form_Open(Cancel As Integer) If Not bInReportOpenEvent Then ' If we're not called from the report MsgBox "For use with the Reports only", _ vbOKOnly Cancel = True End If Form_Open_Exit: Exit Sub End Sub Private Sub OK_Click() Me.Visible = False End Sub And for some reason this is what my Query looks like:
VIEWS ON THIS POST

156

Posted on:

Monday 22nd October 2012
View Replies!

Calculate Yes/No fields in a query

In a query that utilizes a table containing a yes/no field, is it possible to calculate the yes/no field and get a numeric return If so, can you share how this could be accomplished.
VIEWS ON THIS POST

310

Posted on:

Monday 22nd October 2012
View Replies!

Replacing query

hi. i are replacing queries in my tables to get the database ready to convert to SQLServer. Am having a problem replacing a query that is brings in a table for a drop down and inserting the selection in the record. during creation, the record shows a date, id, task, hours. the datatable needs date, id, task_code, task_name, task_job, hours. On "task" the drop-down allows for selection of several choices that are 3 columns wide. i can get the columns to show but only the first column currently goes in my new table. the table's columns that the selection comes from is "task_code,task_name, task_job". columns are needed as different areas use the same task code but use different jobs or names. I was told to use a insert into statement, however am having problems with it. have as follows: SQLStatement = "SELECT [Task_code], [Job_name], [Task_name] FROM tablea; " rec.Open SQLStatement, , adOpenDynamic job = rec(1) task = rec(2) rec.Close SQLStatement = " Insert into [tableb]([Job_name],[Task_name]) " & _ " values (" & job & ", " & task & " ) " rec2.Open SQLStatement, , adOpenDynamic db.Execute (SQLStatement) any point in the right direction would be greatly appreciated.
VIEWS ON THIS POST

291

Posted on:

Monday 22nd October 2012
View Replies!

ERROR: Operation must use an updateable query.

asp code: Code: error: Code: Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query. /site/content/register_act.asp, line 13 It's says it has something to do with permissions... I set writing & reading permissions for the db and the directory that contains it through contorl panel->Administrative Tools->Internet Information Services I have no "security tab" when I rightclick->properties the db file or it's directory even though the file system is NTFS in spite all these when I rightclick->properties the directory that contains the db I see a "read only" square marked in the general tab-attributes, when I try to unmark it seems to be unmarked but when I rightclick->properties again it's marked again... what should I do
VIEWS ON THIS POST

271

Posted on:

Monday 22nd October 2012
View Replies!

Relationships query

hi.. Im struggling to get the relationships correct within my database. (4 tables) It's for a shopping cart application Can someone please help: tblcustomerdetails: custid forename surname addline1 addline2 postcode email telno tbllogin customerid username password tblorder orderid orderdate orderqty orderprice tblproduct productid productname productprice productquantity productimage
VIEWS ON THIS POST

267

Posted on:

Monday 22nd October 2012
View Replies!

access query - continued

I sent message before I finished explaining my problem! The query to get the totals includes queries with the employee, vehicle, material, and barricade costs all sorted by activity code but for it drops all activities that don't have costs in each column--if there weren't any costs entered for material or barricades for the month, they get dropped. How do I get everything even with some fields are null
VIEWS ON THIS POST

306

Posted on:

Monday 22nd October 2012
View Replies!

Update query help

I have a table with trailer numbers currently on the yard. I also have a prompt to enter trailers as they are leaving. I would like an update query to then remove the record from the OnYard table. Is this possible
VIEWS ON THIS POST

275

Posted on:

Monday 22nd October 2012
View Replies!

Lookup value from a table to input in a query

I am building a mortgage/amortization database and I need to look up the "points paid" from another table and also the "adjustment to margin" based on the points paid and the index, "MTA" or "COFI". I have no idea how or where to write this lookup. Any suggestions Thank you! KellyJo
VIEWS ON THIS POST

289

Posted on:

Monday 22nd October 2012
View Replies!

Automatically update value in the subform from Crossstab query

, I have a main form which has 2 subforms. The main form is the name of the Student. The first subform is the data entry form. It has 2 fields. One for the Trimester - a drop down which has "1, 2, 3" listed. And the second field is the benchmarks (again drop down). Now the second subform is a more elaborate way of viewing (only for viewing purposes) which standards have been chosen for which trimester. Now this form's control source is a Crossstab query, which updates the Trimester field for each of the benchmarks for the particular student. Now my problem is I am trying to update this subform which is for viewing purposes only, automatically, so that the moment a benchmark is chosen on the other subform, the [trimester] field automatcially gets updated on this form. the benchmarks are listed on this form (view form) and a field which is the trimester field. Data entry is not allowed in this form. So the information has to get updated automatically. It does when I close and open the form, but thats not how it should work. I tried everything I thought possible (requery, refresh, a button to click) but couldn't get the form to update. Please help! I would really appreciate if you could help me find a solution.
VIEWS ON THIS POST

315

Posted on:

Monday 22nd October 2012
View Replies!

Requery value of combo to listbox

, I have a form with a text box, a combo and a listbox. The textbox stores the name of the user. The combobox gets its data from a query. I would like that when I open my form, by default the list box will show all the records of a table named Master and then, I would use the combo to requery the list by selecting another user. records under that criteria, will have to show up in the list box.
VIEWS ON THIS POST

285

Posted on:

Monday 22nd October 2012
View Replies!

Concatenation of query text

Can anyone please give me an Access 2000 VB module that will concatenate [Portia - 1 query.text1] with [Portia - 1 query.text2] and type the result in textbox [text]
VIEWS ON THIS POST

253

Posted on:

Monday 22nd October 2012
View Replies!

Listbox selections requery subfrm

I have a form that has names with a subform with information. The contacts are in a list box and the subform (in datasheet view) shows the phone number/email/etc.. of the selected contact person. I am using the form as a quick look up of a persons information. Before changing it to a list box, it was previously a combo box, and everything worked fine. i was able to requery the subform and the cooresponding info for the person would come up. However, its now a list box and i am having problems with the code to make this happen. I have a different button sending the names selected from the list box to a report...and that works fine, but i am obvisouly missing something to make it work with the subfrm requery. Below is the code i got so far. Any help would greatly be appreciated. its driving me nuts! Private Sub QuickLookup_Click() Dim varItem As Variant Dim strWhere As String strWhere = "[memberID] = " For Each varItem In Me.MemberID.ItemsSelected strWhere = strWhere & Me.MemberID.ItemData(varItem) & " OR [memberID] = " Next varItem strWhere = Left(strWhere, Len(strWhere) - 17) DoCmd.Requery "subfrmqryindividual"
VIEWS ON THIS POST

267

Posted on:

Monday 22nd October 2012
View Replies!

Locking a table based on query results

I have a query that checks an expiration date field and displays the word "Expired" in another field if applicable. This query is used to look up items, then the user would enter whether or not it is approved right in the resulting data grid thereby entering that approval into the table being used for the query. Can I do something with the query so that if the item returned has expired, a user would not be able to enter anything into the approval field Something like, if field1 = "expired" then lock the table I can't use a form, I have to just do it in the basic query or forget it. I would very much appreciate any suggestions!
VIEWS ON THIS POST

324

Posted on:

Monday 22nd October 2012
View Replies!

Help on query

i have this query Code: SELECT Group, ((Progress.DaysSpanned/(SELECT SUM(Progress.DaysSpanned) FROM Progress))*100)*((Progress.PercentCompleted*Weight)*100) AS WeightedProgress FROM Progress ORDER BY Group; it should reutrn the progress as a percent based on two weights (days spanned on task, and percent completed) the prob i have is if the percent is 100% complete it does not show in the progress, a value of less then 100% (due to the weights) how would i rewirte the query so that if percent completed is 100% it displays that, basically if the percent completed is already 100% it should ignore all other calculations and just return 100%
VIEWS ON THIS POST

331

Posted on:

Monday 22nd October 2012
View Replies!

Form using dynamic query results

hope this is a reasonable thing for semi-beginner to intermediate person to do. Our MS Access 2000 db shared on a server stores projects, categories of projects, clients, contractors doing the projects. Contractors are offered projects within their chosen categories based on their order on the list (last company who was offered a contract goes to bottom of list, like that). Currently we print out a phone list of the contractors (in desc. date order of the most recent offer accepted or refused), then we phone down the list until someone accepts. Later we enter all those offers in order on frmOffers to store the date & time stamp of that offer. Problem is, several contracts can be on offer on a given day, so when a company accepts/declines Project A, that company is still showing as high on the (paper) list for Project B. So when we have 3 people phoning they don't know they've offered multiple contracts to the same company this morning. Is it possible for a form "Offers" to be based on a query that keeps updating like that For example, you open frmProject to display details of a certain Project, cmd button to open frmOffers (continuous form, showing all offers so far with company, phone #, "accept"/"decline" & date/time stamp). Then, when you tab to the next record, can you have it show which company is next in line (even as the person beside you has updated their frmOffers in the meantime) Would you need to have a separate table storing only the most recent offer for each company If so, how would you get that updated all the time Anything you could offer to point me in the right direction would be very much appreciated!!
VIEWS ON THIS POST

267

Posted on:

Monday 22nd October 2012
View Replies!

Open query or form

access 2000 have a form with a list box some items in the list box after i click on it i want to open a query and the others i want to open a form help
VIEWS ON THIS POST

286

Posted on:

Monday 22nd October 2012
View Replies!