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"
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.
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.
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
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:
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!
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.
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?
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"
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.
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?
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.
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)?
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 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 "*"
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
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).
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???
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.