Help using InputBox data to add data...

[Austinl]

I have my Access Form with a button.
I click the button and it opens an InputBox.
The results of the Input data are stored as a variable.
I want the user to be able to put in a number in the Input box, and then, based on the data, modify the appropriate table entry.

Here is what I've got,

Dim AddSize As Integer

AddSize = InputBox("Which size of shoe would you like to add to this record?")

I have a table with alot of columns so i need i tried this:

[Number(AddSize)] = [Number(AddSize)] + 1

This however didn't work.

Basically, i got 26 columns (0, 0.5, 1, 1.5, 2, 2.5, 3, etc....12.5, 13)
I want to add one to the field that the person has typed the number in for.

Eg. 13, thus Column 13's record + 1

Can anybody tell me how to do this? Id rather not have tons of code for every eventuality. :)

Also is there a way to stop getting error messages when somebody adds something thats not number? Eg. Whfgdfga = Broken :(

Thanks In Advance
Lee

[Imdabaum]

Dim rst as DAO.Recordset
'This enforces the value to be of Integer type. You can just as easily
'declare double to validate 1.56 entries.
Dim AddSize as Integer ' or Double
Set rst = Currentdb.OpenRecordset([YourTablename])

'If they enter text, you will get a ERROR Datatype mismatch.
'Would be a good idea to throw in some error handling code to not send
'the user into the VBE.
AddSize = InputBox("Which size of shoe would you like to add to this record?")

rst.Fields(Cstr(AddSize)) = rst.Fields(Cstr(AddSize)) + 1

[Imdabaum]

You'll need to also check to make sure AddSize <=13. Do you mind me asking what you are trying to accomplish with this?

To trap the error, you'd want to add
On Error Goto Error_Handler

'CODE.....

ExitProcedure:
Exit Function
Error_Handler:
Msgbox Err.description, , "Error: " & Err.Number
' or Err.raise err.number, , err.description
Resume ExitProcedure


Additionally you can add some more specifica error catching. If the error is a type mismatch, then you can send them back to re-enter the value.

You'd do that by simply adding


GetAddSizeValue:
AddSize = Inputbox("Which size of shoe would you like to add to this record?")

'CODE...
ExitProcedure:
Exit Function
Error_Handler:
If Err.number = {datatypemismatch error value} Then
Resume GetAddSizeValue
Else
Msgbox Err.description, , "Error: " & Err.Number
' or Err.raise err.number, , err.description
Resume ExitProcedure
End If

[Austinl]

Ah sure, im doing a stock control system for work :)

Thanks for your help, but i am currently unable to get any of your code to work with it at the moment...Its kicking back
Private Sub btnAdd_Click()
GetAddSizeValue:
Dim rst As DAO.Recordset
'This enforces the value to be of Integer type. You can just as easily
'declare double to validate 1.56 entries.
Dim AddSize As Integer ' or Double
Set rst = CurrentDb.OpenRecordset([Shoes])

'If they enter text, you will get a ERROR Datatype mismatch.
'Would be a good idea to throw in some error handling code to not send
'the user into the VBE.
AddSize = InputBox("Which size of shoe would you like to add to this record?")

rst.Fields(CStr(AddSize)) = rst.Fields(CStr(AddSize)) + 1
ExitProcedure:
Exit Function '<--Errors on this
Error_Handler:
If Err.number = {datatypemismatch error value} Then '<-- In Red
Resume GetAddSizeValue
Else
MsgBox Err.Description, , "Error: " & Err.Number
' or Err.raise err.number, , err.description
Resume ExitProcedure
End If
End Sub

[OBP]

Austin, Can I ask why you have chosen to use this method, rather than using a Combo or List box?

[Imdabaum]

Ah sure, im doing a stock control system for work :)

Thanks for your help, but i am currently unable to get any of your code to work with it at the moment...Its kicking back
Private Sub btnAdd_Click()
GetAddSizeValue:
Dim rst As DAO.Recordset
'This enforces the value to be of Integer type. You can just as easily
'declare double to validate 1.56 entries.
Dim AddSize As Integer ' or Double
Set rst = CurrentDb.OpenRecordset([Shoes])

'If they enter text, you will get a ERROR Datatype mismatch.
'Would be a good idea to throw in some error handling code to not send
'the user into the VBE.
AddSize = InputBox("Which size of shoe would you like to add to this record?")

rst.Fields(CStr(AddSize)) = rst.Fields(CStr(AddSize)) + 1
ExitProcedure:
Exit Function '<--Errors on this
Error_Handler:
If Err.number = {datatypemismatch error value} Then '<-- In Red
Resume GetAddSizeValue
Else
MsgBox Err.Description, , "Error: " & Err.Number
' or Err.raise err.number, , err.description
Resume ExitProcedure
End If
End Sub

Exit Function errors because you've placed it in a Sub. Change it to Exit Sub
& make this change.
If Err.number = 13 Then '<-- In Red

http://support.microsoft.com/kb/146864