If No Matches Reopen Form After Msgbox
Jul 18, 2012
I currently have an unbound form I am using as a switchboard. When a button is pressed it loads a form asking for the user to enter their password (the swtichboard is then closed). If the correct password is entered the form opens to their details, when that form is closed, the swtichboard re-opens. I want an action that if the correct password is not used, then a msgbox lets them know and then the Switchboard they were at re-opens. So far I have the following code:
Private Sub Form_Open(Cancel As Integer)
If Me.Recordset.RecordCount = 0 Then
MsgBox "Incorrect Password!"
Cancel = True
End If
End Sub
This works well, but I am unsure how to write that after the msgbox I want the Switchboard to open. I tried to add "DoCmd.OpenForm frmSwitchMain" in various places, but I am unsure on the proper procedure to do this.
View Replies
ADVERTISEMENT
Dec 23, 2005
After I save the record, I would like to clear all the data in the form and allow use to add another new record.
I use code like me.mycontrol.myfield=null, it sets all text box to null, but the selectedlist box still filled in.
The only way is to close the form and reopen the form. I have some code in the open form event. This makes the form displayed correctly.
But this is really not convenient, the user have to close the from and reopen the form in order to see everything refreshed again amd enter new record
Is there any way that I can use a button in the current form that close the form and reopen the form, which actually lookes reset the page.?
Thanks
View 2 Replies
View Related
Jul 11, 2013
Is there a way to run code to see if a query is open? Then if the query is open, program the database to close it and re-run it?
I have a query that is based on a field on my form. It runs fine the first time. However, if the user leaves the query open and goes back to the form, makes a new selection, and clicks the button to open the query, the query doesn't refresh. The user either has to close the query and re-run it or press the Refresh All button. I need a way for the query to refresh automatically if it is open already or not. The only other alternative I can think of is to program the database to close the query and re-open it. I tried using QueryName.Requery and QueryName.Refresh but in both cases, it tells me that an object is required and I don't know what it wants. I also tried setting the query definitions using VB, but the query is apparently too long because no matter what I do, I can't get it on one line and Access doesn't seem to like it if the query takes up two lines.
View 6 Replies
View Related
Mar 30, 2013
A charity has a client file (over 700 clients) that will have records that need editing. The user is presented with a form(1) holding a text box to enter the parameter (Client Surname) for a query. They can use starting letters and wildcards to narrow a search e.g. Jo*The click a command button that opens another form(2) based on a query that uses the parameters from the first form.
This Works just as planed if matches are found. If not, it displays a blank/new record. I want to either 1) stop the blank form being displayed 2) tell the user that their query was unsuccessful. I Do not want them to use this facility to CREATE a NEW Client record.
The parameter query is working fine and form(2) shows the matching records, it's reacting to a situation where no records are found, that is my problem.
View 2 Replies
View Related
Jan 23, 2014
Every time I re-open Access 2010 it pops up stating the following with a blank text box:
"[Forms]![Frm_system]![sub_frm_invoices].[form]![invoice_id"
I hit ok and the usual invoice_id param text box message box appears again.
Once that is over, I gain access. I can browse to the form - frm_system, open the sub-form sub_frm_invoices and remove the invoice_id field. Once I re-add it everything works fine (until I close the application).
I initially thought it was because the database was set to "compact on close" but I disabled that and it is still occuring .
I know this is the cause but as the sub-form itself uses a table record-source I know the field exists - not to mention I can simply delete and re-add it to fix it temporarily.
This behavior occurs when a field, a criteria, an expression, or a control in a query, a form, or a report references a name that Access cannot find. For example, a name could be misspelled or a field may not be available within that scope.
Basically it looks like the textbox displaying the invoice_id on the form is the problem. I can't figure out why though. When I remove it everything works - the queries work if I recreate it as well..
View 1 Replies
View Related
May 27, 2015
We have an access database which has a form that has a list box that details particular records. See attach "list box".
What I would like to do is double click on the record and this would look in a powerpoint folder (see attach "path") and open the presentation if there is a match to the value in Listbox Column 5 i.e. Tool Number.
I can get it to open the presentation if I include the tool number in the path but I cannot seem to be able to get it to compare the values and only open if there is a match.
View 4 Replies
View Related
Mar 26, 2015
basically am creating a booking system, i have a add a room form. my form should check whether i already have a room number in my table, which works when the form is filled in. however when my form is null, then i press add new room button, i get this error rather than " please fill your form in"
Error: runtime error '3075' syntax error (missing operator) in query expression 'Room Number ='.
room number is a number field, integer but has primary key. i cant keep autonumber, as my requirement is to add new room number, but the roomnumber has to be unique.
here is the dlookup;
If DLookup("RoomNumber", "tblRooms", "RoomNumber = " & Forms!RoomPackages!txtRoomNumber) > 0 Then
MsgBox "This number already exists."
Else
p.s it unbound form
View 3 Replies
View Related
Aug 16, 2014
I am trying to make use of the OpenForm command to open a form to a record where a textbox matches the value of a text box on another form.
On Form1, I have a textbox called txtRtnRef, and it contains a reference number in a similar format to SWR-9
On Form2, I have a textbox called txtReOrderRef (control source ReOrderRef) which also contains a reference number in the same format (SWR-9)
I am trying to have a button next to the text box on Form1 that has an OnClick Event that opens Form2 to the record containing the same text reference number.I have tried, to no avail, a number of variations of the OpenForm command, the most recent being
Code:
DoCmd.OpenForm "Form2", , , "ReOrderRef = '" & Me.txtRtnRef & "'"
The form opens, but to a blank record?
View 6 Replies
View Related
Mar 16, 2005
hello all,
i have a form which I have set so that it opens a blank record once opened. After it has been opened I want a msgbox to appear. my code so far is:
---------------------------------------
Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
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
-------------------------------------------
but the problem is that the msgbox appears before the form is opened.
any ideas how to fix this would be grately appreciated. thank you
View 4 Replies
View Related
Aug 25, 2005
Hi all,
I've added a message box to what is basically a standard simple to use access control, "closeForm", I'm a newbie working on learning access vb. I'm guessing my code if fudged. Any help is greatly appreciated.
Private Sub Close_Click()
On Error GoTo Err_Close_Click
Dim Answer As Integer
Answer = MsgBox("Press Ok to Close, Cancel to Continue.", vbOKCancel + vbQuestion, "Exit Data Entry?")
If vbOK Then DoCmd.Close
Exit_Close_Click:
Exit Sub
Err_Close_Click:
MsgBox Err.Description
Resume Exit_Close_Click
End Sub
Thanks to all in advance
View 5 Replies
View Related
Dec 30, 2006
Hi,
I have a Form that is modal and popup, and which opens without the MS Access in the background. It is like a separate window (piece of program) that runs under Windows XP.
If I move the Form on my screens (I have dual monitors) I would like that the messages that are triggered by various actions to appear on top of the form not - for example - on the other screen .
I can add more info if necessary.
Thanks for help.
View 2 Replies
View Related
Mar 28, 2007
Hello,
My database has two main tables: Table1 and History
Users can add data to this table and decide if store part of data into the History table using an append query behind a cmdbutton. I do this to create an Historical Log of my records.
I then have a main form (form1) with the following code on the OnCurrent event:
If Not IsNull(DLookup("[SSN]", "history", "[SSN] = '" & [SSN] & "'")) Then
msgbox "Record present in the Historical Log!", vbOKOnly, "Warning"
end if
I would now like to change this code so that users are prompt with a message that allows them to either open the form1 or the Historical log (frmHistory)
I would need something like this but cannot get it to work:
If Not IsNull(DLookup("[SSN]", "history", "[SSN] = '" & [SSN] & "'")) Then
msgbox "Record present in the Historical Log!", vbYesNo, "Warning"
If vbYes Then
DoCmd.OpenForm "frmHistory", acNormal, "qryhistoryfrommsgbox", "", , acNormal
If vbNo Then '
'open the regular form1.
Another thing I cannot figure is why this pop up msg comes up even if I close the form. Is there a way to revome it from the close form event?
Thanks.
View 4 Replies
View Related
Aug 13, 2013
I have a form with one field on it. I want to be able to look up a value on this form and based on whether it is in the table or not give a message box saying yes it is there, or no it is not and then reset the form to look up another value.
View 3 Replies
View Related
Sep 11, 2005
I have a code for a free text search form which will return records in another window when the search button is clicked.
CODE
Private Sub cmdSearch_Click()
If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
MsgBox "You must select a field to search."
ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter a search string."
Else
'Generate search criteria
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
'Filter frmRhinitis based on search criteria
Form_frmRhinitis.RecordSource = "select * from tblBaseline where " & GCriteria
Form_frmRhinitis.Caption = "Customers (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
MsgBox "Results have been filtered."
End If
End Sub
However, even if no records match the criteria the window will return a "Results have been filtered" but return a blank form. How do I include code that will return a "Match cannot be found" MsgBox if the search string isnt found?
Thanks :)
View 3 Replies
View Related
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 6 Replies
View Related
Jun 1, 2012
I use filter Combo boxes in a lot of places so that users can filter records easily. (Not combo boxes for input).I have them labeled as filters but nonetheless users keep trying to input into them for some reason.How would I add a message box to it so that it states that 'this box is not for data entry etc'.
View 2 Replies
View Related
Sep 14, 2006
I have a combo box that is filtering a form. The combo box contains employee's last name. However, the field that it is looking up can contain more than one name (e.g., possibilities include just Smith, Smith & Johnson, Smith & Jones & Johnson, etc.).
Thus, when I click on the combo box, I want all records containing "Smith" to show up. However, I have only been able to bring up exact matches. Here is my code. Thanks in advance.
Dim strSQL As String
Dim strSQLSF As String
Status_CB = Null
strSQL = "SELECT DISTINCT Pipeline.Status FROM Pipeline"
strSQL = strSQL & " WHERE Pipeline.[Lead_Person(s)] = '" & S_MD_CB & "'"
strSQL = strSQL & " ORDER BY Pipeline.Status;"
Status_CB.RowSource = strSQL
strSQLSF = "SELECT * FROM Pipeline "
strSQLSF = strSQLSF & "WHERE Pipeline.[Lead_Person(s)] = '" & S_MD_CB & "'"
Me!Data_Entry_SF.LinkChildFields = "[Lead_Person(s)]"
Me!Data_Entry_SF.LinkMasterFields = "[Lead_Person(s)]"
Me.RecordSource = strSQLSF
Me.Requery
View 3 Replies
View Related
Apr 12, 2007
What I am trying to do is split a sentence into words LikeJocelyne Labylle And Jacob DesvarieuxThis would produce 4 words as the and,&,/ Ect are removedSo I end up with:-JocelyneLabylleJacobDesvarieuxIf all 4 words are found in a string it returns all matching records plus gets 100%3 words again returns all matching records plus say 75% and so onI haven't done a lot with it at the min as the first bit is quite simple IE getting the names Etc. but I can't think for the life of me how I can do the search.I Have Tried a query With [Names] Like ""*[String Names]*""" but it seems to only work with each letter???but my main reason for asking here is I don't like reinventing the wheel or at least trying to which will mean hours of Fun but maybe somebody knows of something I could use on the net.Thanks for any help/Advice.mick
View 5 Replies
View Related
Jun 22, 2005
undefinedHi, apologies if this has been answered before but can't find anything relevant!
Trying to match one field to the first 5 digits in another i.e.
Field 1 = 55667
Field 2 = 5566785431 so would want to match the 1st five digits in field 2...
Any ideas?
Thanks
View 3 Replies
View Related
Aug 18, 2005
I'm comparing two tables in a query, the tables are information that was imported from other sources. When I compare the tables, I'm trying to get a one to one result (a reconciliation of sorts). The problem is that there are duplicate line items in one of the tables that may or may not be valid, but all are considered matched. How can I create my query to match to only one line item and leave the others unmatched for analysis? I've tried everything but VBA (of which I have little experience in). Any suggestions?
View 2 Replies
View Related
Mar 13, 2006
I have a form that shows a customer number and a dollar amount. In a subform, I want to show all the records with that customer number that are "close" to the dollar amount. For this purpose close can be INT(amt1) = INT(amt2). But I can't figure out how to put that in the parent/child relationship on the sub-form.
Any help?
Thanks,
David
View 2 Replies
View Related
Feb 17, 2015
I am trying to find company matches between 2 tables. The issue I have is that the spellings on table A differs from table B.Some of the differences are minimal like "St. Annes" and "St Annes". And some really big "St. Annes" and "Annes, ST London".
View 1 Replies
View Related
Nov 6, 2013
I have created a table which holds transactions which can be both positive and negative. To this table i have added an extra column that shows the Absolute values which end results is as follows:
YYYY MM Currency Amount ABS
2013 10 EUR 50.000 50.000
2013 10 EUR -50.000 50.000
2013 10 EUR 49.995 49.995
2013 10 EUR -50.000 50.000
I need to create a table that shows those positive and negative transactions that can be netted of. I have been able to create a query with the following SQL code:
Code:
SELECT tbl_All_Absolute.[Posting Account], tbl_All_Absolute.Jnl, tbl_All_Absolute.CCYY,
tbl_All_Absolute.PP, tbl_All_Absolute.[Batch Num], tbl_All_Absolute.[Trans Num], tbl_All_Absolute.[Detail Reference],
tbl_All_Absolute.Amount, tbl_All_Absolute.RFPNum, tbl_All_Absolute.[Transaction Ref 1], tbl_All_Absolute.[Transaction Ref 2],
tbl_All_Absolute.[Transaction Ref 3], tbl_All_Absolute.[Transaction Ref 4], tbl_All_Absolute.Expr1
FROM tbl_All_Absolute
WHERE (((tbl_All_Absolute.Expr1) In (SELECT [Expr1] FROM [tbl_All_Absolute] As Tmp GROUP BY [Expr1] HAVING Count(*)>1 )));
Unfortunately (but obviously) the code will list 3x the 50,000 transactions and leaves out the 49.995.
My knowledge in Access is not that of an expert and i am wondering if there is a way for Access to only include the first 2 transactions (the +50.000 and the -50.000)?
View 12 Replies
View Related
Nov 20, 2011
I have a database that keeps track of customers by last name, first name and various other information. I have the combo box setup so that I can type in a last name and it brings up my results. The problem is that it only brings up the first match and doesn't let me browse through all the matches. The last name and first name are seperate fields so if I look for a last name that more than one customer has like smith it only takes me to the first match. Is there any way to make it filter all the names that aren't matches out of the combo box as the values are typed in?
View 3 Replies
View Related
Nov 5, 2007
I have a table from an excel sheet that comes in the format above with up to 5000 rows in record groups from 1 to 10 rows in each. The repeating data is how it comes from our all singing and dancing case recording software and I can’t change that fact.
Take a group of records with the same TradingName such as “The Herriots” group, There should be one row in the group where FdInspAction = LastInspDate (in this case 06/01/2007) . In this group there is no match so we need to identify the group. The “Bloggs” group is fine (see Row 8) so can be ignored.
My feeling is it can’t be done other than by visual checking. Can anyone think of a way around this?
IDTradingNameFdInspAction LastInspDate
1The Heriotts 03/02/2001 06/01/2007
2The Heriotts 10/02/2002 06/01/2007
3The Heriotts 16/02/2003 06/01/2007
4The Heriotts 25/01/2005 06/01/2007
5The Heriotts 04/01/2006 06/01/2007
7Bloggs Ltd 02/01/2005 25/01/2005
8Bloggs Ltd 25/01/2005 25/01/2005
View 8 Replies
View Related
Apr 8, 2008
Hi there,
I've successfully created, for the first time, a database with many attributes that is searchable by most of those attributes as well. I've finally gotten the swing of how tables, forms, queries, and macros link up and am understanding how MSA works.
I can't seem to find any information on here that tells me how to do wildcard searches. For my particular application, people enter items into the database in a nonstandard fashion, i.e. "oring," "o-ring," and "o ring." I need to enable the capability to search "ring" so my search functions are fully maximized.
Can anyone help?
Thanks!
View 2 Replies
View Related
