Forms :: Requested Change In Table Not Successful
Mar 31, 2015I'm getting an error message as shown in the screen shot I've been trying found where the error is in my relationship but just can't see it.
View Replies
ADVERTISEMENT
Forms :: Linking Successful Login To A Form
Jan 3, 2015i have created a login page and i have coded it but i do not know to set coding up so that it results in a linked form to open up..( main menu)
View 8 Replies View RelatedTable Organization Help Requested
Aug 31, 2007I have at least 200 excel spreadsheets that get updated every day with closing prices of commodities. I want to run a breakout query against every spreadsheet to see if today's close is a new 20 day breakout. What is the best way to organize the access table or tables? Put all the data in one table or have separate tables for each contract and run one query against the group? I need to be able to update the tables everyday so that today's close info is included. I have a working query that works with a single dynamically linked spreadsheet to table, but I don't want to set this up for all 200 spreadsheets. Can this process be duplicated? I have tried to modify the query to run against two separate tables, but was unsuccessful. Since there are so many potential tables, if that is the best way to do it, how can you say select all tables and then run the query?
Here is the query I have written:
SELECT WZ07.Symbol, WZ07.AlphaDeliveryMonth, DateSerial(Val(Left([ddate],4)),Val(Mid([ddate],5,2)),Val(Right([ddate],2))) AS transdate, WZ07.DClose
FROM WZ07
WHERE (((DateSerial(Val(Left([ddate],4)),Val(Mid([ddate],5,2)),Val(Right([ddate],2)))) Between Date()-28 And Date()-1) AND ((WZ07.DClose)=(select max([WZ07].DClose) from [WZ07])));
The WZ07 is the December Wheat contract for 2007. This is what the excel spreadsheets are named. I had to do a dateserial command to turn a number into a date(couldn't make access recognize that 20070831 was August 31,2007) All the column headings on all the spreadsheets are the same: Symbol, AlphaDeliveryMonth, DDate(transdate),Close
I know that this is both about tables and queries so I wasn't sure where to post this. Thanks for your help.
Forms - Allow Edits, Only When Requested
Nov 29, 2003Hello,
i am relatively new to databases so this is a fairly basic question.....
I have a form. Within this form all records are locked as i do not wish users to change details. However i am told there is an option allowing the editing of existing records upon request. is this true?, as it would improve my form greatly
help would be much appreciated
thanks.
Help Requested On Access Search Criteria Forms
Apr 5, 2006hi i just signed up to this site and need some assisstance from someone
i need to create a form in access 97 that enables me to search for a keyword on a given search area, for example the cd/dvd rom drive for files and folders
is this possible in access 97?
if so could someone help me out please?:confused:
cheers
Many-to-many Relationship Not Successful
Jan 18, 2005Hi,
What looks simple is not turning out that way!
In our organisation a country may have one or more (often just two) provinces. Some provinces have one or more countries (as many as four). As an example, Philippines South province may have that part of the philippines to look after and Pakistan as well. The country (philippines) has two provinces, one north one south. The south has part of the philippines to look after plus pakistan. Clearly, then, a many-to-many relationship?
In due course I want to be able to pull out information relating to the country (e.g. Philippines) from either the north or the south province....the country info will be the same, but the province info will be different.
But I keep getting a one-to-one resulting from the following tables, which was the way I thought I'd need to create the many-to-many relationship. I'm beginning to think it mightn't be M:M after all!
COUNTRY
countryID (text type, since no names repeat)
PROVINCE
ProvinceID (text type since we use a 3 letter code eg FIN stands for Philippines North FIS for Philippines South)
COUNTRYPROV (link table)
ProvinceID
CountryID
I assumed that to relate the first two successfully, I create a link table by taking the two primary keys above and putting them into a third table, right?
But for some inexplicable reason, I can't then get PK countryID to relate to FKcountry ID one-to-many. It comes up as one-to-one. Similar when I drag PKprovince to FK province in the link table.
I'm obviously missing something very simple but what is it?
Julian
How To Test If Delete Successful
Oct 18, 2013I have some code to delete a record from a table, which does not delete the record, presumably because of referential integrity settings. That is fine of course, that's what should happen. However I want to be able to notify the user if a delete did not succeed. How can I check this? I tried this code, but it did not return an error.
Code:
strsql = "DELETE * FROM " & tblAddress & " WHERE GenAddressID = " & Me.tb_edtID
Err.Clear
CurrentDb.Execute (strsql)
If Err.Number <> 0 Then
MsgBox (Err.Description)
End If
Queries :: Run Query On Successful Login
Apr 3, 2013I have created my own login forms using VBA Language, however I now want a query (Query A) to automatically run, to update data from a linked table (Table A) to a table that is editable (Table B).
View 4 Replies View RelatedForms :: How To Change Default Message Of Existing Table
Jan 7, 2014How to change the default message of Exist table. the message shows like as. I wanna change this. How can i will do this?
View 1 Replies View RelatedWeird Help Requested
Feb 5, 2007sorry,
there are too many requests and not enough information to easily create the required reports for my customers.
I want to copy a table/query with yes/no fields and paste into excel with the
boxes for the yes/no fields. . .
I can't remember or figure out how to do it. . .
I know it can be done as I have done it before, how, i can't recall. . .
thanks
sportsguy
:insert idiot emoticon:
Changes Not Successful; Create Duplicate Values In The Index, Primary Key, Or ...
Nov 29, 2005This is a real mind bender.
I am running a2k. I am merging two databases. table1 is in the backend database. table2 is linked to the second database.
I run an append query to add table2 entries to table1. The append fails for 96 recs saying key violation. Turns out I can rerun the append if I drop the zip code field.
I then try to manually change the zip code and receive the duplicate values in index, primary key, or relationship.
I ran a compact & repair the databases, still no go. I can't edit the value of the zip code. I import the table to a new database. Still can't change the zip code. I drop MANY of the indexes. Even the index on the zip code field. Run compact&repair. still getting the index message. I even re-imported to a new db again. Still same error message.
I may start again by importing JUST these two tables to a new blank db and see if I get the same issues. I'll keep you posted...but maybe there is someone out there who has seen this error before.
fyi-if I enter a new record, the autonumber field correctly increments to the next available autonumber field. (I say this because of another thread on this matter w/ autonumber fields and bug in A2003)
Queries :: Calculate The Number Of Successful Backups Within A Window
Oct 16, 2014I receive a report daily. From this report I need to calculate the number of successful backups within a window, and number of successful backups outside a window. The window is 7:00 a.m. The column contains date and time (10/15/2014 7:00 AM). Right now I have written the following query =Date() And > #7:00:00 AM#
I do not get any results even though there are 79 records that fit the criteria.
Forms :: How To Change Drop Down Box Options In Form Without Affecting Table
Jun 20, 2014I'm a new user of Access and I'm required to input a survey into access. The data collected is being analyzed afterwards and therefore there cannot be any alpha content in the original table so I assigned multiple choice options numbers to correspond to the answers. In form view however I need the drop down boxes to spell out the answers, not just the numbers so the interviewer can read them out, I've seen this done on other similar surveys..I also tried going into form view and editing the drop down choices but that changes the table values.
View 1 Replies View RelatedResolution Requested - SQL Statement In VBA
Jul 14, 2004I have an Append Query that I developed and am wanting to use the SQL View statement in a piece of VBA code that I am writing. The problem is that it is not working.
Here is the SQL View when I am in Query Design View.
Code:INSERT INTO tblMasterAccountList ( AccountNum, AcctName, [Account Type], [Billing Spec] )SELECT TEMPAcct.Acct, TEMPAcct.Name, TEMPAcct.Type, TEMPAcct.BillingSpecFROM TEMPAcctWHERE (((TEMPAcct.Existing)=False));
Here is the code I have in VBA...
Code:Dim strSQL As StringstrSQL = "INSERT INTO tblMasterAccountList (AccountNum, AcctName, [Account Type], [Billing Spec]) "strSQL = strSQL & "SELECT TEMPAcct.Acct, TEMPAcct.Name, TEMPAcct.Type, TEMPAcct.BillingSpec "strSQL = strSQL & "FROM TEMPAcct "strSQL = strSQL & "WHERE (((TEMPAcct.Existing)=False));"DoCmd.RunSQL strSQL
The code runs fine in that no errors are generated, but it does not append the records. When I run the query outside of VBA, it does append the records. Is there something special that needs to be done with an action query for the SQL statement to work in VBA?
Commission Calculation Assistance Requested
Nov 3, 2004Hi all. I am looking for a formula to calculate a commission, based on the total sales price. The commission is a sliding scale. Ex. if sale price is 200 or less, commission = 30%; if sale price is 201-500, commission = 25% on 201-500 & 30% of 1st 200; if sale price is 501-1000, commission = 20% on 501-1000, 25% of 201-500 & 30% of 1st 200; if sale price is 1001+, commission = 15% on 1001+, 20% on 501-1000, 25% on 201-500, & 30% on 1st 200. Does that make sense? In other words, if sale price is 300, I want commission to be 30% of 200 + 25% of 100, which = 85. Is there a way to write this in one formula?
View 4 Replies View RelatedReport Not Showing Requested Totals
Jun 24, 2005Dear All:
I am totally clueless on this one. Here is the statement I am using in a textbox in a report:
=DCount("[Transcript_type]","[Diplomas_requested_per_month]","[Transcript_type]='Official copy'")
This report is based on a query. The query is called "Diplomas_requested_per_month". The fields in the query are: "Transcript_type" and "Request_date". In this query there is Between [Start Date] And [End Date].
I am attempted to count the number of "Official Copy" for a specified month.
I am propted to input the start date and ending date, it works great when I run the query. But in the report, I get a "#error".
I am thankful for who have responded to the previous posting of this issue, but I am completely lost.
Any help is greatly appreciated.
Regards,
Dion
Error # 5852. Requested Object Is Not Available
Dec 1, 2005Using Access 2k and Word 2k on a WinXp Workstation.
The code that I'm using is MS standard and has never previously given me any problems (see below). I've searched the MS knowledge base and Googled the error but nothing I find seems to relevant to this situation.
Can someone help?
TIA,
Mo
-----code----
DoCmd.Hourglass True
If CreateWordObj() Then
With gobjWord
.Visible = True
.Documents.Open "c:hp_lettersdrugsranout.doc"
DoEvents
With gobjWord.ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.Execute
End With
'.ActiveDocument.PrintPreview
.Visible = True
End With
End If
End If
Reports :: Parameters Being Requested Multiple Times
Nov 17, 2014I set up what I thought was a simple query in my DB using it to create a report containing a subreport.
I kind of works, however Access is asking me for the parameter value [Extract Date] multiple times, when in theory it should only ask once!
In addition (and likely related) the Access is then repeating the output multiple times.
Forms :: Change Button Color After Data Change
Apr 5, 2014I am trying to change the button color on a subform if a related form data changes.Main form is products with a continuous subform with serial numbers of products i.e, serial number, location, price and a button to add addtional issues if there are any for this particular serial number (this will open up another form related to the serial number so I can add an issues if there are any).The reason I would like the button to be a different color is so I can quickly see if there are any additional notes been added to the serial number. Just in case you may ask why not add the field to the continuos form is that the issues and be quite lengthy and there may be lots of serial numbers on the form
2346 location warehouse price 29.99 (button - green)
2347 location shop price 29.99 (button - red)
Forms :: Cannot Add Or Change A Record Because A Related Record Is In Table
Feb 22, 2015I have created three tables, all of them are connected by one-to-one relationship by same field, as you can see in the screenshot. and at the same time I have created three forms for each table. then I brought two forms in one remaining form. so whenever I entered data in first form and click on the next tab in which another form exist, it gives me this error: "you cannot add or change a record because a related record is in table".
View 2 Replies View RelatedQueries :: Updating Child Table List View On Change Of Main Table Row
Apr 19, 2013Here's a query that the bottom listview in the attached form i.e. a listview representing a table of calls(many) to fims (1 top listview)
Code:
SELECT calls.id, calls.firm_id, calls.called, calls.said, calls.spoke_to, calls.next
FROM calls
WHERE (((calls.firm_id)=[firms].[id]))
ORDER BY calls.called DESC , calls.next DESC;
When I run the thing...I get a dialog asking me for firm id.
I want to change this so when I move up and down the firms LV (top)... the bottom LV updates taking firm id from the top LV with focus.
Access 2003.
Move Record To New Table After Data Change In Combo Box Value In Original Table.
Nov 24, 2006Ok my problem is this......
I have a form with a field "Property Status" on it. It has 4 possible values -
"C - SHELTERED (with warden charge)";"H - SHELTERED (No warden charge)";"J - WHEELCHAIR SHELTERED (With warden charge)";"M - WHEELCHAIR SHELTERED (No warden charge)";"X - DISCONNECTED"
When the value "X - DISCONNECTED" is selected in the form I want the record to be removed from its existing table and sent to a new table which keeps all the "X - DISCONNECTED" records together.
Any ideas would be greatly appreciated. How would this be coded?
Forms :: Change To Forms Property Causes Fetching Different Records
May 5, 2013I have a form that launches a query. The results are displayed in a continuous form called ParentForm. On the ParentForm is a combobox which selects a singleform called a ChildForm that displays the details of the selected record. I placed a command button on the ChildForm. This button simulates a circular triple state toggle switch. By clicking this switch the form goes from ReadOnly to Edit to Add modes. The form properties AllowEdits , Allow Additions and DataEntry are adjusted accordingly. These properties when they are changed seem to initiate requery of the underlying data source. Here is the code. I am looking for a way to avoid requery of the data when the mod is changed.
Code:
Private Sub ModeBt_Click()
'-------------------------------------------------------------------------------
' Circular toggle button to change display mode of the form
' ReadOnly - Edit - Add
'-------------------------------------------------------------------------------
Select Case Me.Mode
Case "Edit Mode"
Me.AllowEdits = False
[code]....
Change Value In A Table Based On Criteria From A Different Table?
Sep 22, 2015In my database I have 2 tables that contains values I would like to add if a check box is marked.
Table 1 columns: JobID;JobNumber; Product; Qty.
Table 2 columns: COrderID, JobNumber, QtyChanged, Accepted(Accepted has the check box).Also, there are multiple records for each job number based on how many change orders will be.
I would like to change the value of Qty on Table1 with the values of QtyChanged from Table2 only if the checkbox is marked.
Change Linked Table With Vba
Mar 16, 2006Is it possible to change which table in an external database that my linked table is pointing to using VBA? (Access 2000)
Example code appreciated!
Regards
Change Top N Based On Table
Aug 12, 2005Well hello everyone. I'm building some queries that need to have a specific Top N amount read from a value in a table (or form). Can someone please tell me how to do that without using the query Top N drop-down? I have this built into a VBA module, so it's possible I can change the Top N from there. Thanks!
View 2 Replies View Related