Would anyone be willing to help me debug my syntax? The below code statement test to see if an item has been marked "yes", Then checks the serial number is blank. If the serial number is blank will prompt for a number and write it to the record.
I am getting a compile error and am sure it is my syntax. I really appreciate any help I can get here?
Thank you
Code:
'Find out if the item is secured and get serial number if it does not exist
If Me![cboMaterial_ID].Column(3) Is Selected Then
If secured = Me![cboMaterial_ID].Column(4) = "null" Then
serialnum = InputBox(" What is the units serial number")
strSQL = "UPDATE Materials SET Unit_Serial_No = " & serialnum & " WHERE Material_ID = " & Me.cboMaterial_ID
End If
Else
End If
Hello back2basic, I am a bit confused here.. What are you trying to do here..
Code:
If Me![cboMaterial_ID].Column(3)[COLOR=Red][B] Is Selected [/B][/COLOR]Then
Have you confused it with .ItemSelected property? Or .Selected, if so.. Should it not be,
Code:
If Me[COLOR=Blue][B].[/B][/COLOR][cboMaterial_ID].Column(3)[COLOR=Red][B].[/B][B]Selected[COLOR=Blue](rowNumber)[/COLOR] [/B][/COLOR]Then
and here..
Code:
If [COLOR=Blue]secured[/COLOR][COLOR=Red][B] = [/B][/COLOR][COLOR=Red]Me[COLOR=Blue][B].[/B][/COLOR][cboMaterial_ID].Column(4)[/COLOR][COLOR=Red][B] = [/B][COLOR=Blue][B]"null"[/B][/COLOR][/COLOR] Then
Are you trying to see if it is null? If so..
Code:
If [COLOR=Red][COLOR=Blue][B]IsNull([/B][/COLOR][/COLOR][COLOR=Red]Me![cboMaterial_ID].Column(4)[/COLOR][COLOR=Blue][B])[/B][/COLOR] Then
Hello back2basic, I am a bit confused here.. What are you trying to do here..
Code:
If Me![cboMaterial_ID].Column(3)[COLOR=Red][B] Is Selected [/B][/COLOR]Then
Have you confused it with .ItemSelected property? Or .Selected, if so.. Should it not be,
Code:
If Me[COLOR=Blue][B].[/B][/COLOR][cboMaterial_ID].Column(3)[COLOR=Red][B].[/B][B]Selected[COLOR=Blue](rowNumber)[/COLOR] [/B][/COLOR]Then
and here..
Yes I am trying to see if is Null and Thank you, Your statement below worked
Code:
If [COLOR=Blue]secured[/COLOR][COLOR=Red][B] = [/B][/COLOR][COLOR=Red]Me[COLOR=Blue][B].[/B][/COLOR][cboMaterial_ID].Column(4)[/COLOR][COLOR=Red][B] = [/B][COLOR=Blue][B]"null"[/B][/COLOR][/COLOR] Then
Are you trying to see if it is null? If so..
Code:
If [COLOR=Red][COLOR=Blue][B]IsNull([/B][/COLOR][/COLOR][COLOR=Red]Me![cboMaterial_ID].Column(4)[/COLOR][COLOR=Blue][B])[/B][/COLOR] Then
If Me.cboMaterial_ID.Column(3, cboMaterial_ID.ItemsSelected) Then
If IsNull(Me.cboMaterial_ID.Column(4, cboMaterial_ID.ItemsSelected)) Then
serialnum = InputBox(" What is the units serial number")
strSQL = "UPDATE Materials SET Unit_Serial_No = " & serialnum & " WHERE Material_ID = " & Me.cboMaterial_ID
End If
End If
Thank you very much! It is all working (barely). I can not mix numbers and letters in the serial number and write it, I get a runtime error.
Would be willing to look at this code below and see if it makes good sense to you. If not I totally understand I am simply trying to get this part working.
The codes purpose is to first test if the item is secured by testing cbo_Material_ID.column (3) for true. If true, it will test for a serial number and prompt if not available.
Otherwise the item is not secured and prompt for the quantity desired and Update quantities as required
Code:
Private Sub cboMaterial_ID_AfterUpdate()
Dim Number As Integer
Dim NumResult As Integer
Dim result As Integer
Dim secured As String
Dim strSQL As String
Dim serialnum As String
' This Sub routine checks the quantity value of a material to be sure materials are avaialbe to
' assign to employees or equipment trucks.
' If material are available, the user is prompted to eneter the desired quantity to assign and
' the quantity is updated
'Find out if the item is secured and get serial number if it does not exist
If Me.cboMaterial_ID.Column(3, cboMaterial_ID.ItemsSelected) Then
If IsNull(Me.cboMaterial_ID.Column(4, cboMaterial_ID.ItemsSelected)) Then
serialnum = InputBox(" What is the units serial number")
strSQL = "UPDATE Materials SET Unit_Serial_No = " & serialnum & " WHERE Material_ID = " & Me.cboMaterial_ID
CurrentDb.Execute strSQL, dbFailOnError
Me.cboMaterial_ID.Requery ' To update the Combo, use:
End If
Else
result = Me![cboMaterial_ID].Column(2) ' Get the quantity available of the material selected.
Number = InputBox("Enter the Quantity,") 'Ask user how much of this material is needed. Handle the error on cancel
NumResult = result - Number ' subtract Quantity needed from quantity available
strSQL = "UPDATE Materials SET Quantity = " & NumResult & " WHERE Material_ID = " & Me.cboMaterial_ID
'Need to figure out how to also insert "number" in the QTY_Allocated field in the Allocation table.
' Below statements are not working
'strSQL = "UPDATE allocation SET QTY_Allocated = " & Number & " WHERE
' Use Material_ID = '" & Me.cboMaterial_ID & "'" if Material_ID is not numeric.
CurrentDb.Execute strSQL, dbFailOnError
Me.cboMaterial_ID.Requery
End If
MsgBox ("Current Value of Quantity " & NumResult)
End Sub
Okay, I think I get what you are trying to say, but I am not sure at the same time.. but before that, Number is a bad variable name.. Try something else.. Like NumVar.. Use Me.<controlName> rather than Me!<controlName>.. Try the following..
Code:
Private Sub cboMaterial_ID_AfterUpdate()
Dim NumVar As Integer
Dim NumResult As Integer
Dim result As Integer
Dim secured As String
Dim strSQL As String
Dim serialnum As String
[COLOR=Green] ' This Sub routine checks the quantity value of a material to be sure materials are avaialbe to
' assign to employees or equipment trucks.
' If material are available, the user is prompted to eneter the desired quantity to assign and
' the quantity is updated
[/COLOR] [COLOR=Green] 'Find out if the item is secured and get serial number if it does not exist[/COLOR]
If Me.cboMaterial_ID.Column(3, cboMaterial_ID.ItemsSelected) Then
If IsNull(Me.cboMaterial_ID.Column(4, cboMaterial_ID.ItemsSelected)) Then
serialnum = InputBox(" What is the units serial number")
strSQL = "UPDATE Materials SET Unit_Serial_No = " & serialnum & " WHERE Material_ID = " & Me.cboMaterial_ID
CurrentDb.Execute strSQL, dbFailOnError
Me.cboMaterial_ID.Requery[COLOR=Green] ' To update the Combo, use:[/COLOR]
End If
Else
result = Me.[cboMaterial_ID].Column(2, cboMaterial_ID.ItemsSelected)[COLOR=Green] ' Get the quantity available of the material selected.[/COLOR]
NumVar = InputBox("Enter the Quantity,") [COLOR=Green]'Ask user how much of this material is needed. Handle the error on cancel[/COLOR]
NumResult = result - NumVar [COLOR=Green]' subtract Quantity needed from quantity available[/COLOR]
strSQL = "UPDATE Materials SET Quantity = " & NumResult & ", QTY_Allocated = " & NumVar & " WHERE Material_ID = " & Me.cboMaterial_ID
CurrentDb.Execute strSQL, dbFailOnError
Me.cboMaterial_ID.Requery
End If
MsgBox ("Current Value of Quantity " & NumResult)
End Sub
Which line did you have trouble i.e. Rutime Error.??
[FONT="]OK Thank you, the line in red will give me "run time error 3061 : Too few parameters, Expected:1". If I mix letters and numbers. [/FONT]
[FONT="]Also just to clarify: I have Three tables and I completely understand if you want to skip this stuff and “cut to the chase”, but I figure that the more is known, the better.
[/FONT]
[FONT="]Table 1[/FONT]
[FONT="]Materials:
Material_ID(PK), Auto
Material_name, Text
Material_Part_No, Text
Quantity, Numeric
Material_Serial_No, Text
Secured_Item, Yes/No)
[FONT="]Where field Material_name in the Materials table and Employee_Number in the Employees table are both Combo Boxes in the a Allocated_Materials form. They have the following Row Sources respectively:[/FONT]
Code:
cbo_Material_ID: Row Source
SELECT materials.Material_ID, materials.Material_name, materials.Quantity, materials.Secured_item, materials.[Material_name] FROM materials WHERE (((materials.Quantity)>0)) ORDER BY materials.[Material_name];
Code:
cbo_Employee_Number: Row Source
SELECT [employees].[ID], [employees].[Employee number], [employees].[Last], [employees].[First] FROM employees ORDER BY [Last];
[FONT="]What this DB does is takes a list of materials and a list of employees and associates them Via a form in the Allocated_Materials Table. The Combo Boxes provide easy to use Pull down lists.[/FONT]
[FONT="]One of my biggest problems here is getting the syntax right to update and Write the prompted information to the correct spots in each of these tables. Mainly the quantity.[/FONT]
Code:
Private Sub cboMaterial_ID_AfterUpdate()
Dim NumVar As Integer
Dim NumResult As Integer
Dim result As Integer
Dim secured As String
Dim strSQL As String
Dim serialnum As String
' This Sub routine checks the quantity value of a material to be sure materials are avaialbe to
' assign to employees or equipment trucks.
' If material are available, the user is prompted to eneter the desired quantity to assign and
' the quantity is updated
'Find out if the item is secured and get serial number if it does not exist
If Me.cboMaterial_ID.Column(3, cboMaterial_ID.ItemsSelected) Then
If IsNull(Me.cboMaterial_ID.Column(4, cboMaterial_ID.ItemsSelected)) Then
serialnum = InputBox(" What is the units serial number")
[COLOR=Red]strSQL = "UPDATE Materials SET Unit_Serial_No = " & serialnum & " WHERE Material_ID = " & Me.cboMaterial_ID[/COLOR]
CurrentDb.Execute strSQL, dbFailOnError
Me.cboMaterial_ID.Requery ' To update the Combo, use:
End If
Else
result = Me.[cboMaterial_ID].Column(2, cboMaterial_ID.ItemsSelected) ' Get the quantity available of the material selected.
NumVar = InputBox("Enter the Quantity,") 'Ask user how much of this material is needed. Handle the error on cancel
NumResult = result - NumVar ' subtract Quantity needed from quantity available
strSQL = "UPDATE Materials SET Quantity = " & NumResult & ", QTY_Allocated = " & NumVar & " WHERE Material_ID = " & Me.cboMaterial_ID
CurrentDb.Execute strSQL, dbFailOnError
Me.cboMaterial_ID.Requery
End If
MsgBox ("Current Value of Quantity " & NumResult)
End Sub
Thank you that works. Believe me, I hate to say this, but I am doing quite a bit of research and due diligence but I still come short. You have literally saved me days in trial and error, I am grateful. If you could look at one more thing:
Below is a statement in Blue and Orange. I am having trouble completing the "Where" clause because I do not know how to Update a field in the current record I am creating. There is a field in the Allocation_Materials table called Quantity_Allocated which needs to be Updated with the variable "number"
I do understand and will change all variables to ones which are more meaningful to the code as soon as I can get this to work.
Code:
Private Sub cboMaterial_ID_AfterUpdate()
Dim Number As Integer
Dim NumResult As Integer
Dim result As Integer
Dim secured As String
Dim strSQL As String
Dim serialnum As String
' This Sub routine checks the quantity value of a material to be sure materials are avaialbe to
' assign to employees or equipment trucks.
' If material are available, the user is prompted to eneter the desired quantity to assign and
' the quantity is updated
'Find out if the item is secured and get serial number if it does not exist
If Me.cboMaterial_ID.Column(3, cboMaterial_ID.ItemsSelected) Then
If IsNull(Me.cboMaterial_ID.Column(4, cboMaterial_ID.ItemsSelected)) Then
serialnum = InputBox(" What is the units serial number")
strSQL = "UPDATE Materials SET Unit_Serial_No = '" & serialnum & "' WHERE Material_ID = " & Me.cboMaterial_ID
CurrentDb.Execute strSQL, dbFailOnError
Me.cboMaterial_ID.Requery ' To update the Combo, use:
End If
Else
result = Me![cboMaterial_ID].Column(2) ' Get the quantity available of the material selected.
Number = InputBox("Enter the Quantity,") 'Ask user how much of this material is needed. Handle the error on cancel
NumResult = result - Number ' subtract Quantity needed from quantity available
strSQL = "UPDATE Materials SET Quantity = " & NumResult & " WHERE Material_ID = " & Me.cboMaterial_ID
[COLOR=Navy]'Need to figure out how to Update "number" in the QTY_Allocated field in the Allocation table.
' Below statements are not working
'strSQL = "UPDATE Allocation_Materials SET Quantity_Allocated = " & Number & " [COLOR=DarkOrange]WHERE ????[/COLOR]
[/COLOR]
' Use Material_ID = '" & Me.cboMaterial_ID & "'" if Material_ID is not numeric.
CurrentDb.Execute strSQL, dbFailOnError
Me.cboMaterial_ID.Requery
End If
MsgBox ("Current Value of Quantity " & NumResult)
End Sub
Attempts to update the QTY_Allocated field of the Materials table but this is obsolete, because this field does not exist. The "Allocated_Quantity" field is in the table "allocation" and is the current record I am creating which needs the variable "number" written to it as I have attempted to do below:. I hope this makes sense.
Code:
strSQL = "UPDATE Allocation_Materials SET Quantity_Allocated = " & Number & " WHERE ????
Look at the four statements in red below. Does that make sense? Don't I need a new Combo Box or Text Box if I am switching to a different table and writing different information?
Code:
Private Sub cboMaterial_ID_AfterUpdate()
Dim Number As Integer
Dim NumResult As Integer
Dim result As Integer
Dim secured As String
Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim serialnum As String
' This Sub routine checks the quantity value of a material to be sure materials are avaialbe to
' assign to employees or equipment trucks.
' If material are available, the user is prompted to eneter the desired quantity to assign and
' the quantity is updated
'Find out if the item is secured and get serial number if it does not exist
If Me.cboMaterial_ID.Column(3, cboMaterial_ID.ItemsSelected) Then
If IsNull(Me.cboMaterial_ID.Column(4, cboMaterial_ID.ItemsSelected)) Then
serialnum = InputBox(" What is the units serial number")
strSQL = "UPDATE Materials SET Unit_Serial_No = '" & serialnum & "' WHERE Material_ID = " & Me.cboMaterial_ID
CurrentDb.Execute strSQL, dbFailOnError
Me.cboMaterial_ID.Requery ' To update the Combo, use:
End If
Else
result = Me![cboMaterial_ID].Column(2) ' Get the quantity available of the material selected.
Number = InputBox("Enter the Quantity,") 'Ask user how much of this material is needed. Handle the error on cancel
NumResult = result - Number ' subtract Quantity needed from quantity available
[COLOR=DarkRed]strSQL1 = "UPDATE Materials SET Quantity = " & NumResult & " WHERE Material_ID = " & Me.cboMaterial_ID
CurrentDb.Execute strSQL1, dbFailOnError
strSQL2 = "UPDATE allocation SET QTY_Allocated = " & Number & " WHERE Material_ID = " & Me.cboMaterial_ID
CurrentDb.Execute strSQL2, dbFailOnError[/COLOR]
Me.cboMaterial_ID.Requery
' Use Material_ID = '" & Me.cboMaterial_ID & "'" if Material_ID is not numeric.
End If
MsgBox ("Current Value of Quantity " & NumResult)
'StrSQL = "UPDATE Materials SET Quantity = " & NumResult & ", QTY_Allocated = " & NumVar & " WHERE Material_ID = " & Me.cboMaterial_ID
' CurrentDb.Execute strSQL, dbFailOnError
End Sub
back2basic, as jdraw had requested, could you provide the rendered SQL?? using Debugging Technique? To access the Immediate window go to your VBA Editor (ALT+F11) and then Ctrl+G.
UPDATE [COLOR=DarkRed]allocation[/COLOR] SET QTY_Allocated = 2 WHERE Material_ID = 31
QTY_Allocated is correct at 2 but Material_ID = 31 is not pointing to the correct table or record ID. I want this QTY_Allocated to write to the current table "allocation", where a new record is being created.
Does this make sense? I hope so.
You may be wondering why this field, I agree and perhaps it should be, but I need a new record in this "allocation" table each time a material is assigned so I can tell the dates, times and quantities each time en employee takes materials.
