I am trying to create an Access 2003 database that contains several types of demographic information about the employees in my organization (40+ employees). I have created a database framework, but I feel as if I am not doing things as efficiently as I could be. I was wondering if anybody could look at my database and let me know if they notice anything in my tables or fields that could be better/fixed. I would appreciate it so much.
I have attached the database in a .zip file to this post (it wouldn't allow me to attach it as an .mdb file, so I zipped it).
Thanks so much. Feel free to reply via this thread or email me with any comments/help you can give me. Thanks so much again!
Hello I keep getting the error after I copied a form and saved under a different Name ++++++++++++++++++++ A problem occured while Microsoft Access was communicating with OLE Server or ActiveX control.
There may have been an error evaluating the function , event or Macro
+++++++++++++++++++++++++ Strangely, I keep getting this error when loading and clicking on a control in the ORIGINAL form while the copy seems to work without errors.
I have deleted some tex box controls from the first form and added a few new ones in the copy.
I have tried a copy of the database by deleting the New form, but still get the same error.
I have run DEBUG on both form codes and everything seems fine.
I have checked the refrences and find none missing.
Is there any way to resolve this issue. Or am I doing something fundamentally wrong by copying and modifying the form ?
Guys, any idea how to evaluate the command line options used when an Access application is started from a command line. For example I use the following command line: C:..MyDatabase.mdb /parameters:110001110 Then, in VB, I evaluate the string "110001110" and do the further job. Is there any property or function to return the command line option string? Thanks
I was not sure exactly how to word the title so that it made sense, but I have strDocName assigned a report name and would like to use the value of the string on the left hand side of an evaluation. In the example below (which changes the caption of a report depending on the week) where stDocName appears in the "Reports!stDocName.Caption = ", I wish to actually use the value stored in the string. Does that make sense? This is what I have:
Code:stDocName = "rptTopScores" DoCmd.OpenReport stDocName, acViewDesign, , , acHiddenReports!stDocName.Caption = Forms!frmDoStuff!LeagueName & " - Match " & Forms!frmDoStuff!txtWeekNum & " - Media Report"DoCmd.Close acReport, stDocName, acSaveYes DoCmd.OpenReport stDocName, acPreview This is what I want it to evaluate as:
Code:stDocName = "rptTopScores" DoCmd.OpenReport stDocName, acViewDesign, , , acHiddenReports!rptTopScores.Caption = Forms!frmDoStuff!LeagueName & " - Match " & Forms!frmDoStuff!txtWeekNum & " - Media Report"DoCmd.Close acReport, stDocName, acSaveYes DoCmd.OpenReport stDocName, acPreview And yes I know I could just use the second code segment, but then I wouldn't be able to use the code snippet without remembering to edit it. I've spent a couple hours trying to find the correct method to no avail. Any assistance out there?
i have a query that returns a dataset that looks like this:
Account Status 123 Paused 123 Paused 123 Not Paused
I want to evaluate these results and in a second query, for the account 123 return the value "Not Paused" if there is at least one status set to "Not Paused".
I am having problems getting Access to evaluate whether a textbox is empty using VBA behind my form. I'm currently using Access 2000 on a Windows98 machine, but I also tested this in Access 2003 on an XP machine and got identical results.
The way I’d like the code to operate is if the “Product1” field is empty, the value taken from another form would be pasted there, and if it isn’t empty, go to the “Product2” field and if that field is empty, paste it there, otherwise go to the “Product3” field, etc.
I have tried using If IsNull(“Product1”) Then… but Access seems to think that the unbound textbox with no default value isn’t empty.
I tried using If ("Product1" <> "*") Then… but Access again seems to think that there is something in that unbound/no-default value textbox.
ElseIf ("Product2" <> "*") Then DoCmd.GoToControl "Product2" DoCmd.RunCommand acCmdPaste …
End If
I admit that my VBA skills are limited, but after having scoured these forums as well as Access help and examining the logic of my code, I am completely stumped. Any suggestions you can offer would be greatly appreciated!
Code: 'Pacer frame set to proper background If Me.FrmPacer.Value = 1 Then Me.LblPacer.BackColor = vbGreen ElseIf Me.FrmPacer.Value = 2 Then Me.LblPacer.BackColor = vbYellow ElseIf Me.FrmPacer.Value = 3 Then Me.LblPacer.BackColor = vbRed
[code]...
I have multiple frames on one subform that when selected sets a unassociated labels back color. Then when cancelled by undo code the back color stays on the color that it was changed to on afterupdate event from the frame with option controls. I would like to loop thru but could not come up with the proper method if it is even possible.the above code works after refreshed, but just wondering if there is a shorter or more efficient method.
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.
I'm trying to set up a simple query that links four tables. However, the tables are extremely large, all in excess of 1.5GB each so I had to split the tables up into four separate DBs. I've tried the following with no success:
1) Link the 4 tables in the DB which contains my primary key. This quickly inflates increases the file size above 2GB and won't let me go any further.
2) Build a remote query to connect the four tables. This looked promising until I tried to run the query and it became evident that it only knows to point to the last database source that you specified.
I'm running everything locally on my C drive. The data source are simple text files (1.6 million rows) from the FDA website.
Well, I am facing one problem..in my application; I need to show all forms / reports name of other database( .mdb ) file without opening the other database physically. I tried a lot but didnt succeded. I tried with below code..
Set AcApl = New Access.Application Call AcApl.OpenCurrentDatabase(strfolder, True) Set AcProj = AcApl.CurrentProject
Set frm1 = AcProj.AllForms
intCount = frm1.Count
But here wen the second line AcApl.opencurrentdatabase get executed at that time the database get open physically, and i dont want that..So is there any other way around..If so..please please help me..
i have a database that runs updates from within itself.what i need is, this database to then open a another database run a update query, then close it.
I have designed a touchscreen input system using Visual Basic.net and this writes to an Access Database. Each Touchscreen has its database locally so it can still work even if there are Network problems.
Now what I would like to do is have all these local databases write to a central database say every minute but only write new records to the central database. The Central Database can either be Access or SQL.
I have been working on a database for over a month now, and my boss just threw a monkey wrench in my work. I believed that the Metal Market Prices would be entered once a week in the current DB. My boss informs me today that he wants an employee to go in every morning and enter that days Price for Each Metal with respect to many different markets.
There is a total of 12 metals, and 5 markets. I need the data to be stored first by date, then by either market or metal, and lastly by which ever isn't used second (Either: date-market-metal, or date-metal-market). I think the Latter of the two methods makes the most sense. Is it possible for my current DB to lookup values from the Metals Database based on date-metal-market?
I have a problem that seems to be happening on several users' databases and is causing a big problem. None of the databases is a shared database...they are all single-user databases on stand-alone computers. I have tried looking for help within previous posts, but all seem to be related to shared databases.
I am getting an error message: "The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time." The database cannot be opened, imported, repaired...nothing seems to work.
Again...these are NOT shared databases. I appreciate any help I can get. I created the database for all of the secretaries in our school district to keep up with absence data. It involves many tables, queries, forms and reports, and has generally worked well. However I am now seeing several that are getting similar errors as mentioned.
I'm new to Access and VBA, for the record. What I need to do is copy a SQL database (table structures and records) and save it as a local Access database, so that the user can query and make any changes on the local file without editting the SQL database.
Looking around, it seems like the best option would be to use the TransferDatabase method with the acImport option. Does this sound reasonable, or can someone with more experience suggest a better way to go about it? Thanks.
I am testing the security of my DB X on Acccess 2007. I could create a new database Y and linked to the database X. Unfortunately i could change the records on the tables. I don't want other DB that make connections to my DB to change my tables!
Is it possible to access a table in one Database (Database A) from a separate Database (Database B) and if so how. I should clarify that this is an Access Database.
I created a database with forms, querys, etc. Now I was thinking of creating a exe so everyone can use this database without having access to all the design functions.
the only problem I see about doing this is if we need to use the same database for different project, we need to always create a new exe for each project.
Is there a way to make a empty database exe with just the layout, querys etc but with no data on the tables and have a save, open and save as option. So we can have several projects using the same database?
I was thinking to create a VB code for the database to delete all data on the tables when the database is open. and before closing the database exporting all the data to a txt file or something.
Then the next time someone opens the database goes to the main form which has a open bottom which imports the txt file into the tables.
This way we can have several txt file for several project and use always the same exe database.
I am working on a database that will be an addition to an existing one on the company server. However, to make the overall layout not so complex and allow room for other additions in the future, I'd like to keep the databases separate. This will also ensure more efficiency, integrity and troubleshooting overall.
I have a car dealership database and have a servicing section.
I want to create a form to service these vehicles.
The only vehicles which will be displayed on the from are the sold ones (because you cannot service a vehcile which hasnt been sold) which i have a query for.
I also want the form to display the customer who owns the car the service details (eg what they need), the parts availiable and the parts required for the service.
The calculations i need to make in this form are as follows
quantity needed (parts required table) - quantity in stock (purchasing customer parts table)
i havent been able to figure out how to get my subform to work properly.... i've posted in the forms section (http://www.access-programmers.co.uk/forums/showthread.php?t=99624 )
it is really important that i figure this out so that i can keep my job..
how else can i obtain help? even if i have to pay for it?
I was playing with passwords for one of my access db.
I tried opening the db with the password, but it won't work. So, I thought I forgot the password. I checked some posts on this forum that helped in retrieving the password and it's the same that I have been trying. I am not sure if the Name is correct, is there a way to retrieve the log on name?
I also noticed that when I try opening any other db on my system, it asks me to log on, I though I was only applying a password to one file.