Combining Code

[Beany]

Hi,

I have got an ADD button with the following code:

Private Sub addrec_Click()
On Error GoTo Err_addrec_Click

DoCmd.GoToRecord , , acNewRec

Exit_addrec_Click:
Exit Sub

Err_addrec_Click:
MsgBox Err.Description
Resume Exit_addrec_Click

End Sub

and i have some validations through the following code:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim blnValidation As Boolean
Dim strValidate As String

strValidate = "These item(s) were not filled out and are required:" & vbCrLf & vbCrLf

If IsNull(Me.username) Or Me.username = "" Then
blnValidate = True
strValidate = strValidate & "Username" & vbCrLf
End If

If IsNull(Me.number) Or Me.number = "" Then
blnValidate = True
strValidate = strValidate & "Number" & vbCrLf
End If

If blnValidate Then
MsgBox strValidate
Cancel = True
End If

End Sub

The question...... how do i combine these codes and have an ADD button that will show a validation message before updating anything??

i cant seem to get this right? somat always goes wrong? i do not want the add button to add anything until the form is complete?

[NeoPa]

Private Sub AddButton_Click()
    'Do validation checks
    If blnValidate Then
        MsgBox strValidate
    Else
        'Add new record
        DoCmd.GoToRecord , , acNewRec
    End If
End Sub

[Beany]

thanks Neo,

that code doesnt work.............. ive tried the following.......... what am i doing wrong?

[HTML]Private Sub addrec_Click()
'Do validation checks


Dim blnValidation As Boolean
Dim strValidate As String

strValidate = "These item(s) were not filled out and are required:" & vbCrLf & vbCrLf

If IsNull(Me.username) Or Me.username = "" Then
blnValidate = True
strValidate = strValidate & "Username" & vbCrLf
End If

If blnValidate Then
MsgBox strValidate
Else
'Add new record
DoCmd.GoToRecord , , acNewRec
End If

End Sub[/HTML]

[Beany]

any1 have an idea??

[markmcgookin]

any1 have an idea??

Could you split the code into sub routines, then call them in order?

i.e.

btn_click ... ()

sub_validate1()

sub_validate2()

end sub

sub_validate1 ... ()

end sub

sub_validate2()

end sub

Something like that?

[NeoPa]

You spelt blnValidation in the Dim statement but blnValidate when referring to it.
As the check is so simple, you don't need it anyway ;).
As the string is also only set and used once each, it too is unnecessary.
Try this :

Private Sub addrec_Click()
    If Nz(Me.username, "") = "" Then
        MsgBox "These item(s) were not filled out " & _
               "and are required:" & vbCrLf & vbCrLf & _
               "Username" & vbCrLf
    Else
        'Add new record
        DoCmd.GoToRecord , , acNewRec
    End If
End Sub

I made the code narrower with the line continuation character (_) to display more easily in here but that is not important to the code working.
BTW Code indentation also doesn't affect the way the code works, but is invaluable to help a reader understand what is happening where.

[Beany]

Neo you're a star.......

just another question............

the code you provided shows that if the USERNAME field is missed a validation message occurs.(which is good)...............

how would i add more fields to that....... for eg, phone number, imei code etc.....

so it brings up a list of all the fields ive missed?

[NeoPa]

Then you would use a string variable.
Give me some time and I'll post some code to give an example.

[NeoPa]

Assuming IMEI code is numeric & Phone Number is a string :

Private Sub addrec_Click()
    Dim strMsg As String
    Dim blnValidate As Boolean
 
    strMsg = "These item(s) were not filled out " & _
             "and are required:" & vbCrLf & vbCrLf
    blnValidate = False
    If Nz(Me.username, "") = "" Then
        blnValidate = True
        strMsg = strMsg & "Username" & vbCrLf
    ElseIf Nz(Me.Phone_No, "") = "" Then
        blnValidate = True
        strMsg = strMsg & "Phone Number" & vbCrLf
    ElseIf Nz(Me.IMEI_Code, 0) = 0 Then
        blnValidate = True
        strMsg = strMsg & "IMEI" & vbCrLf
    End If
    If blnValidate Then
        MsgBox strMsg
    Else
        'Add new record
        DoCmd.GoToRecord , , acNewRec
    End If
End Sub

[Beany]

ur good...........and quick.........

il give that a try........ thank you very much........

appreciate it Neo

[NeoPa]

Shhhhhhh.
My boss will hear.
;)

[Beany]

Neo is the Hero.................


brilliant u got it working for me...............

[Beany]

sorry for being a nuisance...

ive noticed that it still adds the details to my table even if the form is incomplete???

so if i just put in the username and miss out the imei,phone number fields, it will bring up the validations but it just adds the username to my table????

[NeoPa]

That's because I got it wrong :embarrassed:
Try this instead :

Private Sub addrec_Click()
    Dim strMsg As String
    Dim blnValidate As Boolean
 
    strMsg = "These item(s) were not filled out " & _
             "and are required:" & vbCrLf & vbCrLf
    blnValidate = False
    If Nz(Me.username, "") = "" Then
        blnValidate = True
        strMsg = strMsg & "Username" & vbCrLf
    End If
    If Nz(Me.Phone_No, "") = "" Then
        blnValidate = True
        strMsg = strMsg & "Phone Number" & vbCrLf
    End If
    If Nz(Me.IMEI_Code, 0) = 0 Then
        blnValidate = True
        strMsg = strMsg & "IMEI" & vbCrLf
    End If
    If blnValidate Then
        MsgBox strMsg
    Else
        'Add new record
        DoCmd.GoToRecord , , acNewRec
    End If
End Sub

Don't forget to change all variables (Fields; Controls etc) to the correct names as used in your database.

[Beany]

ha........... thanks once again............


ive got a problem.........

my form is a bit crazy....... as i insert the value within the field it adds the details to my table!?!!!..... y is that? i want the add button (created from your help) to do the adding once clicked?

[NeoPa]

I suspect that you have the form bound to the recordset (table).
For the concept you require to work - you need it NOT to be bound.

[Beany]

this is going to be a funny (or a stupid) question...........

how do i unbound it??? oops i can see ur shoe comin towards me :)

[NeoPa]

No Beany.
In karate we tend not to use shoes.
Besides, the trick, when done properly (gyaku mawashi-geri for instance), is to catch the 'recipient' without their even seeing it. :D

Anyway - enough of the nonsense (for now).
In Design mode of the form, Clear the Record Source property.

[Beany]

No Beany.
In karate we tend not to use shoes.
Besides, the trick, when done properly (gyaku mawashi-geri for instance), is to catch the 'recipient' without their even seeing it. :D

Anyway - enough of the nonsense (for now).
In Design mode of the form, Clear the Record Source property.

Hi,

NeoPa, if i clear the Record Source property, how will it add the details to the table tab_main?

Im confused??

[Beany]

anyone????????????????????????

[NeoPa]

anyone????????????????????????

Hang on matey.
We do have lives and jobs and things too.
This is on my list and I will respond when I can.

[NeoPa]

It seems you're confused because (sorry to say this) you're going about this the wrong way.
In this case you clearly do NOT want to add the record separately. You already have it bound to the form.
Simply moving off that record will update it with any changes already made.
You will need to put your validation code (amended below) in the Form_BeforeUpdate sub instead.
This will be instead of your command code.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strMsg As String
 
    strMsg = "These item(s) were not filled out " & _
             "and are required:" & vbCrLf & vbCrLf
    If Nz(Me.username, "") = "" Then
        Cancel = True
        strMsg = strMsg & "Username" & vbCrLf
    End If
    If Nz(Me.Phone_No, "") = "" Then
        Cancel = True
        strMsg = strMsg & "Phone Number" & vbCrLf
    End If
    If Nz(Me.IMEI_Code, 0) = 0 Then
        Cancel = True
        strMsg = strMsg & "IMEI" & vbCrLf
    End If
    If Cancel Then MsgBox strMsg
End Sub

New record added automatically if Cancel = False

[Beany]

sorry for being so eager Neo, its just my database its annoying me.......

i think ive got you confused...............

we was on the right track, ive got the validation working with the add button, the only thing that i want to do, is use the add button to insert the record into my table......

i want the form to be unbound. i tried this by clearing the row source of the form.... but if i leave it like this nothing will add........

i would like to use the add button for the users to add details.......

Gui lah hui te ha (thanks)

[Beany]

i need help? any1?

[NeoPa]

i need help? any1?

I'm out tonight.
Do another bump tomorrow and I'll pick it up then.

[MMcCarthy]

In the Click event of the Add button you can use an INSERT query.

Example:

Dim strSQL As String
 
   strSQL = "INSERT INTO TableName (NumberField, DateField, TextField) VALUES (" & _
      Me.NumberTextbox & ",#" & Me.DateTextbox & "#,'" & Me.TextTextbox & "');"
   DoCmd.RunSQL strSQL
 

Pay close attention to the different syntax for different types of values.

Mary

[Beany]

Thanks Mary for the code:

[PHP]Dim strSQL As String

strSQL = "INSERT INTO TableName (NumberField, DateField, TextField) VALUES (" & _
Me.NumberTextbox & ",#" & Me.DateTextbox & "#,'" & Me.TextTextbox & "');"
DoCmd.RunSQL strSQL[/PHP]

is it possible for you to help me insert the following info into the above code, coz i dont understand that code:

table is tab_main

i have the following textboxes and checkboxes in my form(the ones i would like to add):

textboxes(names):

username
cost_centre
number
phone_model
imei
puk_code
sim_no
date_issued
notes
tariff

checkboxes(names):

call_int
roam

-----------------------------------------------------------------------------------

i have deleted the row source and now in every textbox i get the following #name?... y?

Mary ur help will be highly appreciated

thanks

[NeoPa]

Thanks Mary for the code:

[PHP]Dim strSQL As String

strSQL = "INSERT INTO TableName (NumberField, DateField, TextField) VALUES (" & _
Me.NumberTextbox & ",#" & Me.DateTextbox & "#,'" & Me.TextTextbox & "');"
DoCmd.RunSQL strSQL[/PHP]

is it possible for you to help me insert the following info into the above code, coz i dont understand that code:

table is tab_main

i have the following textboxes and checkboxes in my form(the ones i would like to add):

textboxes(names):

username
cost_centre
number
phone_model
imei
puk_code
sim_no
date_issued
notes
tariff

checkboxes(names):

call_int
roam

-----------------------------------------------------------------------------------

i have deleted the row source and now in every textbox i get the following #name?... y?

Mary ur help will be highly appreciated

thanks

You need to indicate what type (String; Numeric; Date) each field is to know how to code this.
For the #Name problem, you need to go to each control and remove whatever is there in the ControlSource.
I assume by 'row source' you mean 'Record Source'?

[Beany]

Gud to hear from you Neo;

textboxes(names):

username >Text
cost_centre >Text
number >Text
phone_model >Text
imei >Text
puk_code >Text
sim_no >Text
date_issued >Date/Time
notes >Memo
tariff >Text

checkboxes(names):

call_int >Yes/No
roam > Yes/No


sorry i meant record source............. ive cleared all the Control Source like you said Neo......

i hope this is it???

[MMcCarthy]

strSQL = "INSERT INTO tab_main (username, cost_centre, number, phone_model, " & _
         "imei, puk_code, sim_no, date_issued, notes, tariff, call_int, roam) " _
         "VALUES ('" & Me.username & "','" & Me.cost_centre & "','" & _
         Me.number & "','" & Me.phone_model & "','" & Me.imei & "','" & _
         Me.puk_code & "','" & Me.sim_no & "',#" & Me.date_issued & "#,'" & _
         Me.notes & "','" & Me.tariff & "'," & Me.call_int & "," & Me.roam & ");"
 
DoCmd.RunSQL strSQL 
 

Mary

[Beany]

With ur help Mary, im using the following code for the Add button:

[PHP]Private Sub addrec_Click()

Dim strSQL As String

strSQL = "INSERT INTO tab_main (username, cost_centre, number, phone_model, " & _
"imei, puk_code, sim_no, date_issued, notes, tariff, call_int, roam) " _
"VALUES ('" & Me.username & "','" & Me.cost_centre & "','" & _
Me.number & "','" & Me.phone_model & "','" & Me.imei & "','" & _
Me.puk_code & "','" & Me.sim_no & "',#" & Me.date_issued & "#,'" & _
Me.notes & "','" & Me.tariff & "'," & Me.call_int & "," & Me.roam & ");"

DoCmd.RunSQL strSQL

End If

End Sub[/PHP]

the problem is that the code below in Microsoft Visual Basic, is showing as RED:

[PHP]strSQL = "INSERT INTO tab_main (username, cost_centre, number, phone_model, " & _
"imei, puk_code, sim_no, date_issued, notes, tariff, call_int, roam) " _
"VALUES ('" & Me.username & "','" & Me.cost_centre & "','" & _
Me.number & "','" & Me.phone_model & "','" & Me.imei & "','" & _
Me.puk_code & "','" & Me.sim_no & "',#" & Me.date_issued & "#,'" & _
Me.notes & "','" & Me.tariff & "'," & Me.call_int & "," & Me.roam & ");"[/PHP]

i cant debug it? is there sumat in the code above which im using for adding?

[MMcCarthy]

Sorry my mistake.

[PHP]

Private Sub addrec_Click()
Dim strSQL As String

strSQL = "INSERT INTO tab_main (username, cost_centre, number, phone_model, " & _
"imei, puk_code, sim_no, date_issued, notes, tariff, call_int, roam) " & _
"VALUES ('" & Me.username & "','" & Me.cost_centre & "','" & _
Me.number & "','" & Me.phone_model & "','" & Me.imei & "','" & _
Me.puk_code & "','" & Me.sim_no & "',#" & Me.date_issued & "#,'" & _
Me.notes & "','" & Me.tariff & "'," & Me.call_int & "," & Me.roam & ");"

DoCmd.RunSQL strSQL

End Sub
[/PHP]

I also removed the End If as I assume it was left over from previous code.

Mary

[Beany]

Mary Hun,

im using the above code(thanks)............

but im getting the following error message when i click the add button:

Run-time error '3134',
Syntax error in INSERT INTO statement

why is that?

is there a problem with the following line:

DoCmd.RunSQL strSQL

[MMcCarthy]

Make sure all the field names and control names are correct.

In the VBA editor. Open the Immediate pane and put

Debug.Print strSQL

before the line DoCmd.RunSQL strSQL

When you run the code, this will print out the statement in the immediate pane.

Copy and post it here and I will have a look at it.

Mary

[Beany]

Hi Mary,

ive placed the code Debug.Print strSQL before DoCmd.RunSQL strSQL...........

then saved it and clicked on the add button........... what information am i looking for? which pane?

[Beany]

sorry Mary,

i think i know what you mean,

for some reason i couldn't view the immediate pane, but got it sorted.........

this is what i have in my immediate pane:
[PHP]
INSERT INTO tab_main (username, cost_centre, number, phone_model, imei, puk_code, sim_no, date_issued, notes, tariff, call_int, roam) VALUES ('','','','','','','',##,'','',,);[/PHP]

[NeoPa]

sorry Mary,

i think i know what you mean,

for some reason i couldn't view the immediate pane, but got it sorted.........

this is what i have in my immediate pane:
[PHP]
INSERT INTO tab_main (username, cost_centre, number, phone_model, imei, puk_code, sim_no, date_issued, notes, tariff, call_int, roam) VALUES ('','','','','','','',##,'','',,);[/PHP]

To get to the 'Immediate' pane easily just type Ctrl-G.
Now, look at the string you've posted.
You will see that there is no real data there. Even the date is empty (this will cause error).
Can you tell us why there is no data there?
Was thae form filled in before you executed this code?
You need to check for empty data in the code before running this APPEND query.

[Beany]

Sorry Neo and Mary,

i didnt fill in the form before i clicked the add button...
i have done now and ive got the following:

[PHP]INSERT INTO tab_main (username, cost_centre, number, phone_model, imei, puk_code, sim_no, date_issued, notes, tariff, call_int, roam) VALUES ('John Parker','London','0112233333','Blackberry 7100v','1212121','5544543444','34555534323',#01/01/2001#,'hi','free',-1,);[/PHP]


whats wrong?

[NeoPa]

INSERT INTO tab_main (username, cost_centre, number, phone_model, imei, puk_code, sim_no, date_issued, notes, tariff, call_int, roam) VALUES ('John Parker','London','0112233333','Blackberry 7100v','1212121','5544543444','34555534323',#01/01/2001#,'hi','free',-1,);

There is no value provided for roam. This should be set to TRUE or FALSE (-1 or 0).

[Beany]

Ive got into my table tab_main and changed the roam to true or false.

but it didnt sort out the error im getting, and also in the immediate pane, it still is showing as -1!(i selected roam before i executed it)

????

[MMcCarthy]

Ive got into my table tab_main and changed the roam to true or false.

but it didnt sort out the error im getting, and also in the immediate pane, it still is showing as -1!(i selected roam before i executed it)

????

What NeoPa was trying to tell you is that you haven't got any value showing for roam. The Yes/No should show as -1 or 0 the same as int_call. Only one of the values is showing. At a guess I would say that you are using the wrong control name for roam. Open the properties for roam and under the other tab see what is showing for Name. I would guess it's not roam.

Mary

[NeoPa]

Beany,

Can you see why it is so important to check your code and data?
Debug.Printing is an invaluable tool to help you determine that you've got one part right before you move on to building another part thereupon (using the first part as a building block of the next one).

[Beany]

thanks for advice Neo, will bear that in mind.............

Mary, both names are correct.........

if you look below, whilst filling in the form i selected both roam and call_int and i got following result:

[PHP]INSERT INTO tab_main (username, cost_centre, number, phone_model, imei, puk_code, sim_no, date_issued, notes, tariff, call_int, roam) VALUES ('Test','london','010101001','Blackberry 7130v','11111111','22222222','3333333',#01/01/2002#,'asasas','free',-1,-1);[/PHP]

if i didnt select them, would it show 0?

[NeoPa]

thanks for advice Neo, will bear that in mind.............

Mary, both names are correct.........

if you look below, whilst filling in the form i selected both roam and call_int and i got following result:

[PHP]INSERT INTO tab_main (username, cost_centre, number, phone_model, imei, puk_code, sim_no, date_issued, notes, tariff, call_int, roam) VALUES ('Test','london','010101001','Blackberry 7130v','11111111','22222222','3333333',#01/01/2002#,'asasas','free',-1,-1);[/PHP]

if i didnt select them, would it show 0?

Yes (+extra chars for the ten)

[Beany]

if i dont select both, it doesnt show 0..............

is this the reason why i am getting the error message?

[MMcCarthy]

if i dont select both, it doesnt show 0..............

is this the reason why i am getting the error message?

Yes, in the properties of the checkboxes set the default value to 0. This should solve the problem.

Mary

[Beany]

now it does show the correct info but..........

im still getting the same error message( see post #33)

[NeoPa]

if i dont select both, it doesnt show 0..............

is this the reason why i am getting the error message?

Check the 'Triple State' property of the CheckBox.
This should be set to 'No'. The default property should then not matter.

[NeoPa]

now it does show the correct info but..........

im still getting the same error message( see post #33)

To find the problem in your current code, we need to see your current code ;).
If you post what you're currently using then we can look at it for you.

[Beany]

its been set to 0.........

anyway the code for the add button:

[PHP]Private Sub addrec_Click()

Dim strSQL As String

strSQL = "INSERT INTO tab_main (username, cost_centre, number, phone_model, " & "imei, puk_code, sim_no, date_issued, notes, tariff, call_int, roam) " & "VALUES ('" & Me.username & "','" & Me.cost_centre & "','" & Me.number & "','" & Me.phone_model & "','" & Me.imei & "','" & Me.puk_code & "','" & Me.sim_no & "',#" & Me.date_issued & "#,'" & Me.notes & "','" & Me.tariff & "'," & Me.call_int & "," & Me.roam & ");"
Debug.Print strSQL
DoCmd.RunSQL strSQL


End Sub[/PHP]