Modules & VBA :: Bring Up Msgbox If Certain Fields Blank And Not Run The Macro In Form
Jan 20, 2015
I have a form with various text, date and combo controls. There is a button at the button that runs a macro (Close NB) at the bottom. What I'm trying to do is bring up a msgbox if certain fields are blank and not run the macro. I only want the macro to run if all the fields specified have data in them.
The fields are :
cmb_cliname
cmb_disease
cmb_projectType
cmb_ProposalStatus
The on click code is:
If (Me.cmb_cliname Is Null) Then
MsgBox "Please fill in the relevant details",
ElseIf (Me.cmb_Disease Is Null) Then
MsgBox "Please fill in the relevant details"
[Code] .....
View Replies
ADVERTISEMENT
May 11, 2013
I created this function to manage a MsgBox containing all required fields with no data:
Code:
Function FormValidation(TheForm As Form) As Boolean
Dim boolResponse As Boolean
Dim strError As Variant
Dim ctl As Control
boolResponse = True
strError = Null
[code]...
Then, I have a Form_BeforeUpdate event, where I wanna place the function, which contains also some VBA code to manage duplicates records:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rst As DAO.Recordset, dbs As DAO.Database, strICAO As String
Dim ctl As Control, txtMsg As String
[code]....
Now, how to add the function in this event to get these two results:
1. if required fields are blank and I press OK on the MsgBox, the routine must stop;
2. the focus must go in the first required blank control.
I tried some options but I get different kind of malfunctions: no custom message for duplicate records but only the access default one, "go to next record" feature with tab key not working and so on.
View 6 Replies
View Related
Nov 14, 2013
I'm creating a search form to filter out data based on certain inputs. My VBA code looks like:
Code:
Private Sub Command18_Click()
On Error GoTo errorcatch
Me.Filter = "([Experiments.Log] Like ""*" & Me.Text21 & "*"") AND ([Expdate] Like ""*" & Me.Text22 & "*"") AND ([BaseSolution] Like ""*" & Me.Text24 & "*"") AND([AddCom] Like ""*" & Me.Text25 & "*"") AND ([Test] Like ""*" & Me.Text26 & "*"") AND ([Plan] Like ""*" & Me.Text23 & "*"")"
Me.FilterOn = True
Exit Sub
errorcatch:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub
However, the output does not include records where other fields are blank. I have read that I may need to use Is Null but am not sure how to.
View 3 Replies
View Related
Mar 10, 2005
hi,
i have a form, which when opened, gives me a blank record. which is what i asked it to do in the OnOpen property. however, i also need a msg box to popup - but after the form has been opened. i tried placing both pieces of 'code' into the OnOpen property, however, the popup displays first, then the form opens.
so currently i have:
Private Sub Form_Load()
Select Case MsgBox("Please select the Registration Number from the drop down menu or type it in", vbOKOnly, "Select Registration Number")
End Select
End Sub
--------------------------------------------------------------------------
Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord , , acNewRec
End Sub
--------------------------------------------------------------------------
but it does the same thing. so, is there a way to make the msg box appear after the form opens with a blank record?
thank you in advance :D
View 3 Replies
View Related
Mar 24, 2014
I try to go throgh all columns and replace all blank fields with 0.Somehow this doesn't work:
Code:
Sub TEST()
Dim str As String
Dim rs As DAO.Recordset
Dim fld As DAO.Field
[code]...
View 7 Replies
View Related
Aug 17, 2013
I am making a database which has its output in PowerPoint. I have set it up so that each field value is shown on a different slide with the code for each slide like this:
Code:
With .Slides.Add(rs.AbsolutePosition + 1, ppLayoutLargeObject)
.FollowMasterBackground = False
.Background.Fill.Solid
.Background.Fill.ForeColor.RGB = RGB(0, 0, 0)
[code]....
This works fine until a filed is blank (which they sometimes are) where it then crashes with error 94 invalid use of null. What I was thinking was putting the whole thing above in an If-then-else statement so that a blank field does not produce a slide, something like:
Code:
If IsNull(CStr(rs.Fields("Song 1 chosen_Verse 2").Value)) Then
Else
With .Slides.Add(rs.AbsolutePosition + 1, ppLayoutLargeObject)
.FollowMasterBackground = False
.Background.Fill.Solid
.Background.Fill.ForeColor.RGB = RGB(0, 0, 0)
[Code] .....
This doesn't work though - at least not like I have written it!
View 5 Replies
View Related
Dec 19, 2013
I got one months table containing a reporting_month ,timeperiod and an Index column ID with data type Autonumber.Basically I want to search through the table whenever the User types in a new reporting month or timeperiod over the dialogue.Now I want to realize the following options:
1. The user types in a new reporting month, when a record in the months table exists with a timeperiod and a blank reporting month field, it should be assigned there. For example the User types in reporting month = 032014 it should be assigned to the Time_Period Value = 042014-032015
2. Vice versa, the user types in a time period. This value shuold be assigned to the blank field beneath the existing reporting month.
View 14 Replies
View Related
Feb 28, 2014
How can I check if a table contains blank fields/values.
If there are blank fields I want to replace them with 0.
View 4 Replies
View Related
Mar 20, 2007
I created a database for a survey. There are a lot of number fields and a few yes/no fields. I did a query to give an average for the number fields and a sum of the yes for the yes/no fields. I get the right number for the yes/no fields, but the number is negative. What do I need to do so that the number is positive?
Thanks
Robyn
View 7 Replies
View Related
May 7, 2015
I have a query that does a great job at fining duplicate master records
How to this query so that it fining duplicate master records like the query show and add one more record that will link up as shown in the example.
[sql]SELECT subqry2.maxdatereferral
,[PSB Accout Linkingqry].master
,[PSB Accout Linkingqry].RecordNumber
,[PSB Accout Linkingqry].FirstName
,[PSB Accout Linkingqry].LastName
[Code] .....
View 2 Replies
View Related
May 30, 2015
I'm creating a query from one table using two fields that require certain records to NOT bring up records that have the following text:
One table, two fields
First field ECO LifeCycle Status Criteria is Not "ERP UPDATE" or "CANCELLED"
Second field ECR LifeCycle Status Criteria is Not "COMPLETE"
When I put the Not Criteria in the first field only I get a result.
When I put the Not Criteria in both fields I get no results.
View 9 Replies
View Related
Feb 12, 2006
How do I make a form open with blank input fields?. The one I made always has a prior record displayed.
View 1 Replies
View Related
Dec 8, 2013
I am trying to write a more complex macro that will start another macro at a preset time, however I am getting stopped at the first hurdle - getting a macro to run another macro.
Here is the code i am using at the moment, all I want to do currently is click the first button, then get the second macro to execute. But no luck, getting error 2157 "cannot find the procedure"
Code:
Private Sub Command3_Click()
MsgBox "1st macro running", vbExclamation, "Note"
Application.Run "teststart1"
' Application.OnTime TimeValue("19:55:00"), "teststart1"
[code]....
View 1 Replies
View Related
Oct 7, 2004
Hey Guys,
This one could be a tough one.
I have a form that displays products information.
The products table has about 30 fields but not every field is used in every record.
Depending on the product I would like those fields that are blank not to appear on the form and then for the form to be reorganized so there are no odd spaces.
Is this possible?
Thanks!
View 5 Replies
View Related
Mar 15, 2006
Hello guys
How can get blank fields on opening or loading my form?. I don't want to prevent data entry option.
Please help me
Thanks:)
View 14 Replies
View Related
Jul 10, 2006
I have a form linked to an employees table. I have a search function on this form which allows you to search for a particular employee.
At the moment when i open my form, it always displays the first record!
How can I make my form open with blank fields instead of the first record showing?
Thanks alot.
Regards,
Amber
View 14 Replies
View Related
Jan 22, 2014
I have a form with a button which when pressed need to run a macro which sits on a different form.
i have tried run macro ad do cmd but none of these work.
View 1 Replies
View Related
Mar 18, 2007
Hi Have scoured the forum and have found people with a similar problem, but I just can't seem to get my head round this.
Basically I have a table with various fields, one of these fields is a sign off field where the user enters their name once the record has been reviewed.
I want to count all the records that do not have a name entered in this particular field thus are null/blank. I then want this figure to appear on a form thus representing the total number of records still to be reviewed.
I have tried doing this with various methods with no joy.
Any help would be much appreciated.
View 3 Replies
View Related
Sep 7, 2013
I have a [Order Central table] that compiles products from four different categories that employees can order from.
Employees using the database will select from four categories on buttons which opens up the order form EX: [Category 1 form].
They enter their order info on [Category 1 form] into a subform sourced from [Category 1 Table] called [Category 1 subform].
Is there a way that they could select the "Category 1" button, which would run a macro opening [Category 1 form] with the [Category 1 subform] having a where condition of 1=0 (or just so that the subform appears blank)?
View 7 Replies
View Related
Jan 31, 2014
I have 2 forms and a macro in Form1 which runs throught every record to update the records, however.I would like to run this macro from the main form (Form2)
I have tried
docmd.openform "Form1"
Run Macro
but it doesnt work
View 4 Replies
View Related
Mar 20, 2007
I have a query linked to some combo/txt boxes on a form so the user defines which categories he/she is looking for then presses button and query comes up with relevant results. This works fine if the user inputs information that is actually there, but if they leave it blank it goes all wrong.
The statements I have in the query are as follows:
[Forms]![frmStockControlSigned]![comboSupplier]
[Forms]![frmStockControlSigned]![txtPurchaseOrder]
etc.
I thought the solution may be along the lines of this but it does not work:
=if([Forms]![frmStockControlSigned]![txtDate]=Null,[Forms]![frmStockControlSigned]![txtDate],"*")
basically i want to make it so the user does not have to enter criteria into evey box, thus if every search box is left blank it would show all records "*"
View 2 Replies
View Related
Jul 18, 2014
I have written the following code to open the Outlook 2013 'Select Names Dialog' with a command button called 'btnTo' from an Access 2010 form, this code works fine to display the dialogue box but it is usually hidden and the user has to 'click' the icon on the task bar to show the dialogue box. I know that there is an API that will bring the dialogue box to the front but how to code the API to do this.
Code:
Private Sub btnTo_Click()
On Error GoTo Err_btnTo_Click
Dim olkApp As Outlook.Application
Dim olkSes As Outlook.NameSpace
Dim olkSND As Outlook.SelectNamesDialog
[Code] .....
View 4 Replies
View Related
May 30, 2014
I've written a function which opens the Outlook GAL dialog, allows users to select recipients and passes back a 2-D array (recipients and type i.e. To, CC or BCC).Here is the code :
Code:
Public Function GetContactsFromOutlookGAL() As Variant
Dim appOutlook As Object ' Outlook Application
Dim objNameSpace As Object ' Outlook NameSpace
Dim objSelectNamesDialog As Object ' Outlook Select Names Dialog
[code]...
The only problem I have is that when I 'display' the SelectNamesDialog, it doesn't make it the active window and bring it to the front - it just opens the dialog in Outlook and you have to navigate to the main Outlook window to find it.I'd like for the dialog to 'popup' - ideally, without the main Outlook window dragging along behind
I believe it's possible (judging from this article) to achieve this using API's - specifically, a combination of FindWindow (to retrieve the window handle) and ShowWindowLong (to set it to a topmost window in front of all other windows).
View 12 Replies
View Related
Sep 14, 2006
Okay I have 3 forms. I have one form which is called frmEndOfTrial on this form there is a combo box which has two values--> Accepted and Return for credit.
I have two other forms called frmAccept and frmRFC. I have dragged those two forms onto the frmEndofTrial form and set there visibility to No.
What I want to happen is that when the user clicks on the 1st or 2nd option that corresponding form opens below the combo box?
I've tried a few things here but can't get it to work. I'm pretty limited in knowledge on access. Right now on hte combo box events I have this code for the After Update event
me.sfrmRFC.Visible = True
me.sfrmAccept.Visible = False
I know this is not what I want and not finished cause if I click on either accept or RFC it just brings up the sfrmRFC. How can I do this???
View 8 Replies
View Related
Nov 16, 2006
Hello. Is there a VB command that brings a certain form to the front of the window?
View 1 Replies
View Related
Sep 8, 2004
I'd like to know how to programatically bring a form on top of another one
I have two forms: Menu and Notice
in the onOpen event of the form Menu, I open the form Notice
problem is that even if Notice is the active form, it still hides under the Menu form...that's just weird...
Could it have something to do with the startup option in Access in which I tell to open Menu first, it looks like the startup event comes after the onOpen event.
View 1 Replies
View Related
