I have always had trouble with this and no matter how much I read I cannot get a visual picture of how to do this.
So if any of you could show me how this can be done. It would be appreciated more than you can ever know.
A company has many employees.
Each employee can have many certificates.
So how do I set up the tables and the RELATIONSHIPS(this is the part I can't understand), so when I create a form and enter the different types of certificates they all go to the right table?
This is what I have so far.
Employee TABLE
EmployeeID(PK)
EmployeeFirstName
EmployeeLastName
EmployessAddress
EmployeeCertificateOne
EmployeeCertificateTwo
EmployeeCertificateThree
EmployeeCertificateFour
etc....to
EmployeeCertificateEight
Could someone explain in detail how I can do this?
I really would like to understand this.
I'm sure it is simple but for some reason I just can't get a visual picture in my head on how to do it.
I'm a visual learner, I have to see it work and then read how it works to fully understand how something works.
Thanks in advance.
P.S. I forgot to mention that I need DATE fields for the Employee Certificates because they expire after a certain time.
I was not sure where to put those.
I designed a form to provide options for a group of reports. Each option when clicked, runs a specific report. On my machine when I created and ran the form, worked very well. This database is on a shared network drive with few other people having access to it. When these other users opened this form (they don't have problems running ANY other form), they got errors such as "Undefined Format function in the expression". I checked on their machines, the Access version and permissions of the database (I haven't set any special permissions on this database at all). It is same as mine. Please help me. Why is this happening and what should be done to avoid it? The database opens in a default shared mode.
I'm really sorry to bother with my stupid questions, but I'm definitely not a programmer, and after reading about Access, SQL and ASP so much, I feel really confused...
I've already explained what I want to do in the SQL server forum. It's for an experimental project, I need a database that keeps only 100 records, and every new entry of data replaces the oldest record. So in the end there are always only 100 records.
Well, Lauramc nicely gave me this code that should do that:Code:CreateTrigger YourTable_Trigger1On YourTableAFTER INSERTAS DECLARE @OldestID intSET @OldestID = (SELECT MIN(YourTable.ID) FROM YourTable)--Table name is YourTable, with a primary key of ID.IF (SELECT COUNT(YourTable.ID) FROM YourTable) = 101 --You can also use @@RowCount to get the number of rows.BEGIN DELETE FROM YourTable WHERE YourTable.[ID] = @OldestIDEND
But all I know is how to do is put some simple SELECT statements in an ASP page to call a csv database.
I've realised that I might need to use Access but I don't understand anything.
- Should I use Access and if yes, where am I supposed to copy this code? Should I bring any modification to it?
- Apparently it's possible to create a database from SQL, does it mean not using Access? Where should I type the code? In what sort of file?
Please please, can someone tell me it's actually very simple?
I am now right at the last fence in what has been an "interesting" assignment! Sending an attachemnt from Access through winfax!
I have found the following excellent code but, the problem is that the documemnt appears in a "preview" kind of pane, I just want it to go ahead and send! Everything else works fine
Any sugestions pls?
Many Thanks Andy
Public Function SendFax()
'On Error GoTo ErrorHandler
Dim strRecipient As String Dim strFax As String Dim strattach As String
'Test for required fields strFax = Forms!frmFinanceProposal!Text1077 If strFax = "" Then MsgBox "Please enter a fax number" GoTo ErrorHandlerExit End If
'Start DDE connection to WinFax. 'Create the link and disable automatic reception in WinFax lngChannel = DDEInitiate(Application:="FAXMNG32", topic:="CONTROL") DDEExecute ChanNum:=lngChannel, Command:="GoIdle" DDETerminate ChanNum:=lngChannel
'Create a new link with the TRANSMIT topic. lngChannel = DDEInitiate("FAXMNG32", "TRANSMIT")
ok I have an access db which is all on access. the dbase has been operational now for 3 years and working well however, do to business needs and changes I'm trying to figure out a better way to collect and share data. So the backend of the database is stored on our local office server when users log on to enter or review data they must be in the local office. We now had the need to access the database remotely from customer sites. Now the way this works is we use a local dialer to connect to our company's network and then are routed to our local server (you can imagine how slow this is) it is impossible to operate the current database this way. So I have been reading different posts and different options (front page, sql, asp etc.) What I would like to do is convert my dbase to a program which can allow input and review of data both remotely and locally without a huge speed loss. What is the best approch for me?
I created a db, tested it with dummy info and all was fine. I then imported live data from another db. When I came to add new records, discovered that, in the table design, a default value of 0 (zero) has been entered for fields where a look up table is used (where I had used Autonumber for the ID). Solved that easily enough, but later I amended a text box to a combo box and the same thing happened. Why does it do this?
With rstComment .AddNew ![CommentNo] = DCount("CommentNo", Me.OpenArgs, strCriteria) .Fields(strItem) = nNumber ![ProgressionID] = Me![cboProgressionID] ![Comment] = Me![txtComment] ![PersID] = 0 'Will be changed by SQL Server ![Date] = Now() 'Will be changed by SQL Server .Update .Close End With
This code is used in a form that is accessed via a main form when a progression is selected. The form that opens allows the user to selct a type of progression and to enter a comment. This data is then transferred to a table.
This form is used elsewhere in the database and works fine. The code is exactly the same. The form name is different and the table it points to is the same design of table as the other but obviously named differently. i am confident it is referenced correctly. But you lot are the experts.
I am not trained in VBA and have only just started to understand bits of it.
Action: 1) Update the blank fields in a table + add newly imported records but...make sure that there are no duplicates. I need to Append the records from a table called: XLS_Imp_11_27_07 to my main Table " Invoice Tracking for A/P 10_30". The fields found in the XLS _ table: Release Dt, Entry Dt, Liquidation Dt may have been populated by a live report found on Internet. Since it's a live report, when Appended, to the other table, it will create numerous duplicates. The fields found after the 7 first fields in the Invoice Tracking Table may have been updated by users
Is there a way to avoid duplicates and get the data from one table to the other without wiping out whatever is already filled-in? :(
i'm writing an Access database to quote for windows & doors, there are many variants like style, width, height, glass types, security specifications and about 4 other options.
what would be the best way of working out how to add the extras for each option,
The price would be based on the style first, then depending on width and height, then on what options were ticked . i.e securtity spec yes/no, then on what glass type, the price gfor that would be based on the entered width x height etc.
is it possible to do a look up, like you can in Excel where it looks at at grid/matrix based on style number and width x height to get the price, but then how would i get it to add the other options which will depend on size and number of openings.
I am completely new to Access but probably all I will need is a gentle push in the right direction. I'd like to think I'm fairly quick at learning new things. Thank you in advance, here's where I am, (first step anyway):
Table 01 Parts List ID - name - cost
Table 02 Item List ID - nick name - description
Table 03 Items to parts relationship (this is where I fall apart) ID - Item ID (relationship to table 02) - Item nickname - PartID (relationship to Table 01) - Part name - Part quantity.
What I am trying to do: I resell items in table 02 which are built of various parts from table 01. I need to generate a table that says: Item 1 is built from 4 of part 1, 2 of part 2, 9 of part 3, and the total cost of parts for Item 1 is x. I want to update the parts list cost on a regular basis and have the item price change reflected.
If I can understand how to make this work, I can ultimately add in labor and overhead etc etc. Baby steps for now.
I am using Access 2003 in a simple database and want to add digital pictures of each item in my database.
Before trying to enhance the database with pictures; the various fields are filled with either descriptive text or numbers. The size of the mdb database file is 464k.
I recently wanted to add a picture of each database entry. After adding a picture that is only 300k in size the mdb file increase to 18MD. Then after adding another picture also of 300k bytes my database grew to 25MB.
In the drop down menus Under Tools - Database Utilities - I ran the Compact & Repair Database. This did not reduce the size.
In the database Form I am using a Bound Object Frame to hold the picture and yes I am not linking the picture I am including it in the database. But, would only expect it to increase the database size by slightly more than the picture size.
I should also point out that the only way I have found to get the picture to be visible (not just an icon of the type of picture file) is to right click on the Bound Object Frame scroll down and choose Insert Object. Click on Create New and select Adobe Photoshop Image. This open adobe photoshop I make sure the picture properties are creating a picture of 300k.
So my question(s) is again. Why is my database increasing in size so rapidly? Is there a better to insert picture into a database?
Hi, I have found the answer to most of my problem, thanks to previous posts. I am using the Undo command on a Date field. However it seems that my code is maybe not staying on the event to action the undo.
After it runs the Focus goes to the next field Me.LeaveDays and the value is automatically entered, unless I comment back in the Goto's in which case the value will be zero.
I want to be able to Undo the EndDate (and preferable the StartDate, and Setfocus to StartDate) EndDate will do if not possible.
Don't want to Undo the whole form, although that could be a last resort.
My code is in the before_update event, image posted (ignore faded fields, not visible/needed in final solution) Private Sub EndDate_BeforeUpdate(Cancel As Integer) On Error GoTo Err_EndDate_BeforeUpdate
Dim intLeaveLeftAL As Integer Dim intLeaveLeftSL As Integer Dim intLeaveDays As Integer Dim dtNullDate As Date Dim varNullField As Variant
' Use the WorkingDays function to calc the number of Leave Days ' set LeaveDays and requery the form intLeaveDays = WorkingDays(Me.StartDate, Me.EndDate)
If Me.LCode = "LC41" Then ' Sick Leave
Select Case intLeaveDays Case Is > intLeaveLeftSL MsgBox "Employee does not have enough Leave" & vbCrLf & "to take as Sick Leave.", vbCritical + vbOKOnly + vbDefaultButton1, "Entry in Error" varErrorCondition = True Me.EndDate.Undo GoTo Exit_EndDate_BeforeUpdate Case Else: End Select
ElseIf Me.LCode = "LC11" Or Me.LCode = "LC13c" Then ' Annual or C/Fwd Leave
Select Case intLeaveDays Case Is > intLeaveLeftAL MsgBox "Employee does not have enough Leave" & vbCrLf & "to take as Annual Leave.", vbCritical + vbOKOnly + vbDefaultButton1, "Entry in Error" varErrorCondition = True Me.EndDate.Undo GoTo Exit_EndDate_BeforeUpdate Case Else: End Select End If
