I have this Access application. The Incidents table was created incorrectly. apparently the design was not thought over. The Incidents table was created to store employee accidents. However, since the employee data is based in nicely normalized data, all Incident reports will have the employee's PRESENT data, instead of the Department, Cost Center, Location, etc., that the employee was at the time of the incident. I want to de-normalize the Incidents table and add the Department, Cost Center, Location, etc., that belong to the employee at the time of the incident, I have added the columns to the table, but I am having problems getting the data from the normalized tables to the Incidents table in the Click event of the AddIncident command button. I have tried to create queries, but can not get them to work. I was unable to send the mdb, but attach is the AddIncident event. In the Fields statement, I want to get a field data from another table. For example, the present Employees table, which always has the employees present info, contains the AutoNumber Key to the rest of the normalized tables, Department, Cost Center, Location, etc; I want to get the actual Department name, Cost Center number, etc, and put it in the Incidents table. The Employees table has the data I want for the time of the incident. But the employee may change Department, Cost Center, Location, etc, before his next incident. :confused:
I'm going crazy!! Someone who didn't really understand relational database theory (not like I'm an expert, but. . .) built a DB that is much more complicated than it needs to be. That person left and I got the job. I have to work with our IT people to hopefully redesign it completely (I would without asking, but they control the SQL server and the connection to the web.) but in the meantime, I have to work with a mess.
Okay, the question--I have to print a report of which organizations used which software company.
The table fields are:
Organization Comp1 Comp2 Comp3 Comp4
etc.
the answers are a "1" for a yes and a "0" for no in the "Comp" fields, so there is an answer in every field of a record. Essentially, it's a spreadsheet.
Off the top of my head, I know I could query each Company name with the court individually and then union query them all together. But isn't there any easier way? We're talking about 15 queries if I do it that way!
Anyway, thanks for any help you can give--this is driving me crazy (over 40 table set up in similar disarray. . . .)
I have designed a form to act as a menu to open other forms. I created the form and placed command button on the form. Using the Access Wizard, the following VBA was created by Access to open the next form:
Private Sub cmdCCDolClr_Click() On Error GoTo Err_cmdCCDolClr_Click
Dim stDocName As String Dim stLinkCriteria As String
I have done this several times in the past with success. However, this time, I get the following error message when I click on the command button to open the second form.
The expression On Click you entered as the event property setting produced the following error: A problem occured while GCM (name of dB) was communicating with the OLE server or Active X Control. *The expression may not result in the name of a macro, the name of a user-defined function, or [EVENT PROCEDURE]. *There may have been an error evaluating the function, event or macro.
I tested other forms that were designed to open secondary forms and they continue to act correctly. Is there an easy fix to this issue. There are 16 command buttons and all get this message. Do I have to delete the form and recreate it. A nusiance, but not difficult.
Looking forward to hearing from you experts out there. Thanks, Alan __________________ Alan Sidman
I have a small receiving inspection database that prints lables for each item received. However when they use the program on a different computer the lables do not print on the lable printer at that location.
I am writing the following query to insert data in tblpostroom from another table. Both the tables have same field names like Date1,582,1810.Now If I run the following query then in the fields 582,1810 of tblpostroom , the data gets stored 582,1810 respectively for all the records. But in actual fields 582 and 1810 store different data.
Code: INSERT INTO tblpostroom ( Date1, 582, 1810 ) SELECT Date1, 582, 1810 FROM Sheet1;
all of a sudden now, when i open my form, its not displaying any of the previous data that i stored in it!! All the data is still in the table, and when i add data it goes into the table!! I checked properties and the record source and everything is ok there, what have i done wrong guys...HELP!!!!
I purchased a copy of Office 2003, on Ebay, for less than half the price it normally sells for. I created databases, which work well on my computer. However, when I copy them over to the office computer from my computer, via Flash memory stick, the program does not run. The message seems to indicate a problem with registration. When I installed Access, it asked me for a registration key, which I did provide. After that it worked-not activation process. Does this mean that you can only use the computer you created the database on to run it, or that I do not have a proper version of Access 2003? I cannot transfer, from my home computer, to the office computer. Is this Microsoft paranoia again? Subsequent uninstalls and reinstalls of the program did not ask for the installation key. What should happen? I would like to resolve this major problem and your help will be greatly appreciated. The program is flaky. Also, Switchboard does not work-must use Option Box.
These questions most user must know about initial installation and copying mdb files to other computers -please reply.
i have a string variable denoted by cui and i need it to take value from table tblPersoaneJuridice from field CodUnicInregistrare. CodUnicInregistrare and NumarInregistrareRegComertului is text.
cant get it running. i got the same query running made in the querybuilder, but somethings change, so i need to adjus it in vba. But it doenswork, and get a type error
DoCmd.RunSQL "INSERT INTO tblAccessLog " _ & "(DateLoggedOut, TimeLoggedOut) VALUES " _ & "(#" & Date & "#,#" & Time & "#) WHERE " _ & "(LogID = '" & LogsID & "');"
Could anyone possibly tell me why this isn't working? Probably something stupid i've done bus still.
When i run it, I am getting the error message "Query input must contain at least one table or query" yet i've told it to use tblAccessLog so i'm just confused.
i've posted it as the sql string is built in the code
i get a 'catastrpohic error' when running it from vb like this and when i build put the string into a query and run it through the access user interface it worrks
I made a number of changes to a DB that was working fine (normalised further). So what's wrong? I have a number of forms (for my sample DB I have stripped down to just one, if I can resolve for this I can apply to others).
qryENTRY_COMPUTERS ENTRY COMPUTER FORM
The user adds a new computer then selects the Computer Make (qryLOOKUP_COMPUTER_SUPPLIER) & Computer Model (qryLOOKUP_COMPUTER_MODELS).
This now returns error 'recordset is not updateable'.
The query on which the form is based is incorrect (qryENTRY_COMPUTERS). Previously I was able to add tblSupplier & tblSupplierProducts to the query then add field ProductDescription fom tblSupplierProducts to the query. This allows users to select a value from cboComputerModelID (ProductID), field ProductDescription then displays the related descriptive value for the ProductID selected (value remain on screen when user naviagtes between records.
I have attached a sample database (access 2002-2003 format).
I am sure the answer is staring me in the face but I have now spent hours trying to resolve!!
Help very much appreciated. Phil.
Update: One difference I can see is that on the query 'qryENTRY_COMPUTERS' where i have added tblSupplierProducts & tblSupplier the join properties are in reverse i.e. Left Table Name = tblSupplierProducts Rigth Table Name = tblComputers Left Column Name = ProductID Right Column Name = ProductID ????
I am trying to create a form that has an unbound text box which row source is a field in a table. I have created a subform that gets the records from another table.
Both tables are linked in a one to many relationship. When a user selects a record in the cmbo box i want the table to show only those records which are related.
This obviously didnt happen. So i changed the subform instead of a table as its source but a query, then in the criteria of a query just set it to look at the cmbo on the form. After the cmbo was updated i got the subform to requery.
This worked fine, but i was unable to make any changes or updates but with no particular error.
When inserting a subform using the wizard i do remember coming across a section of a wizard that asks me if i would like to tie in the subform with something on the form. However this part of the wizard just doesnt show. Maybe i was using a different version of access for this wizard?? i am currently using Access XP.
I'm trying to mess with a combo box where when one user selects a value a form opens up. This is the code I'm typing in but I keep getting a syntax error
DoCmd.OpenForm(Phone,[View As AcFormView=acNormal],[DataMode As AcFormOpenDataMode=acFormAdd],[WindowMode As AcWindowMode = acWindowNormal],[OpenArgs])
Hello once again. I have been working on a small database that records all IT records (Desk Info / Software / License Info / Hardware etc...) Somebody out there might even find it useful!! Will post to DB forum when working :)
Where to begin. I thought it would be a good idea to normalize a little more........(still not 100% but an improvement). I seperated Software/License information. I now have tblSoftware & a tblLicenseDetails.
Basically the clux of it all centers around a form 'frmDeskSetup'. You can search via three methods (Desk ID / User ID or Computer ID). I would like to amend the Installed Software Form (last form on frmDeskSetup) to include the License Information.
I have also tried to create a query linking tblComputerSoftware tblSoftware + tblLicenseDetails. My idea was to create a form that would allow users to Add Software/ Add a License for that Software and then allocate the licensed software to a computer all from one form. The query run OK but would not allow data entry via the form!!
Sorry for the length of this post. I have attached a stripped DB for forum in Acess 2003 format. Thanks in advance, Phil.
What is wrong with this line? I am entering it in the following query:
ERName Group By Department Group By Date of Call-In Group By Type of Call-In Group By Count:ERName Count
The other thing I tried is just doing another query based on the above query and doing a totals (sum) line on Count:ERName. That doesn't work, it prompts me for count (as if that is a parameter I want to choose.
I am still learning access- I am hopeful someone does not mind helping me...no matter how easy or simple this may be. I will do my best to describe what I am trying to do.
I am creating a system admin database- starting from scratch and am willing to start over.
I would like to create 2 forms that work directly with the appropriate tables.
Table 1 (host/IP): Hostname IP address User - uses a drop down already created System - uses a drop down already created
Table 2 (system updates): Action- uses a drop down including install, remove, etc... Software- uses a drop down to select from including virus, system updates Date updated Comments
Form 1: -new record button (to enter new hostname record and IP associated) -find button -save button -Exit (database button) -Delete Record button) -hostname -IP address -user (drop down to change user if necessary) -System (drop down to select laptop, workstation, etc.) -"Add/update patch" button (which I would like to link to form 2 showing only the records for the selected host/IP on form 1) - "view all updates for this system" button (within last week or month)
Form 2: - Clear or undo button (which will undo any field entered by mistake on this form) - Save button - Exit (to main form) button - Hostname field which matches what is on form 1 - user field which also matches what is on form 1 - Next, there are four separate lines (as seen below)- each with the appropriate links to table 2. The purpose of the four lines is if the admin has four separate updates to be entered, they can do it on one page, click save and each separate will be saved and accociated with that hostname, IP. 1. Action - Software - Date Updated - Comments 2. Action - Software - Date Updated - Comments 3. Action - Software - Date Updated - Comments 4. Action - Software - Date Updated - Comments - System Admin field - this is to show who did the updates for the selected hostname/IP
Now, I seem to be okay with form #1 (except the "view all updates..." button) Form #2 is giving me a fit. I seem to be having problems populating all the hostname records that have been entered. Also, the four lines are not working independently.
Is it possible to have the date update field automatically update when action or software is updated/selected.
Perhaps I have the second form set up wrong due to the relationships or primary keys or I just did not configure the form correctly.
Sorry for the long message- I hope someone can help straighten me and/or this database out.
I purchased a copy of Office 2003, on Ebay, for less than half the price it normally sells for. I created databases, which work well on my computer. However, when I copy them over to the office computer from my computer, via Flash memory stick, the program does not run. The message seems to indicate a problem with registration. When I first installed Access, it asked me for a registration key, which I did provide. After that it worked-no activation process. Does this mean that you can only use the computer you created the database on to run it, or that I do not have a proper version of Access 2003? I cannot transfer, from my home computer, to the office computer. Is this Microsoft paranoia again? Subsequent uninstalls and reinstalls of the program did not ask for the installation key. What should happen? I would like to resolve this major problem and your help will be greatly appreciated. The program is unreliable. Also, Switchboard does not work-must use option box instead.
Time for you form experts to lend a hand.... what I have is 2 tables with queries based directly on them with no criteria and forms directly on the queries.
custtbl CustID ----Text-Primary Key Custname--Text-non essential just identifies customer
equiptbl CustIDFK - Text-Foreign Key EquipID -- Text -Primary Key EquipDesc-Text -non essential just identifies equipment
qucust CustID ----Text-PK Custname--Text
quEquip CustIDFK - Text EquipID -- Text -PK EquipDesc-Text
fmEquip CustIDFK - Text EquipID -- Text -PK EquipDesc-Text
On the button for fmcust, I have the following code: Code:Private Sub Command7_Click()On Error GoTo Err_Command7_ClickDim strid As Stringstrid = Me.CustIDIf IsNull(DLookup("custidFK", "equiptbl", "custidfk =' " & Me.CustID & " ' ")) Then strQuery = "INSERT INTO equiptbl(custidfk) Values('" & strid & "');" DoCmd.SetWarnings False DoCmd.RunSQL strQuery DoCmd.SetWarnings TrueEnd If Dim stDocName As String Dim stLinkCriteria As String stDocName = "fmequip" stLinkCriteria = "[CustIDFK]=" & "'" & Me![CustID] & "'" DoCmd.OpenForm stDocName, , , stLinkCriteriaExit_Command7_Click: Exit SubErr_Command7_Click: MsgBox Err.Description Resume Exit_Command7_Click End Sub Now, for an existing record, this pulls it up fine, but if the record doesn't exist, then I want it to create a new record in the equiptbl and pull it up in fmequip. I've done some error checking, and everything works fine up to the insert to part of the vba. I have a feeling it's something simple, but I'm too much into it to look at it objectively. (I've had a lot of distractions all weekend - dogs, kids, roommate, roommate's kids, I just want to punch anything that makes noise, lol)
Here is the sample I'm working with. Record 1 from the customer table is included in the equipment table, record 2 is not.
Edit: As it is, if the record doesn't exist in equiptbl, it pulls up the form with an entirely new record, everything blank, but I want the customer ID to populate the custidFK field of the equiptbl if the record doesn't exist in equiptbl (sort of autocompleting part of the new record if you will).
