Hello,
I have written an extensive Access DB Application for my company and have placed it on a cd for distribution. When we try to copy it from the CD to a folder on another machine the above mentioned error comes up. Any Suggestions. Jeff
hi, I have 2 Tables: tablea & tableb. The field names in both tables are: user_id, firstname, lastname, from these i have 2 querys (querya & queryb) both of which just look at the fields from the repective tables, & from this i have 1 form (entry form) that pics up the fields from querya. I would like to be able to enter details (records) into the entry form which puts all the data into tablea then automatically copies all the information into tableb, then automatically checks to make sure there are no duplicate records in tableb and finally deletes all the records out of tablea (probably after the form closes). This might sound a bit strange why i want to do this but I hope if its possible it could be used in a larger database.
I think i need to do querys but not sure what I need to do, Any advise would be very helpful.
Is this scenario considered bad design form? (see attached image)
I'll never delete a Customer or Site for whom a a Job exists. I need historical data to remain intact. I'll just mark a Customer or Site as [InActive] so they can't be used, when appropriate.
If the relationships are considered good form, then what is considered good form when deciding upon which relationships to set to Cascade Update? Do I even need to update autonumber foreign keys?
On upsizing this database, SQL complains that this arrangement is a cyclic update and won't create the relationship unless I tell it to use Triggers. This leads me to question whether I'm correctly using Cascade Updates in my Access db's.
Thank you.
I’ll apologize up front; I’m not quite sure where to put this. It is an issue I’m having with a form, but it involves SQL and VBA…
I need some help with an access 2007 project I’ve been working on. I’m new to access, SQL and VBA so I’m not really sure how to make it work correctly. Here is the issue:
The form “frmAddCitationsToDissertations” does not work the way it needs to. It currently allows identical records to be made in the tblCitations. This is not correct, what I need it to do is before creating a new record look at the values from the 3 drop down records and see if that value set already exist, if it does then it should enter the existing CitationID and DissertationID in the join table. If it does not exist, then it should create a record in the tblCitations and insert the new CitationID along with the dissertationID into the join table.
The logic goes as follows:
A dissertation can have N citations.
A citation may be sited N times by any single dissertation.
Two citations may have any two of the 3 attributes in common and not be the same record, but if it has all 3 attributes in common then it is the same citation and a new record should not be created in the tblCitations, it should only be created in the join table. Two of the 3 attributes are able to be blank or null, so if a citation is simply “The bible” and the other 2 attributes are the same then I need to be able to compare it as well, and ascertain that it is the same citation.
I’m hoping for a programmatic solution, I don’t want some long involved process with forms because this database will end up having more than 50,000 citations. A long process will increase the expense of data entry exponentially.
Any advice that someone can offer is greatly appreciated. If you’re interested in helping an old dog learn new tricks you can download the database I’m working on here: http://c-helm.com/access/
Hello guys & gals on here.
I've been struggeling with this problem for a couple of days now and finally decided to post here and kindly ask for assistance. I've got a pretty strong OO background, but haven't worked with databases much...
I'm trying to set up a small client database for my company. The client companies have staff members (StaffClients). Client staff can attend Events and work on Projects, which I've managed via junction tables. Our own Staff can also attend Events and work on Projects.
I've uploaded my first draft of tables and relationships here:
http://img514.imageshack.us/img514/3937/snapjl9.jpg
(sorry for the blurred bits)
Now I think I've done a fairly good job in normalising the tables, but one thing that I keep stumbling across is that I need two almost identical tables: one for our Staff and one for StaffClients.
They are subtly different, e.g. StaffClients have some extra attributes, such as JobTitle, Department and one StaffClient can be the assistant of another StaffClient (also done via a junction table). The other difference is that client staff can have different roles in a project (MainContact or Assistant) compared to our own staff (Manager or TeamMember).
As a result of these differences I have set up separate junction tables for both Staff and StaffClients, but are obviously very similar.
My question is: Is that structure sound? Or can I simplify it somehow before setting up the forms for the data entry?
Your input is kindly appreciated!!
I've inherited a database with multiple users, massive redundancies, and strict policy limitations:
1. The existing table structures must remain, despite redundancies that create problems constantly, because of multiple client programs.
2. Data entry must not be programmatic: no data can be generated by code.
I can, however,
A. Create new, interdependent tables, or
B. Validate data with criteria or VBA.
I have two rules I'd like to enforce:
Rule 1: This one is easy. Certain redundant Locations must be identical. So, all the Locations in Table A must appear in Table B as well (although B may contain Locations not in A). No Big Deal.
Rule 2: An absolute rule for every Location is that each must occur between the boundaries established for that particular Track and Section.
So, given that I have a Table with Records of Track, Section, StartLocation, EndLocation (which defines the limits for Location values)...
Track 1, Section 1: Starts at Location -100, Ends at Location 100
Track 1, Section 2: Starts at Location 50, Ends at Location 20
Track 2, Section 3: Starts at Location 0, Ends at Location 400
I want to prevent users from entering illegal locations in another Table with Track, Section, Location, like:Track 1, Section 1, Location 101 (this location is beyond the end of Track 1 Section 1)I could code this in VBA, but the downside is it won't be enforced if the user has Macros turned off by default.
I have a db that is in a multi-user environment. I have a problem that keeps occurring. The problem is that I designed a report that is programatically sent via an email from a button on a form. The problem is a copy object error (run-time error 2501)that is occurring. The object wasn't canceled, ie the email wasn't cancel (which produces the same run-time error). This error just occurs. Doesn't occur all the time. The same report will be able to be sent (emailed) by the inspector either by the end of the day or by the next morning. So the problem doesn't appear to with the code (even though it still may turn out to be), since it works. The report has a unique name which is done by the coping the object and then deleting the object.
This problem is completly frustrating me and my users as it appears that it just "hangs up" when it so desires.
Any help would be greatly appreciated.
Thanks.T
I want to be able to click a field and it copies the field value. Just as if I were using Ctrl+C. THen I can go to excel or internet an paste it. i have the code:
Code:
ClipBoard.SetData = Me.GBL & vbCrLf
Me.GBL.SetFocus
Me.GBL.SelStart = 0
Me.GBL.SelLength = Len(Me.GBL)
I keep getting an error 424 Object required. How do I fix this or is this even on the right path?
I have a form with two Text box fields "Username" & "Password"
I want to create a copy button next to each field that will copy the contents to the clipboard.
The password field has the code:
Code:
Private Sub Command228_Click()
Me!Password.SetFocus
DoCmd.RunCommand acCmdCopy
End Sub
The username field has the code:
Code:
Private Sub Command229_Click()
Me!Username.SetFocus
DoCmd.RunCommand acCmdCopy
End Sub
The Username copy button for some reason gives me the following error:
Run-time error '438': Object doesn't support this property or method
Why it doesn't like this field?
I am using Excel/VBA as a frontend and Access backend. The sheet2 stores the queue name and Queue number. We have to update the sheet1 from column L to column O by looking for the values from the Access table for the date selected from the comboboxes. Now In sheet 2 , it says Queue number and in actual in access table it is the combination of Type & Type1 & Type2. So we have to look for Type & Type1 & Type2 in the table and find out total Batches ,Total Envelopes,Total documents and total pages and then store the values in the ExcelSheet1 from column L to column O.
The following formulas will be used in the select statment:
Total Batches = count(BatchNo) for date selected
Total Envelopes=sum(Envelopes) for date selected
Total Documents=sum(Cases) for date selected
Total Pages=sum(Pages) for date selected
I have a report with an if then statement for a checkbox.
If checkbox is true...etc versus if checkbox is false...etc
When there isn't a record that has this checkbox checked, I get the error
"You entered an expression that has no value." (Runtime error 2427) How can I ignore this error
when the record doesn't apply to the criteria?
I'm using the following code (from GHudson) to check for required fields being filled in but it stops at a particular combo box and gives the error message Runtime error 2110 "can't move the focus to the control" although it works on previous combos which are set up in the same way.
Private Sub Close_Click()
'Key "Required" in the Tag property of the form object.
Dim ctl As Control
For Each ctl In Me
If ctl.tag = "Required" Then
If IsNull(ctl) Or ctl = "" Then
MsgBox "You must complete all required fields to continue. Your cursor wll now be set to the missed field", vbCritical, "Required Field"
ctl.SetFocus
Exit Sub
End If
End If
Next
Set ctl = Nothing
DoCmd.RunCommand acCmdClose
'If MsgBox("Have you selected the record type and filled in the other yellow fields? These are mandatory. If you haven't - click on 'No' to return to form", vbQuestion + vbYesNo, "Open Form?") = vbYes Then
'DoCmd.SetWarnings False
'DoCmd.RunCommand acCmdClose
'DoCmd.SetWarnings True
'End If
End Sub
When I try to do a spell check in a Form by pressing F7 I receive this error:
Can't start the spelling checker because it isn't installed.
How do I get it installed?
Thanks!
I have a form - see attached image.
The first text box is called Job_No. When a number is entered here how can I check that the number doesn't already exist when the user tabs to the next box. Then if it does exist display a custom message to the effect "This already exists" and not the Access default duplicate error message.
Thanks
Michael
I have some code that will run if I am populating an empty table(no duplication's possible) but now I am trying to create a Sub to check for existence of a record and handling it going forward.
Anyways the big picture is looping through a text file and placing data where it needs to go. The code follows including some comments point to the issue.
Code:
Sub PutinNewTag(TableIn As String, Tagtype As String, textline As String)
Dim strSQL As String
Dim NameIn As String
Dim TagName As String
Dim Db As DAO.Database
Dim rstin As DAO.Recordset
[Code] ....
Error: Error evaluating check constraint in the validation rule
What does this mean?
I have used a combo box in the table to input into 2 fields - one restricted to numbers 1 to 5 and the other restricted to letters A to E
Message comes up when I try to change the view or press submit. Does it have anything to do with the field type?
I would like to convert a text string to integer.
Lots of posts say to use val, but it is not listed in access 2010
So I am trying,
Creating a field that is numeric then just referring to the string field.
This works good except where it finds actual text. It puts the value "#Type!" in there.
Would there be some kind of function to check for an error or check if the value is text.
How do I go about copying data from a main form to the sub form?
I have a Soldto form with a Shipto subform.
When the two are the same I'd like to have a button to copy the data from the Soldto form to the subform.
I've tried some examples posted on this forum but, don't seem to cover a sub form.
Also, I have very limited coding experience, so please be gentle.
Thanks for any/all help you can give.
This is going to be a hard one...
We recently switched over to A2003. As far as my db everything went ok except for one thing.
I have a continuous form that we enter data into. When you open a new record the form only displays one empty row. Because we enter tons of rows that come from an AS400 system I made a button on the top of the form that switches from continuous form view to datasheet view.
Private Sub Label59_Click()
Me.[Part #].SetFocus
DoCmd.RunCommand acCmdSubformDatasheet
End Sub
Within datasheet view I am able to paste many rows at once. However, after the change to A2003 it still "appears" to work. Many rows are pasted in but if you exit the db and come back to the record all of the data is lost and you are back to one blank row in continuous form view.
When a record is added to the continuous form each record is assigned an autonumber primary key. I think that this is where it is failing. Within Access help is the following under "Cutting And Copying Data", "Copy or move records or data from multiple fields to a datasheet": To paste the data as new records at the end of the datasheet, click Paste Append on the Edit menu.
I think this is what I need to do but I don't allow my users access to any menus so I create command buttons or auto running code in place of menu items that they need.
Can anyone think of some code to run a Paste Append of these records or of any other method in which I might restore the multiple paste funtionality I had with A2K?
hi guys....
am new here and i need smal help :)
i have 3 tables inventory and sales and order and i made 3 forms for each table
in inventory form the user will only read the data
in sales and order the user will enter and delete the data
so my problem is i want to know how when the user enter the data in order form all the data will be copy in inventory table so what ever added in order table it will be copy or updated to inventory table
how can i do that?? :confused:
Hello friends!
I have read some of the posted threads. But I still couldn’t understand on how to solve my problem.
The scenario
I have 2 forms which linked to 2 different tables with the same attributes.
Example:
Form 1 links to table 1.Collumns (index,name,address)
Form 2 links to table 2. Columns (index,name,address)
The problem
How do I replicate the same data from Form 1 to Form 2 with Single button / command?
The Objective
What I’m trying to do is to create an auto fill function so that user do not have to type in the data into form 2 / table 2 by referring to form 1 / table 1.
Hope you guys could help me with this.
:o
Hi:
Can any one please help me when user select 2 dates from DDLDate1 10/09/2006 and DDLDate2 10/12/06 and the name and it close the form. I need to create multiple records in the another table on the basis of two dates like that.
Data in continous form table1
-----------------------------
Data entered in continous form
10/09/2006 10/12/2006 John
10/12/2006 10/13/2006 Petty
11/11/2006 11/11/2006 Stiffny
Required data into table table2
10/09/2006 10/09/06 John
10/10/06 10/10/06 John
10/11/06 10/11/06 John
10/12/06 10/12/06 John
10/13/06 10/13/06 John
10/12/2006 10/12/2006 Petty
10/13/2006 10/13/2006 Petty
11/11/2006 11/11/2006 Stiffny
I am currently using a continuous form where there are two drop downs one for AppDate and other for EndDate. I need a script here once use close the form, Script check the data and create multiple records on the basis of date selection into table2. I am using two tables. One for the continous form and the other for the reports (Both have same fields). Continous form table i empty it every time once it close. So i need the result into another table assume form table name is table1 and table where i need multiple records name is table2.
Thanks.
Hi
I need to copy some data from last record to next(New record), when a user clicks a button on a form. I don't want to copy all the data.
An example would be booking in items from an invoice:
Company would be the same
Invoice number would be the same
Product code would be different
quantity would be different
Hope this makes some sence, and i'm not being stupid:rolleyes:
Andy
I have a field called Shipdate. I have created a new field that is called invoicedate. What I want to do is copy all of the shipdates and paste them in the invoicedate field for my old records. From here on out they would be distinct fields but for old records and reporting purposes I need those dates.
Can I do that in a query, do I need a module???
It's probably easier than I think it is.
Thanks.
I have a problem....
How can I copy data from one column in a table to another colunm, with WHERE and UPDATE?
And how can I find the data, which is the closest, for example using LIKE '%'
UPDATE tag_info,tag_info_kilde SET tag_info.TAG_DESCRIPTION = [tag_info_kilde].[TAG_DESCRIPTION_KILDE]
WHERE [tag_info].[TAG_NAME]=[tag_info_kilde].[TAG_NAME_KILDE];
Hi All,
I have a form which is bound to a table. I want some of the Data to go to another table which is not the control source. I am trying this code, but no luck. What am I doing wrong?
Table![tblImageBLOBs]![record_number] = Me.aud_number
Thanks
Bones