If, by new record, you mean a record that has not yet been created, then you can not use UPDATE. UPDATE is for modifying a field(s) in an existing record.
You could do INSERT INTO which in Access is an APPEND query.
If you really want to use UPDATE, you will have to create the record before you try the UPDATE query.
Also, Number is not a good name for a variable of field. I'm surprised you didn't get some error based on a reserved word. issue.
Jdraw, Thank you. I agree all of my variables have unconventional names and should be renamed. This is because I have not programmed in quite a while and this started as a proto-type. It has migrated to what I want so it is time to overhaul.
Anyhow, you have nailed it. This record has not been created yet. In fact, it is the current record in process so insert is probably what I need; However, the insert command , I thought, was used to create new records, Can it be used to write to a field? Any suggestions is appreciated.
Ok, we are talking about the same thing. This helps: and I mis-spoke. The record has been created and is currently open.....I think . I mean the VBA code is called AfterUpdate of the second field of a form of four fields. So I imagine the record is still open. While in the VBA code the user will be prompted to enter a quantity.
This quantity information needs to be written to a field to this same open record but to a field which is not displayed on the form. The information below but it will add a record and we do not want to do this, correct.
Insert Data Only in Specified Columns
It is also possible to only add data in specific columns.
The following SQL statement will add a new row, but only add data in the "P_Id", "LastName" and the "FirstName" columns:
INSERT INTO Persons (P_Id, LastName, FirstName)
VALUES (5, 'Tjessem', 'Jakob')
That statement is the correct syntax, but you need
Currentdb.execute yourSQL,dbFailOnError to execute the statement.
After the execute the record will have these data values.
If P_Id is an autonumber data type, you do not need to include that field. If it isn't autonumber, then carry on. The field that is not visible can still be inserted.
If the value is available, then it could be included in the INSERT statement.
I think we're getting close, but I still don't know the details regarding the Quantity.
Yes, we are getting close but what are you saying will work? The example I provided above came from the link you forwarded me. Unless the insert command will work, scratch that info. From what I understand this code below works because the record set of the Material Table is closed when the VBA code is called:
Code:
NumResult = result - Number ' subtract Quantity needed from quantity available
strSQL = "UPDATE Materials SET Quantity = " & NumResult & " WHERE Material_ID = " & Me.cboMaterial_ID
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
Likewise this statement will not work, as it did on Materials because the record in the allocation table is currently open.
Code:
'strSQL = "UPDATE allocation SET QTY_Allocated = " & Number & " WHERE Allocation_ID = " current.record
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
Are we on the same page?
The fields of the allocation table are the following:
ID, material_name, Employee_name, Date_issued, Date_returned, QTY_Allocated
The sixth field QTY_Allocated is the one I want to insert the value " number". Which BTW is available and known.
Ok I have it. Can you tell me explicitly what I should be looking at and what doesn't do what you want. And what you want to happen.
Your help is very important to me and I obviously need it. They shut the gates here at 5:15 and all employees must be out by then, If you would like me to pay you for your help, I can work something out with my supervisor. I really appreciate it.
In any event, please allow me to provide a short explanation of my vision on this DB and table relationships:
Explicit Explanation:
The global function of this DB is to assign materials to employees. Therefore, I have a table of employee with pertinent information and a table of materials pertinent information. The intersecting table "allocation", allows me to choose materials and choose employees and relate them to one another. Some key points which influenced my design are:
1) Many of the materials have large quantities and it is not necessary to track them individually. These are all the items not marked secure in the materials table and therefore, I chose to simply use the "quantity on hand" inventory control method. For unsecured items, it is only important to know how many of this materials an employee has and how many was returned. There is no problem getting the quantity from the material table then, updating that quantity using the Combo Box. Thats what this statement does:
Code:
strSQL = "UPDATE Materials SET Quantity = " & NumResult & " WHERE Material_ID = " & Me.cboMaterial_ID
But next, I am having a problem. in the allocation table ( which of course is the current record I am working on) updating the field "QTY_Allocated". The statement below fails because I do not know how to point to the "QTY_Allocated" of the current open record:
Also when I escape or cancel the entry I am getting and error and need to know how to handle cancelling the Update.
Code:
strSQL = "UPDATE allocation SET QTY_Allocated = " & Number & " WHERE Allocation_ID = " current.record
Finally, some materials are valuable and each time one is checked out, a single record must be created in the allocation table. These items are marked secured and when selected, I prompt for a serial number and only allow the user to check one out at a time for this item. This is the if statement in first line of the code.
I just looked at your table and relationships.
Your junction table allocation should have the id from materials and employees (Not the names) OK I understand. So break the relationships and redo with employee ID and Material_ID linked to Allocation_ID?
You may have some hidden entities in your Materials table. Each material has only 1 Location? This is strange. Do you really only see one location? Which one is it? Something must have happened. Below is the items list that should be under the materials table field location. I see all locations on tables, forms and reports.
Not quite sure I understand. Where do I use the looups then? Many resources say lookup fields are great and to use them and some say they are bad. Do you suggest creating tables and relationships for these lookup fields?
How come all the Last names are Helper1? The employee last name were all changed to helper1, the forman was all changed to manager and the employee number changed to hide confidential information.
Ok, thanks for the clarification. First, people here are not looking to be paid. We give advice/suggestions as best we can; and sometimes they conflict with one another.
As for lookups - the general recommendation from people with a database background is do not use lookups at the table field level. This "feature" was introduced by M$oft but is considered poor practice with many pitfalls. The recommended approach is to create a separate table (often called a Lookup table) and use that table to get the appropriate value.
For Example: Suppose you have a database dealing with Projects where each Project can have an ID,a Name, a Manager, and a Status as of certain Dates.
And suppose a Status could be any of Concept, Initialized, Authorized, In Progress , Cancelled or Completed.
You could have a table called tblLkupStatus with fields
StatusID (PK)
StatName text
valued as
1 Concept
2 Initialized
3 Authorized
4 In Progress
5 Completed
6 Cancelled
In the TblProjectStatus you
would use the StatId as (FK) to relate to tblLkupStatus. So in a query, you could have a query with 3 tables. tblProject, tblProjectStatus and tblEmployee.
tblProject may be related to tblEmployee (to get the Supervisor details), and to tblLkupStatus (to get the StatName)
That's fine with Helper1, I understand the confidentiality.
For Location I saw the field defined within the Material table. If you have a list of Locations, you may put those locations in a tblLkupLocations along the same line as my Lookup table example, then just use the LocationID in the associated tables.
What does conex_1---- conex_8 represent? Looks to me that it might be part of a separate entity.
Does someone have to enter these values manually ?
Post your updated database as you progress and have questions.
Good luck
Understand about the pay and appreciate it. Conex 1...8 and all the stuff in that lookup are locations on the property where the materials are stored.
A Conex box is a large steel container typically seen at shipping ports and on trains.
I will get rid of the look-ups and replace with tables and continue to post revised DB's. In the meantime, do you have an answer for writing the "QTY_Allocated" information? I.e., should I close the record and use the strSQL statement as shown below:
Code:
'strSQL = "UPDATE allocation SET QTY_Allocated = " & Number & " WHERE Allocation_ID = " current.record
