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]

Sorry Beany, should have said more clearly.
Can you post what's in the Debug window for the Debug.Print line too please.

[MMcCarthy]

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]

[Beany]

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

[MMcCarthy]

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

[Beany]

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]

[MMcCarthy]

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.

INSERT INTO [tab_main] ([username], [cost_centre], [number], [phone_model], [imei], [puk_code], [sim_no], [date_issued], [notes], [tariff], [call_int], [roam]) VALUES ........etc.
 

Mary

[Beany]

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?

[MMcCarthy]

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

[Beany]

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?????????????????????

[NeoPa]

That's because it's working now.
To hide that message put

DoCmd.SetWarnings = False

but remember to set it back to True again at the end of your running code.

[MMcCarthy]

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?????????????????????

[MMcCarthy]

That's because it's working now.
To hide that message put

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

[Beany]

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?

[NeoPa]

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

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.SetWarnings = False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings = True
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.

[MMcCarthy]

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

[Beany]

Neo, im still getting the same error???? even with the above code

[Beany]

Mary your way worked!!!!

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

how did you lot learn this much?

any recommendations for beginners?

[NeoPa]

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.

[MMcCarthy]

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

[NeoPa]

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).

[Beany]

thats funny................

[MMcCarthy]

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

[MMcCarthy]

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

[NeoPa]

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.

[Beany]

cool.......

will bear that in mind...........thanks