Now I have created the relationships between the tables for the various ID's. My issue is the following:
I will be automating this process, so when the data is imported to the various tables, I need the relevant keys from tbl_1 tbl_2 and tbl_3 to be added to tbl_4 when the new data is brought into the tables.
As to the process of importing the data, I was looking at using PHP to facilitate this. Any ideas, help or direction would be greatly appreciated. I have looked around the net for an idea of how to solve this, but, being so new, I am not exactly sure how to word the question.
My father tables contains all the relevant fields with info of my products (which are listed in records/rows). My sister table contains less fields but are all present in the father table. The info is imported from excel daily into the sister table - all the fields here change regularly except for the unique ID which is present in both tables. Because the sister tables' information for the records/rows changes, it is difficult to update the selected fields of the father table.
I want to update selected fields within my father table based on the information I upload from the excel sheet (which becomes the sister table when uploaded)
Ok, I am getting well excited about what Access can do, and how the database I'm working on is coming along. It's the things that you guys find simple that I'm having problems with. So my question is:
I have fields called InitialAssessmentDate and ReviewDate. I want to be able to have a bold coloured label appear on the front page of a client record informing a user (when they access a clients record) when the client is due a review - usually three months after the Initial Assessment Date/and/or three months after the Review Date.
So far I have a couple of bold coloured labels that appear when individual client records open to advise users of other important stuff such as whether the client is high risk (for personal safety) or if the client needs a interpreter or signer: The code used for these two beauties is:
"Private Sub Form_Current() Me.HighRiskLabel.Visible = Me.High Me.InterpreterLabel.Visible = Me.Interpreter End Sub"
Is it possible to do something similar for the date calculation?
I have a few questions about tables and forms joining, and auto-population of data.
If I join all 8 of my tables one to one to one to one to one, is that possible? That is my first question.
Secondly: If I have my RSN number that I'm using to track each record, is it possible that if I type in the number on a different form, and two of the fields are similar on every form that they will auto populate since the tables are joined? And if they're filled out in one form, they should know that these were the fields we said were attached to this number it'll just appear? Is that a real thing?
I'm working on a database that is quite large. I foreign key that connect all the table is project_id. When I start a new project there a certain table that will not be used for quite some time. I would like them updated when I start the project with the same autonumber that is created for the project table.
There is an input section to add to the project and a view section to navigate through to view all sections of the project ir budget, financing, schedule. The navigate function wont work properly unless I have all project_id's updated.
I have a form "FrmMaintenance" based on the query "QryMaintenance" that is taken information from the table "TblMaintenance".
In the form, I have to pull the serial number of a machine from a drop down list which works perfectly fine in the form and it also updates other associated records.
When I go to the table, it puts associated number instead of the serial number that I've pulled.
Same thing happening for the pull down list "Engineer".
I've tried to look at the format of the "SerialNo" and changed to text, then to Combo Box but it didn't work, and the same for "Engineer"
Does a (the) .ldb file have to be closed for another user on a network (separate FE linked to network drive BE) to read/write info to a table? Or even select info from a table?
I have the following code working great with creating new contacts in Outlook from Access. My problem is I can't figure out a way to update already existing contacts in Outlook.
Function AddContacts()
Dim OutlookObj As Outlook.Application Dim Nms As Outlook.NameSpace Dim MyContacts As Object Dim MyItems As Object Dim MyItem As Object Dim Db As Database Dim Rst As Recordset
Set Db = CurrentDb Set Rst = Db.OpenRecordset("CustomerOutlook", dbOpenDynaset) Set OutlookObj = CreateObject("Outlook.application") Set Nms = OutlookObj.GetNamespace("MAPI")
'to point to a nested folder in Outlook you have to create folder items in folder items: Set MyContacts = Nms.Folders.Item("Public Folders").Folders.Item("All Public Folders").Folders.Item("Our Contacts") Set MyItems = MyContacts.Items
While Not Rst.EOF 'Declare which form to be used to add your contacts Set MyItem = MyItems.Add("IPM.Contact")
'add fields, to find out which fields are available take a look at all the available members of the 'ContactItem class in the Outlook object library. 'Be careful, Microsoft is using various names for outlook fields! MyItem.CompanyName = Rst!BusinessName MyItem.FirstName = Rst!CFirst MyItem.LastName = Rst!CLast If Not IsNothing(Me.WebSite) Then MyItem.WebPage = Rst!WebSite End If If Not IsNothing(Me.CMiddle) Then MyItem.MiddleName = Rst!CMiddle End If If Not IsNothing(Me.Suffix) Then MyItem.Suffix = Rst!Suffix End If MyItem.JobTitle = Rst!JobTitle MyItem.BusinessTelephoneNumber = Rst!Phone MyItem.Business2TelephoneNumber = Rst!BackPhone MyItem.MobileTelephoneNumber = Rst!Mobile MyItem.BusinessFaxNumber = Rst!Fax MyItem.Email1Address = Rst!Email If Not IsNothing(Me.Address1) Then If Not IsNothing(Me.Address2) Then MyItem.BusinessAddressStreet = Rst!Address1 & ", " & Rst!Address2 Else MyItem.BusinessAddressStreet = Rst!Address1 End If End If MyItem.BusinessAddressCity = Rst!CCity MyItem.BusinessAddressState = Rst!CState MyItem.BusinessAddressPostalCode = Rst!PostalCode MyItem.Categories = Rst!CustomerType MyItem.FileAs = Rst!BusinessName
'MyItem.Etc = Rst!Etc MyItem.Close (olSave) Rst.MoveNext Wend End Function
I have a query that i use to update dates relating to jobs and at what stage of development they are in. It worked fine until I started to pull in some data (Prism) to update the user when the project is planned in to sample and when mass production starts (the field this is displayed in is not enabled). when I've incorporated this it I have no longer been able to update any field and my query has turned read only.
In the code below I am creating a record with the INSET INTO statement and then Updating with additional info in various fields. it is not working the way I thought it would, so I am trying to create the record in it's entirety.
Code: SQL_Grade_GUSD_ID = "INSERT INTO Grades (GUSD_Student_ID) VALUES (" & Me.GUSD_Student_ID & ")" SQLM1_1_ELA = "UPDATE Grades SET Grades.Subject = ""BM1(ELA)""" SQLM1_2_ELA = "UPDATE Grades SET Grades.Type = ""Exam""" SQLM1_3_ELA = "UPDATE Grades SET Grades.Score = ""0""" SQLM1_4_ELA = "UPDATE Grades SET Grades.Nam = ""GUSD BM-1""" DoCmd.RunSQL SQL
[Code] ...
I am running to syntax problems when I try to USE the INSERT INTO to create the record with all the info in one statement.
Code: SQLM1_1_ELA = "INSERT INTO Grades ( GUSD_Student_ID, Subject, Type, Score, Nam ) " & _ "SELECT (" & Me.GUSD_Student_ID & ")"" AS GUSD_Student_ID, ""BM2(ELA)"" AS Subject, " & _ """Exam"" AS Type, ""0"" AS Score, ""GUSD BM-1"" AS Nam " & _ "FROM Grades"
I am Getting this error:
Syntax error (missing operator) in query expression '(12345)" AS GUSD_STUDENT_ID, "BM2(ELA)" As Subject, "Exam" AS Type, "0" As Score, "GUSD BM-1" AS Nam From Grades'
I have an Access 2010 report that is pulled from a dedicated query. When i run the report, one of the fields pull the information in both the numerical ID number and the Field information. For example, the field in question is a field for "business type"......the report comes back in some lines as Manufacturer, Supplier, distributor, or contract. In other lines it comes back as either 1, 2, 3, 4...which is the ID number form the table. I have gone through all the properties and data source and can not figure out my the information is being returned so differently.
I need to make a query work so that when someone enters info into a master form, it will pull the data for that job onto other forms when the job # is entered. Thanks for any help.
I'm having an issue with the database being read only and a type mismatch due to OpenRecordset. I know this database use to work when it when it was just
CurrentSerNum = RecSet(0) +1 was doing the incrementing.
I need the database to increment in a different way now.
Q=manufacture location 1st number is new=0 or used=1 2nd and third number =82 4th is single digit year aka 2015 = 5 6 and 7th are work week aka 25 for this week 8,9,10 are incremental numbers for the units made that workweek, so 001,002,003. Then week 26 starts the increment starts back at 001.
Code: Private Sub MakeThoseProducts_Click() On Error GoTo Err_MakeThoseProducts_Click Dim NumericSerial As Double Dim WorkWeek As String Dim WorkYear As String Dim WorkWeekNumber As Integer Dim WorkYearNumber As Integer Dim Location As String
I believe I may have asked this question before but…
I have a FE/BE database. Is there a way to set the tables as “read only”? I’d like the FE to be read only, but allow users to see all the tables, not just forms etc. However, if they open the BE, they would have full control.
Ok, what i have is a form on which a user has an Option Group for selection on which records are shown (consumable and Non-consumable), also on the form is a combo box which reads from Table Asset Categories. However what i want to do is allow this combo box to read from another table depending on which option is selected in the Option Group. ie change its Table and row source, but i can't figure this out. I thought something in the region of:
If Me.OptionGroup = 0 then recordsource = "Asset Categories" Else RecordSource = "Consumable Categories" End IF
Hey all, I need data in a table to be read only, once its inserted no one will be able to modify with the exception of a admin, how can i go about doing this/.
Ok, what i have is a form on which a user has an Option Group for selection on which records are shown (consumable and Non-consumable), also on the form is a combo box which reads from Table Asset Categories. However what i want to do is allow this combo box to read from another table depending on which option is selected in the Option Group. ie change its Table and row source, but i can't figure this out. I thought something in the region of:
If Me.OptionGroup = 0 then recordsource = "Asset Categories" Else RecordSource = "Consumable Categories" End IF
I have a Workorder table and a Workorder form. I would like to be able to make a Workorder closed when it is finished so that it is read only. I have a finished date field so maybe by entering a date there could be a way to accomplish this?
Hello! My Small access aplication has a tables appended from dbf database program. I am coping with an issue to set access to this tables as read only.For me its very vital that source dbf application won't be changed from MS Access.Thanks for help!
I have a main computer where my Access Application runs but I also want other computers with the application runnign on it but linked to the backend database on the main computer, however when linking to the tables they are only read only.
How do you link to tables accross the network so you can read and writ e to them?
I want to be able to open a Table as Read Only, so that a user can browse etc., but neither alter nor enter data - I want them to use the Forms for that. How then, using VBA code, do you open a Table as Read Only?
It seems that there are a few different ways to get info from the tables when developing an applications that i know of:
1)queries 2)dao 3)ado 4)selects without any of the above
I lean towards the last 2 but I need to know this: is there any advantages in using ado as opposed just sql?
For instance i ran this sql:
Private Sub Command0_Click()
Dim SQL As String Dim strCriteria As String
strCriteria = Forms![form2]![Text1]
SQL = "SELECT * FROM Table1 WHERE (((Table1.clinic)='" & strCriteria & "'))"
DoCmd.OpenForm "frmClinic" Forms![frmClinic].RecordSource = SQL End Sub
it worked fine. I could see how a beginner would prefer to use queries over sql but I would prefer to use sql in vba. I've read that dao is older and I should use ado instead. So that leaves ado vs. the way I displayed it in the above code. So which way should I go? If I'm over looking queries or dao in favour of ado/sql, just point out their advantages if you don't mind.
The database I'm working on is coming on nicely, thanks to some valuable help from this forum. But I've got a couple of new problems that I just can't seem to get my head around. Really hope someone out there can help!
So...
I have two tables (well, there are more.. but there are two main ones with the important data on them). The first is a list of Customers (you know, the usual Name, contact, telephone etc...). The second is a list of jobs for each customer.
After starting from scratch, I created a nice looking form wth control tabs that on one page shows you the customer information and on the second; the job history for that customer. I then have a third tab which lets you add new jobs. So I'm all chuffed because that is the basics of what I wanted it to do.
However, I need a hard copy of the job report to print out and give to an engineer to fill in or to print in future should a customer wish to see it.
Try as I might, the reports function didn't look as if it was something that could be "designed" the way I wanted it to look. So, I figured another form was in order.
I started by building a query which included all the fields from the two tables mentioned above in it; I.e. So it would pull up a Job Printout by a workorderID number. That way it would show all the company info PLUS the detail of that one particular job.
Problem is this: I can see from the query in table view that the Jobs are listed; but alongside them is a straight listing for all the companies in the database. Basically, the Company who received the job in question is not being shown by the query.... if you follow me. (Apologies if I'm explaining this like a fool).
Question 1 then... is how do I fix this? Is this something to do with these arcane relationships things?
Question 2: How do I create a button to print that one "form" by workorder ID.
Question 3: Did I do the right thing by using a "form" or is there a better way to create a "report" for printing that can be formatted the way I want (with logos and stuff)
