469,281 Members | 2,484 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,281 developers. It's quick & easy.

Combining Code

173 100+
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
74 4926
NeoPa
32,173 Expert Mod 16PB
Sorry Beany, should have said more clearly.
Can you post what's in the Debug window for the Debug.Print line too please.
Dec 9 '06 #51
MMcCarthy
14,534 Expert Mod 8TB
You only need the & _ when continuing on the next line.

[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 #52
Beany
173 100+
Sorry Neo

here we are:

[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','07777777777','Ericsson R310S','12121212','2323232323','3434343434343',#01/03/2004#,'help me','free',0,0);[/PHP]

Mary, there is no &_? (or shall i go to specsavers??) :)

thanks peeps
Dec 9 '06 #53
MMcCarthy
14,534 Expert Mod 8TB
Just copy and paste my code exactly as it is in the last post and then tell me what is in the immediate pane result. There is something wrong with the date you put in the last post is this just a typing error.

Mary
Dec 9 '06 #54
Beany
173 100+
ive copied your code and got the following in the 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 ('Hello','Leeds','121212121','Blackberry 7100v','1213344444','444455445443','334434434',#01/03/2006#,'asasas','monthly',0,0)[/PHP]
Dec 9 '06 #55
MMcCarthy
14,534 Expert Mod 8TB
There doesn't appear to be any mistakes in the code. I can't understand why it's not working for you.

The only other thing I can suggest is putting square brackets around the table name and each of the field names but that shouldn't make any difference unless there is aVBA protected word in there somewhere.

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [tab_main] ([username], [cost_centre], [number], [phone_model], [imei], [puk_code], [sim_no], [date_issued], [notes], [tariff], [call_int], [roam]) VALUES ........etc.
  2.  
Mary
Dec 9 '06 #56
Beany
173 100+
ive got a userID field in my table tab_main..............

but i dont have a field for that in my form.........

that doesnt make a difference does it?
Dec 10 '06 #57
MMcCarthy
14,534 Expert Mod 8TB
ive got a userID field in my table tab_main..............

but i dont have a field for that in my form.........

that doesnt make a difference does it?
That's fine as long as userID is an autonumber. Did the square brackets make any difference?

Mary
Dec 10 '06 #58
Beany
173 100+
some good news the square brackets adds the details into the table......

but before it does add i get the message ....

You are about to append 1 row(s).

Once you click Yes, you can't use the Undo command to reverse the changes.
Are you sure you want to append the selected rows?


YES NO

Why is this?????????????????????
Dec 10 '06 #59
NeoPa
32,173 Expert Mod 16PB
That's because it's working now.
To hide that message put
Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings = False
but remember to set it back to True again at the end of your running code.
Dec 10 '06 #60
MMcCarthy
14,534 Expert Mod 8TB
The undo command is used for forms to undo the user entry before a record is saved. Once you append a record to the table you can't undo it.

Mary

some good news the square brackets adds the details into the table......

but before it does add i get the message ....

You are about to append 1 row(s).

Once you click Yes, you can't use the Undo command to reverse the changes.
Are you sure you want to append the selected rows?


YES NO

Why is this?????????????????????
Dec 10 '06 #61
MMcCarthy
14,534 Expert Mod 8TB
That's because it's working now.
To hide that message put
Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings = False
but remember to set it back to True again at the end of your running code.
Hi Ade

I'm supposed to be working and doing anything to avoid it. HELP!!

Mary
Dec 10 '06 #62
Beany
173 100+
ive inserted the DoCmd.SetWarnings = False into the code...

is that the right place?

[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
DoCmd.SetWarnings = False
End Sub[/PHP]

im getting a message sayin

Compile Error:

Argument not optional


and what did you mean when......... put it back to true when running the code?
Dec 10 '06 #63
NeoPa
32,173 Expert Mod 16PB
ive inserted the DoCmd.SetWarnings = False into the code...

is that the right place?

[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
DoCmd.SetWarnings = False
End Sub[/PHP]

im getting a message sayin

Compile Error:

Argument not optional


and what did you mean when......... put it back to true when running the code?
Oh BEANY!
What I meant was
Expand|Select|Wrap|Line Numbers
  1. Private Sub addrec_Click()
  2. Dim strSQL As String
  3. 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 & ")"
  4. ''Debug.Print strSQL
  5.  
  6. DoCmd.SetWarnings = False
  7. DoCmd.RunSQL strSQL
  8. DoCmd.SetWarnings = True
  9. End Sub
Now, what I'm trying to encourage you to do is to sit and think about your problems first.
We can help, but you need to try first - otherwise we are just a crutch for you, and you will never learn to walk. We want you to learn from what we put in here, not just use the code. You understand?

Mary,

I'll try to keep things up-to-date but I'm not checking all the time as my head's buried (No - not there! In some code.)
I should be able to respond to most though in time.
You focus on your work.

-Ade.
Dec 10 '06 #64
MMcCarthy
14,534 Expert Mod 8TB
Firstly, you don't use the = sign as the False is a parameter.

Think of it logically. You are turning the warnings off before you run the code and back on after you run it so ....

[PHP]
Private Sub addrec_Click()
Dim strSQL As String

DoCmd.SetWarnings False

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
DoCmd.SetWarnings True

End Sub
[/PHP]


Mary
Dec 10 '06 #65
Beany
173 100+
Neo, im still getting the same error???? even with the above code
Dec 10 '06 #66
Beany
173 100+
Mary your way worked!!!!

amazing.....thank you and Neo..................

how did you lot learn this much?

any recommendations for beginners?
Dec 10 '06 #67
NeoPa
32,173 Expert Mod 16PB
Firstly, you don't use the = sign as the False is a parameter.

Think of it logically. You are turning the warnings off before you run the code and back on after you run it so ....
That was my bad Mary.
That's how I did it in an earlier post :(.
Beany was just following my lead - oops.
Dec 10 '06 #68
MMcCarthy
14,534 Expert Mod 8TB
Neo, im still getting the same error???? even with the above code
Beany

Did you try my code.

BTW, Adrian I'm not saying you're wrong :) but you have had great fun telling me how many mistakes I made yesterday when I had a hangover that I couldn't resist pointing out one of your rare ones.

Mary
Dec 10 '06 #69
NeoPa
32,173 Expert Mod 16PB
Beany

Did you try my code.

BTW, Adrian I'm not saying you're wrong :) but you have had great fun telling me how many mistakes I made yesterday when I had a hangover that I couldn't resist pointing out one of your rare ones.

Mary
Fun it was - and is the other way around too.
Heard a great one this morning from an Ausi radio station...
Q. What's the difference between a Pom and a Jumbo Jet's engine?
A. The engine stops whining when it reaches Australia.
Cracked me up (the b*stards).
Dec 10 '06 #70
Beany
173 100+
thats funny................
Dec 10 '06 #71
MMcCarthy
14,534 Expert Mod 8TB
Fun it was - and is the other way around too.
Heard a great one this morning from an Ausi radio station...
Q. What's the difference between a Pom and a Jumbo Jet's engine?
A. The engine stops whining when it reaches Australia.
Cracked me up (the b*stards).
Have to tell Killer that one.

Since I'm not a Pom I can laugh wholeheartedly. :)

Mary
Dec 10 '06 #72
MMcCarthy
14,534 Expert Mod 8TB
Mary your way worked!!!!

amazing.....thank you and Neo..................

how did you lot learn this much?

any recommendations for beginners?
Hi Beany

Unfortunately, there is no easy way to pick this stuff up as it comes from years of experience learning the hard way how these things work.

The best recommendation I can give you is that when you have a question on how to do something try to find answers already posted on the web in forums like this one and then try to follow for yourself what's going on and why.

When you run into trouble we'll be here but you will learn much more if you try to figure it out for yourself by playing around with the code and see what works and what doesn't.

Mary
Dec 10 '06 #73
NeoPa
32,173 Expert Mod 16PB
Beany,

Pay close attention to what Mary says. She knows what she's talking about.
Many people forget to use the Help system too.
You can get a long way by using what's included in the software as Help.
I've learned most of what I know by playing around (I wish! - I mean within Access) with things and reading up on the correct syntax from Help.
Dec 10 '06 #74
Beany
173 100+
cool.......

will bear that in mind...........thanks
Dec 10 '06 #75

Post your reply

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

Similar topics

2 posts views Thread by Brian | last post: by
3 posts views Thread by Unregistered | last post: by
3 posts views Thread by alwayswinter | last post: by
5 posts views Thread by M.Stanley | last post: by
5 posts views Thread by Tristan Miller | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.