I have a button at the bottom of a form that sends all of the information into a table. The information sent the table is useless without a name. The button is defined and all of the fields are going into the table without a problem, but I need to have a control that says it cannot be submitted without a name. The button is set up with an "On Click procedure, but I can't figure out how to write an If/Then statement that would prevent this. So far it just looks like this: - Private Sub SaveButton_Click()
-
Dim rst As Recordset, db As DAO.Database
-
-
Set db = CurrentDb()
-
Set rst = db.OpenRecordset("main")
-
'MONDAY OUTPUT
-
rst.AddNew
-
rst!Day1 = "Monday"
-
rst!ProjectMonday1 = Me.Combo1463.Column(0)
-
rst!AFEMonday1 = Me.Text1455.Value
-
rst!ProjectMonday2 = Me.Combo1465.Column(0)
-
rst!AFEMonday2 = Me.Text1461.Value
-
rst!ProjectMonday3 = Me.Combo1454.Column(0)
-
rst!AFEMonday3 = Me.Text1462.Value
-
rst!DATE = Me.Text1457.Value
-
rst!EMPLOYEE = Me.EMPLOYEE.Value
...and so on through all the days of the week. I would imagine I'd need something before the "Dim rst As Recordset, db As DAO.Database" to prevent the form being submitted without a name? Very new to VBA...
11  1672  zmbd 5,501
Expert Mod 4TB
percman
This appears to be VBA and not VB...
You would check the value of the control in the on-click event. I would insert this at line3 in your posted code.
You should also read: What is Null? as this will help you write the proper code to check for both a true null and zero-length entries.
Try the following: - Private Sub SaveButton_Click()
-
Dim rst As Recordset
-
Dim db As DAO.Database
-
-
'Must have a Name in order to proceed
-
If IsNull(Me![EMPLOYEE]) Then Exit Sub
-
-
Set db = CurrentDb()
-
Set rst = db.OpenRecordset("main")
-
-
With rst
-
'MONDAY OUTPUT
-
.AddNew
-
!Day1 = "Monday"
-
!ProjectMonday1 = Me.Combo1463.Column(0)
-
!AFEMonday1 = Me.Text1455.Value
-
!ProjectMonday2 = Me.Combo1465.Column(0)
-
!AFEMonday2 = Me.Text1461.Value
-
!ProjectMonday3 = Me.Combo1454.Column(0)
-
!AFEMonday3 = Me.Text1462.Value
-
!Date = Me.Text1457.Value
-
!EMPLOYEE = Me.EMPLOYEE.Value
-
.Update
-
End With
-
-
rst.Close
-
Set rst = Nothing
-
End Sub
Thanks Adezii!
One thing that is a little tricky is that the code above is only a sample of the entire string. After "Me.EMPLOYEE.Value", there is a whole bunch of code that records other aspects - hours, percentage of time, etc. for all 7 days, and then there is yet more code that resets the entire form to blank. I tried to interpret what you wrote to go at the end of the entire string but an error came up "Update or CancelUpdate without AddNew or Edit"
Would the ".update" and "End With" have to go where you wrote it here or can it go to the end somehow? I'm trying to get the form to check just the "EMPLOYEE" value at the top of the form, not for each individual day. The individual day employee input is trivial; just needed for the main form for output. Thanks for you help!
You would need to post the Code in its entirety for us to see exactly what is going on.
So the beginning of the code points to the table that the entries are going to, and the second part of the code resets all of those entries on the form after the data has been sent. It works fine, just need to figure out how to get the stop in if there is no employee name.
zmbd 5,501
Expert Mod 4TB
Line 17: rst!EMPLOYEE = Me.EMPLOYEE.Value
This the name you are concerned about?
If so then after Line 6 in post#6 code, you might try inserting: If ((Me![EMPLOYEE]+"")="") Then Exit Sub
For insight on this What is Null?
This is very much the same as what ADeZii's code does with the "isnull()" function in his code in post#3; however, I'm trying to trap for both null and zero-length string. For insight on this you should look at: What is Null?
I understand what you're saying about line 17, but line 6 is currently showing "Set rst = db.OpenRecordset("main")". Are you saying replace line 6 with what you're writing there, or ADD a line?
zmbd 5,501
Expert Mod 4TB
Sorry, didn't scroll up enough... was looking at ADezii's code block in post #3 (#^.^#) - I'm senior staff in a lab and we had an equipment failure that I had to go take a look at and didn't proof my post.
Insert the modification after Line#6 in the code you posted in Post#6
(wow what a lot of 6's today.... I think I'll go home and hide in the basement the rest of the day if it's going to keep going this way! - Anyway, I'll fix my last post.)
-z
I tried something else in the mean time. I placed the code you mentioned BELOW line 17 and it stops the submit, plus if there IS a name it WILL submit. So that part works. One last crucial step - I am trying to create a message box that will say "You must have a name selected in the Employee area to continue", but how do I make that work? Here's what I wrote: - rst!EMPLOYEE = Me.EMPLOYEE.Value
-
If ((Me![EMPLOYEE] + "") = "") Then Private Sub cmdMessageBox_Click()
-
MsgBox "You must select a name from the Employee dropdown to continue"
-
Exit Sub
-
End If
...but it gives a syntax error when trying to run. Are there too many "subs" in there? Is there a different way to write the box code? All it needs to do is have "OK" and force the user to enter a name to submit.
zmbd 5,501
Expert Mod 4TB
1) Not the best to stop your record in the middle of a new record creation. HIGHLY recomend that you stop the execution PRIOR to even opening the record set.
2) - If ((Me![EMPLOYEE] + "") = "") Then Private Sub cmdMessageBox_Click()
This is malformed."Private" is a reserved token used only in the header of a sub, function, module level typecast.
Not sure if you want to call the code behind the click event for you command button "MessageBox" however, I've pulled it down from the weird spot and cleaned up the syntax. - If ((Me![EMPLOYEE] + "") = "") Then
-
Call cmdMessageBox_Click()
-
MsgBox "You must select a name from the Employee dropdown to continue"
-
Exit Sub
-
End If
Between my last post and your most recent reply, I did just that; moved the code about the employee error message to the beginning. It worked! - Private Sub SaveButton_Click()
-
Dim rst As Recordset, db As DAO.Database
-
-
Set db = CurrentDb()
-
If ((Me![EMPLOYEE] + "") = "") Then
-
MsgBox "You must select a name from the Employee dropdown to continue", vbInformation
-
Exit Sub
-
End If
-
Set rst = db.OpenRecordset("main")
-
'MONDAY OUTPUT
-
rst.AddNew
-
rst!Day1 = "Monday"
-
rst!ProjectMonday1 = Me.Combo1463.Column(0)
-
rst!AFEMonday1 = Me.Text1455.Value
-
rst!ProjectMonday2 = Me.Combo1465.Column(0)
-
Thanks for the advice, it really set me on the right path!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: dvorett |
last post by:
I know this topic has been discussed a lot but after going through the
old posts, I'm still having trouble with the code. I have a photo name
field which refers to the location of the photo on the...
|
by: Larry R Harrison Jr |
last post by:
I have designed databases but have never come across any complications due
to the ridiculous situation of a hyphenated last name. As a database
designer (very junior level) I disdain anything...
|
by: Jain |
last post by:
Hi,
How can I make it compulsory for data to be entered into a field in a form.
Eg it won't allow the user to close the form without filling in a particular
field. An error message appears...
|
by: Peter Dunker |
last post by:
Hi,
I will write ANSI C89.
Is the following struct defenition correct ?
I wrote it with VC6(Windows IDE) and at first no Problem.
As I changed a compiler switch to 'no language extension',
the...
|
by: Alan Silver |
last post by:
Hello,
I'm getting an odd validation error from VWD. As I understand it, an
opening ASP.NET for tag is supposed to look like...
<form runat="server">
with an optional ID attribute. VWD...
|
by: Chuck Anderson |
last post by:
I'm pretty much a JavaScript novice. I'm good at learning by example
and changing those examples to suit my needs. That said ....
..... I have some select fields in a form I created for a...
|
by: NeilIanBaker |
last post by:
Hello
I am trying to select the first name and surname from a name field where the name is in the form of;
eg. Mrs Marilyn Payne
Mrs Mary Swanton
Ms EM Lomas
Lt Col R...
|
by: dynamo |
last post by:
it seems to me that when the name field of an input in a form has an entry with a dot in it,it changes the dot to a dash on submitting it.Is there anyway to get over this problem.Thanks for any...
|
by: Sam Zuhbi |
last post by:
Hi,
I was wondering how do I make the next record button on my access 2007 form stop at the last record, I want to make sure that if it does not create a new record if it reaches the last record...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
