By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,261 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

Inserting into a table with 4 fields but only 2 fields need to be updated

100+
P: 106
I have a table called Applications which had 4 Fields and they are:
ApplicationID --- this is auto numeber and Primary Key -- can't be duplicated
Application
ApplicationOwner
Responsible

Now I have the following record im My table

ApplicationID Application Owner Responsible
6 Budget Model Anthony Gust Unknown


I have two Questions
One
The Owner and Responsible person has changed, I need to update the table, How can I do this?

Two
How can I add new info into the table

Thanks
Dec 17 '06 #1
Share this Question
Share on Google+
4 Replies


PEB
Expert 100+
P: 1,418
PEB
I hI have two Questions
One
The Owner and Responsible person has changed, I need to update the table, How can I do this?

Two
How can I add new info into the table

Thanks
Maybe you want to update the table using Query? Orby the interface of a table or a form? If u using interface like a form u can Access the respective fields easily and change them!

If u need to do it programatically using SQL

docmd.runsql 'UPDATE Applications SET Owner='New One', Responsible='The best' WHERE ApplicationID=6;"

A similar thing u can do for insert a new information into the table.

U can construct your Append query using the query designer... Than passing to SQL view get the code and store it in VB!
Dec 17 '06 #2

NeoPa
Expert Mod 15k+
P: 31,615
Of the options PEB has outlined, I think a form is probably the best for you.
The easiest way to create a common form type like this is to use the Form Wizard which should pretty well do the whole job for you.
Let us know if you have any problems with this.
Dec 17 '06 #3

100+
P: 106
Of the options PEB has outlined, I think a form is probably the best for you.
The easiest way to create a common form type like this is to use the Form Wizard which should pretty well do the whole job for you.
Let us know if you have any problems with this.
Hi NeoPa

I have created a form but it doesn't let me add anything just shows what ever is available.
The thing that I have done is too complicated to explain.
I will start from the very beginning that I have set up the tables and then move through to the stage I have difficulty with.
Please be patient

I have 6 tables and they are:
Expand|Select|Wrap|Line Numbers
  1. Server  ---- Table
  2. FieldName          DataType
  3. ServerID                 AutoNumber    (PK)
  4. Server                     Text
  5. ServerDescription     Text
  6. Location                  Text
  7.  
  8. Applications  --- Table
  9. FieldName        DataType
  10. ApplicationID     AutoNumber         (PK)
  11. Application         Text
  12. Owner                Text
  13. Responsible       Text
  14.  
  15. Departments  ---Table
  16. FieldName         DataType
  17. DepartmentID     AutoNumber        (PK)
  18. Department         Text
  19.  
  20. Contacts   --- Table
  21. FieldName       DataType
  22. ContactID         AutoNumber           (PK)    
  23. Contact             Text         
  24. Mobile            Text     
  25. Extension          Text
  26. ContactType     Text     
  27. DepartmentID   Number
  28.  
  29. ApplicationContact  ---Table
  30. FieldName          DataType
  31. ApplicationID       Number     (PK)               
  32. ContactID              Number     (PK)
  33.  
  34. ServerApplication  ---Table
  35. FieldName          DataType
  36. ServerID              Number     (PK)
  37. ApplicationID       Number     (PK)               
  38.  
  39.  
The Relationship between them is as follows:
There are many to many relationships between Server and Application, and many to many between application and contact tables. There is one to many relationships between Department and contact.

Above was the way that I have designed my tables.
I wanted to be able to view reports, update or add based on Server, Application, Department and Location.

I have created form called Mainwindow which has 4 List Boxes, ApplicationList, ServerList, LocationList and DepartmentList

ApplicationList Displays list of Applications only
I have set its row source to
Expand|Select|Wrap|Line Numbers
  1. SELECT Applications.Application FROM Applications ORDER BY Applications.Application;
ServerList Displays list of Servers only
I have set its row source to
Expand|Select|Wrap|Line Numbers
  1. SELECT Servers.Server FROM Servers ORDER BY Servers.Server;
LocationList Displays list of Location only
I have set its row source to
Expand|Select|Wrap|Line Numbers
  1. SELECT Servers.Location FROM Servers ORDER BY Servers.Location;
DepartmentList Displays list of Departments only
I have set its row source to
Expand|Select|Wrap|Line Numbers
  1. SELECT Departments.Department FROM Departments ORDER BY Departments.Department;
For each of these list I have button called “View” which open a reports for the selection from the list boxes (This section is fine)

The problem that I have is updating and adding new records.
I have tried to create forms via wizards but I can only see the view I can’t add new records.
???Can you tell me of an efficient way to add new records to any of this table???


I have created a form called “UpdateApp” based on query
Expand|Select|Wrap|Line Numbers
  1. SELECT Applications.ApplicationID, Applications.Application, Applications.Owner, Applications.Responsible, Servers.ServerID, Servers.Server, Servers.ServerDescription, Servers.Location, Contacts.ContactID, Contacts.Contact, Contacts.Mobile, Contacts.Extention, Contacts.Email, Contacts.ContactType, Departments.DepartmentID, Departments.Department
  2. FROM Servers INNER JOIN (Departments INNER JOIN (Contacts INNER JOIN ((Applications INNER JOIN ApplicationContact ON Applications.ApplicationID = ApplicationContact.ApplicationID) INNER JOIN ServerApplication ON Applications.ApplicationID = ServerApplication.ApplicationID) ON Contacts.ContactID = ApplicationContact.ContactID) ON Departments.DepartmentID = Contacts.DepartmentID) ON Servers.ServerID = ServerApplication.ServerID
  3. WHERE (((Applications.Application)=[forms]![MainWindow]![ApplicationList]));”
Once user select an item from ApplicationList and hit the button “Update” it will open this form.
For the chosen application you can see all the available information.
???I want to be able to update any thing for that application?
At the moment I have created only two textboxes “NewOwner” and “NewResponsible” to update the information in table Application, and used the following but it give me error and doesn’t update anything. The error message is “Object doesn’t support this property or method”

Expand|Select|Wrap|Line Numbers
  1. Private Sub AddNewOwner_Click()
  2. On Error GoTo Err_AddNewOwner_Click
  3.  
  4. Dim strSQL As String
  5.  
  6.     strSQL = "UPDATE Applications " & _
  7.     "SET Owner = " & Me.NewOwner & " , Responsible = " & Me.NewResponsbile & " " & _
  8.     "WHERE Application = " & Me.Application & ";"
  9.  
  10.   DoCmd.RunSQL strSQL
  11.  
  12. Exit_AddNewOwner_Click:
  13.     Exit Sub
  14.  
  15. Err_AddNewOwner_Click:
  16.     MsgBox Err.Description
  17.     Resume Exit_AddNewOwner_Click
  18.  
  19. End Sub
  20.  

That is all
I know it must be tiring to read all this but this may help you to help me.

Thanks
Dec 18 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
I have created a form called “UpdateApp” based on query
Expand|Select|Wrap|Line Numbers
  1. SELECT Applications.ApplicationID, Applications.Application, Applications.Owner, Applications.Responsible, Servers.ServerID, Servers.Server, Servers.ServerDescription, Servers.Location, Contacts.ContactID, Contacts.Contact, Contacts.Mobile, Contacts.Extention, Contacts.Email, Contacts.ContactType, Departments.DepartmentID, Departments.Department
  2. FROM Servers INNER JOIN (Departments INNER JOIN (Contacts INNER JOIN ((Applications INNER JOIN ApplicationContact ON Applications.ApplicationID = ApplicationContact.ApplicationID) INNER JOIN ServerApplication ON Applications.ApplicationID = ServerApplication.ApplicationID) ON Contacts.ContactID = ApplicationContact.ContactID) ON Departments.DepartmentID = Contacts.DepartmentID) ON Servers.ServerID = ServerApplication.ServerID
  3. WHERE (((Applications.Application)=[forms]![MainWindow]![ApplicationList]));”
  4.  
OK Tara

This query is not updatable. You will need to create a main form and subforms if you want to add or update records

Example

Main Form would be based on Server Table
Subform 1 would be based on Query as follows:

SELECT ServerApplication.ServerID, Applications.ApplicationID, Applications.Application, Applications.Owner, Applications.Responsible
FROM ServerApplication INNER JOIN Applications
ON ServerApplication.ApplicationID = Applications.ApplicationID;

The subform would be joined master/child to the main form based on ServerID.

This will allow you to add applications to a server, add a new server and applications or to edit any current records.

The other required subforms must follow the same rules.

Mary
Dec 18 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.