Need Help! Please.

[ismith]

I got the code below which opens a msg box for each error that is thrown. How can I put all of these in one if statement and have the last bit (isNumeric) in the else if statement?



Err tracks the error and changes to 1 if an error is found.

txtDescription.SetFocus
If txtDescription.Text = "" Then
err = err + 1
MsgBox "Please fill in the Description box!"
End If

cbCategory.SetFocus
If cbCategory.Text = "" Then
err = err + 1
MsgBox "Please select a Category!"
End If

txtSize.SetFocus
If txtSize.Text = "" Then
err = err + 1
MsgBox "Please fill in the Size box!"
End If

txtQuantity.SetFocus
If txtQuantity.Text = "" Then
err = err + 1
MsgBox "Please fill in Quantity box!"
End If

If Not IsNumeric(Me.txtQuantity) Then
err = err + 1
MsgBox "Quantity can only contain numbers!", vbCritical, "Quantity"
End If

[Robeen]

Can you explain with different words exactly what you need to accomplish?

[ismith]

Can you explain with different words exactly what you need to accomplish?

Atm as u can see I'm usin different if statements for each error. I want to put all the statements into 1 If Statement which will print a msg 'please fill out all fields.
This is what I've tried but it's not working..


If Not IsNumeric(Me.txtQuantity) Then
MsgBox "Quantity can only contain numbers!", vbCritical, "Quantity"

Else If txtDescription.Text = "" Or cbCategory.Text = "" Or txtSize.Text = "" Or txtQuantity.Text = "" Or txtPrice.Text = "" Then
err = err + 1
MsgBox "Please fill in all the Fields!"
End If
Else 'do nothing
End If

[Robeen]

I haven't tested this but it should give yo a start:

 
txtDescription.SetFocus
If txtDescription.Text > "" Then
   cbCategory.SetFocus
   If cbCategory.Text > "" Then
      txtSize.SetFocus
      If txtSize.Text > "" Then
         txtQuantity.SetFocus
         If txtQuantity.Text > "" Then
            If IsNumeric(Me.txtQuantity) Then
            Else
            MsgBox "All Boxes are not filled in - OR - Quantity doesn't contain numbers!", vbCritical, "Error!"
            End If
         End If
      End If
   End If
End If

I flipped the logic to see if everything IS filled in and if txtQuantity IS numeric.

That way - as soon as it encounters a field that is not what it should be - it will go to the error message.

I still don't understand exactly what you want to do with the IsNumeric field so I included it without the Esle If . . .

BUT . . .

I prefer the route you were going already, though.

Check each field [like you were].
If it doesn't match what it needs to be - throw the message box and after the message set focus back to that field - forcing the user to correct the error.

Wouldn't that work for you?
What is the problem with it?

[ismith]

Its just that if I were to leave out all the fields blank, then I get an error for each box which is bot user friendly. So I thought I'd put it into 1 if statement and throw 1 msg box

[Robeen]

did my solution work for you?

[ismith]

Hi. Its still not working. It adds it to the database without it even validating.
And when i check the database it adds empty fields...

[Robeen]

I'm not sure if I missed something in your posts.

I was only helping you with putting up the error message box - not with what gets written to your tables.

Does the code that checks for empty or not numeric fields work?
Once that is functioning like you need - we can look at what's getting written.

I don't know how you have programmed your button to write data to the table - but if the part of the button code that writes to the table is not INSIDE the If Then Else structure that I gave you - then it will go through my code and give you the message boxes like you wanted - but it will ALSO execute the code that writes whatever is on the Form as well . . .

[ismith]

I'm not sure if I missed something in your posts.

I was only helping you with putting up the error message box - not with what gets written to your tables.

Does the code that checks for empty or not numeric fields work?
Once that is functioning like you need - we can look at what's getting written.

I don't know how you have programmed your button to write data to the table - but if the part of the button code that writes to the table is not INSIDE the If Then Else structure that I gave you - then it will go through my code and give you the message boxes like you wanted - but it will ALSO execute the code that writes whatever is on the Form as well . . .

The error messages dont come up aswell.

this is my whole code..

Dim err As Integer
Dim database As database
Dim addprod As Recordset

'This sets up the database and uses the query I set up
'to look into the Product Table
Set database = CurrentDb
Set addprod = database.OpenRecordset("productQuery")

'This checks all the fields to see if there is something
'entered or if it is empty. if it is then it sets the
'err attribute to 1 and displays the msg.

txtDescription.SetFocus
If txtDescription.Text > "" Then
cbCategory.SetFocus
If cbCategory.Text > "" Then
txtSize.SetFocus
If txtSize.Text > "" Then
txtQuantity.SetFocus
If txtQuantity.Text > "" Then
If IsNumeric(Me.txtQuantity) Then
Else
MsgBox "All Boxes are not filled in - OR - Quantity doesn't contain numbers!", vbCritical, "Error!"
End If
End If
End If
End If
End If








'This checks the err attribute and checks if it is less than 1.
'this indictaes there are no error.
'It then adds a new Record to the addProd Query and sets the values
'to what is entered in the text/combo box.

If err < 1 Then
addprod.AddNew
addprod!Description = txtDescription.Value
addprod!Category = cbCategory.Value
addprod!Size = txtSize.Value
addprod!Quantity = txtQuantity.Value
addprod!Price = "£" + txtPrice.Value
addprod.Update

'This displays a message confirming the description of the item entered.
MsgBox "Product: " & txtDescription.Value & " has been successfully added"

'closes database.
database.Close

'Once the product is entered this clears all the fields so a new product
'can be entered.
txtDescription.SetFocus
txtDescription = ""
cbCategory.SetFocus
cbCategory = ""
txtSize.SetFocus
txtSize = ""
txtQuantity.SetFocus
txtQuantity = ""
txtPrice.SetFocus
txtPrice = ""
txtDescription.SetFocus

Else
'If there is an error in putting it into the database this error is given.
'it also comes up when any of the prev errors happen.
MsgBox "An Error has occurred, please check and try again", vbCritical, "Error"
End If


End Sub

[Robeen]

Set err = 0 [to start out with no error] at the start of your code and then . . .

Change this:

 
Else
MsgBox "All Boxes are not filled in - OR - Quantity doesn't contain numbers!", vbCritical, "Error!"

To this:

 
Else
MsgBox "All Boxes are not filled in - OR - Quantity doesn't contain numbers!", vbCritical, "Error!"
err = 1

Now - when you get to 'If err < 1 Then' - the Table will not be updated.

Let me know if this works.

[ismith]

Now im getting an error whihc says 'Run Time error 3421'.
And when i go to debug it highlights this line:

addprod!Quantity = txtQuantity.Value

Why is that?

and its not letting me test because of that error.

BUT if i put in some numbers in that box then it adds to database with all the other field empty..

[Robeen]

That error is a data type conversion error.

Possibly in the line of code you mentioned:

addprod!Quantity = txtQuantity.Value

you have two different data types.

Or - to put it another way - whatever is in txtQuantity.Value is not being accepted into addprod!Quantity.

What is the data type of the field addprod!Quantity?

[ismith]

That error is a data type conversion error.

Possibly in the line of code you mentioned:

you have two different data types.

Or - to put it another way - whatever is in txtQuantity.Value is not being accepted into addprod!Quantity.

What is the data type of the field addprod!Quantity?

It's a Number with a Long Int..

[Robeen]

I think the best thing for you to do here is decide whether your initial question in this post was answered - i.e. the issue of putting all your separate message boxes into one If . . . Then . . . Else construct.

If that IS working, then mark this as solved and start a New Thread to open it up to the Forum so that I am not the only one looking at it.

I haven't had much free time to spend on this and I don't want you to be held up waiting for me.

Can you post a stripped down version of your DB here?
Make it small so you don' thave to zip it.
If you can do that, I will look at it and see why you are getting that error.