By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 2,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

Combining Code

100+
P: 173
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?
Nov 29 '06 #1
Share this Question
Share on Google+
74 Replies


NeoPa
Expert Mod 15k+
P: 31,616
Expand|Select|Wrap|Line Numbers
  1. Private Sub AddButton_Click()
  2.     'Do validation checks
  3.     If blnValidate Then
  4.         MsgBox strValidate
  5.     Else
  6.         'Add new record
  7.         DoCmd.GoToRecord , , acNewRec
  8.     End If
  9. End Sub
Nov 30 '06 #2

100+
P: 173
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]
Nov 30 '06 #3

100+
P: 173
any1 have an idea??
Dec 4 '06 #4

markmcgookin
Expert 100+
P: 648
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?
Dec 4 '06 #5

NeoPa
Expert Mod 15k+
P: 31,616
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub addrec_Click()
  2.     If Nz(Me.username, "") = "" Then
  3.         MsgBox "These item(s) were not filled out " & _
  4.                "and are required:" & vbCrLf & vbCrLf & _
  5.                "Username" & vbCrLf
  6.     Else
  7.         'Add new record
  8.         DoCmd.GoToRecord , , acNewRec
  9.     End If
  10. 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.
Dec 4 '06 #6

100+
P: 173
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?
Dec 4 '06 #7

NeoPa
Expert Mod 15k+
P: 31,616
Then you would use a string variable.
Give me some time and I'll post some code to give an example.
Dec 4 '06 #8

NeoPa
Expert Mod 15k+
P: 31,616
Assuming IMEI code is numeric & Phone Number is a string :
Expand|Select|Wrap|Line Numbers
  1. Private Sub addrec_Click()
  2.     Dim strMsg As String
  3.     Dim blnValidate As Boolean
  4.  
  5.     strMsg = "These item(s) were not filled out " & _
  6.              "and are required:" & vbCrLf & vbCrLf
  7.     blnValidate = False
  8.     If Nz(Me.username, "") = "" Then
  9.         blnValidate = True
  10.         strMsg = strMsg & "Username" & vbCrLf
  11.     ElseIf Nz(Me.Phone_No, "") = "" Then
  12.         blnValidate = True
  13.         strMsg = strMsg & "Phone Number" & vbCrLf
  14.     ElseIf Nz(Me.IMEI_Code, 0) = 0 Then
  15.         blnValidate = True
  16.         strMsg = strMsg & "IMEI" & vbCrLf
  17.     End If
  18.     If blnValidate Then
  19.         MsgBox strMsg
  20.     Else
  21.         'Add new record
  22.         DoCmd.GoToRecord , , acNewRec
  23.     End If
  24. End Sub
Dec 4 '06 #9

100+
P: 173
ur good...........and quick.........

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

appreciate it Neo
Dec 4 '06 #10

NeoPa
Expert Mod 15k+
P: 31,616
Shhhhhhh.
My boss will hear.
;)
Dec 4 '06 #11

100+
P: 173
Neo is the Hero.................


brilliant u got it working for me...............
Dec 4 '06 #12

100+
P: 173
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????
Dec 4 '06 #13

NeoPa
Expert Mod 15k+
P: 31,616
That's because I got it wrong :embarrassed:
Try this instead :
Expand|Select|Wrap|Line Numbers
  1. Private Sub addrec_Click()
  2.     Dim strMsg As String
  3.     Dim blnValidate As Boolean
  4.  
  5.     strMsg = "These item(s) were not filled out " & _
  6.              "and are required:" & vbCrLf & vbCrLf
  7.     blnValidate = False
  8.     If Nz(Me.username, "") = "" Then
  9.         blnValidate = True
  10.         strMsg = strMsg & "Username" & vbCrLf
  11.     End If
  12.     If Nz(Me.Phone_No, "") = "" Then
  13.         blnValidate = True
  14.         strMsg = strMsg & "Phone Number" & vbCrLf
  15.     End If
  16.     If Nz(Me.IMEI_Code, 0) = 0 Then
  17.         blnValidate = True
  18.         strMsg = strMsg & "IMEI" & vbCrLf
  19.     End If
  20.     If blnValidate Then
  21.         MsgBox strMsg
  22.     Else
  23.         'Add new record
  24.         DoCmd.GoToRecord , , acNewRec
  25.     End If
  26. End Sub
Don't forget to change all variables (Fields; Controls etc) to the correct names as used in your database.
Dec 4 '06 #14

100+
P: 173
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?
Dec 4 '06 #15

NeoPa
Expert Mod 15k+
P: 31,616
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.
Dec 4 '06 #16

100+
P: 173
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 :)
Dec 4 '06 #17

NeoPa
Expert Mod 15k+
P: 31,616
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.
Dec 4 '06 #18

100+
P: 173
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??
Dec 5 '06 #19

100+
P: 173
anyone????????????????????????
Dec 5 '06 #20

NeoPa
Expert Mod 15k+
P: 31,616
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.
Dec 5 '06 #21

NeoPa
Expert Mod 15k+
P: 31,616
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.     Dim strMsg As String
  3.  
  4.     strMsg = "These item(s) were not filled out " & _
  5.              "and are required:" & vbCrLf & vbCrLf
  6.     If Nz(Me.username, "") = "" Then
  7.         Cancel = True
  8.         strMsg = strMsg & "Username" & vbCrLf
  9.     End If
  10.     If Nz(Me.Phone_No, "") = "" Then
  11.         Cancel = True
  12.         strMsg = strMsg & "Phone Number" & vbCrLf
  13.     End If
  14.     If Nz(Me.IMEI_Code, 0) = 0 Then
  15.         Cancel = True
  16.         strMsg = strMsg & "IMEI" & vbCrLf
  17.     End If
  18.     If Cancel Then MsgBox strMsg
  19. End Sub
New record added automatically if Cancel = False
Dec 5 '06 #22

100+
P: 173
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)
Dec 5 '06 #23

100+
P: 173
i need help? any1?
Dec 6 '06 #24

NeoPa
Expert Mod 15k+
P: 31,616
i need help? any1?
I'm out tonight.
Do another bump tomorrow and I'll pick it up then.
Dec 6 '06 #25

MMcCarthy
Expert Mod 10K+
P: 14,534
In the Click event of the Add button you can use an INSERT query.

Example:

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3.    strSQL = "INSERT INTO TableName (NumberField, DateField, TextField) VALUES (" & _
  4.       Me.NumberTextbox & ",#" & Me.DateTextbox & "#,'" & Me.TextTextbox & "');"
  5.    DoCmd.RunSQL strSQL
  6.  
Pay close attention to the different syntax for different types of values.

Mary
Dec 7 '06 #26

100+
P: 173
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
Dec 7 '06 #27

NeoPa
Expert Mod 15k+
P: 31,616
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'?
Dec 7 '06 #28

100+
P: 173
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???
Dec 7 '06 #29

MMcCarthy
Expert Mod 10K+
P: 14,534
Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT INTO tab_main (username, cost_centre, number, phone_model, " & _
  2.          "imei, puk_code, sim_no, date_issued, notes, tariff, call_int, roam) " _
  3.          "VALUES ('" & Me.username & "','" & Me.cost_centre & "','" & _
  4.          Me.number & "','" & Me.phone_model & "','" & Me.imei & "','" & _
  5.          Me.puk_code & "','" & Me.sim_no & "',#" & Me.date_issued & "#,'" & _
  6.          Me.notes & "','" & Me.tariff & "'," & Me.call_int & "," & Me.roam & ");"
  7.  
  8. DoCmd.RunSQL strSQL 
  9.  
Mary
Dec 7 '06 #30

100+
P: 173
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?
Dec 7 '06 #31

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 7 '06 #32

100+
P: 173
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
Dec 7 '06 #33

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 7 '06 #34

100+
P: 173
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?
Dec 8 '06 #35

100+
P: 173
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]
Dec 8 '06 #36

NeoPa
Expert Mod 15k+
P: 31,616
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.
Dec 8 '06 #37

100+
P: 173
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?
Dec 8 '06 #38

NeoPa
Expert Mod 15k+
P: 31,616
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).
Dec 8 '06 #39

100+
P: 173
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)

????
Dec 8 '06 #40

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 9 '06 #41

NeoPa
Expert Mod 15k+
P: 31,616
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).
Dec 9 '06 #42

100+
P: 173
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?
Dec 9 '06 #43

NeoPa
Expert Mod 15k+
P: 31,616
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)
Dec 9 '06 #44

100+
P: 173
if i dont select both, it doesnt show 0..............

is this the reason why i am getting the error message?
Dec 9 '06 #45

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 9 '06 #46

100+
P: 173
now it does show the correct info but..........

im still getting the same error message( see post #33)
Dec 9 '06 #47

NeoPa
Expert Mod 15k+
P: 31,616
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.
Dec 9 '06 #48

NeoPa
Expert Mod 15k+
P: 31,616
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.
Dec 9 '06 #49

100+
P: 173
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]
Dec 9 '06 #50

74 Replies

Post your reply

Sign in to post your reply or Sign up for a free account.