Hi all,
I am having trouble finding out how to stop a new record showing on my form when there are no records showing in my underlying query
Query criteria is populated from a form text box and then I use a cmd button to open the form.
cmd button on click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "F_SearchJobNoF orm"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms!F_SearchJ obNoForm.Record Source = "Q_SearchJobRef No"
I have on Form_load to just maximise the form and that's it.
I can set the form to not allow additions but it just shows a blank form and the user has to shut it down using the x in the top right corner.
Best way to implement this and trap the blank record before the user sees the form?
I'm pants at access but willing to try very hard ;o)
Appreciate any help
4 1900 JKing 1,206
Recognized Expert Top Contributor
Hi by adding a few more lines you can check to see if the supplied criteria will return any results in your query before opening the form. -
Private Sub cmd_Click()
-
-
Dim db As DAO.Database
-
Dim rs As DAO.Recordset
-
Dim stLinkCriteria as String
-
Dim stDoc as String
-
-
Set db = CurrentDB
-
Set rs = db.OpenRecordset ("qryYourQuery", dbOpenDynaset)
-
-
stDoc = "frmYourForm"
-
stLinkCriteria = "yourCriteria"
-
-
rs.FindFirst stLinkCriteria
-
-
If rs.NoMatch Then
-
MsgBox "Search criteria returns 0 results. Please try again.", vbOkOnly + vbInformation, "Sorry!"
-
Else
-
Docmd.OpenForm stDoc,,,stLinkCriteria
-
End If
-
-
rs.Close
-
-
Set db = Nothing
-
Set rs = Nothing
-
End Sub
-
rs.NoMatch returns true if there are no records in the query that match your stLinkCriteria.
Be sure to replace "qryYourQue ry" with the name of the underlying query of the form you want to open. Sames goes with frmYourForm and yourCriteria be sure to replace them with your own values.
Thank you.
Sorry for being a bit thick but I have tried your code and get an error
"runtime error '3061' Too few parameters. Expected 1."
When I debug it highlights (line 7.)
Set rs = db.OpenRecordse t("Q_SearchJobR efNo", dbOpenDynaset)
Here's the code I put in and substituted the names as per your message
I have rem'd line 9 as I'm unsure what this variable is doing. The query field filter is populated using the text box on another form?
1. Private Sub comViewSearch_C lick()
2. Dim db As DAO.Database
3. Dim rs As DAO.Recordset
4. Dim stLinkCriteria As String
5. Dim stDoc As String
6. Set db = CurrentDb
7. Set rs = db.OpenRecordse t("Q_SearchJobR efNo", dbOpenDynaset)
8. stDoc = "F_SearchJobNoF orm"
9. 'stLinkCriteria = "yourCriter ia"
10. rs.FindFirst stLinkCriteria
11. If rs.NoMatch Then
12. MsgBox "Search criteria returns 0 results. Please try again.", vbOKOnly + 13. vbInformation, "Sorry!"
14. Else
15. DoCmd.OpenForm stDoc, , , stLinkCriteria
16. End If
17. rs.Close
18. Set db = Nothing
19. Set rs = Nothing
20. End Sub
Any more help would be appreciated
Graeme
JKing 1,206
Recognized Expert Top Contributor
I believe the problem is that your query includes parameters which were allowing you to previously filter the form. What I suggest is removing those parameters from the query.
This is where stLinkCriteria comes in. The purpose of the variable stLinkCriteria is to pass it to the openform command as the where statement for the underlying query of the form. So on your first form with your command button you should include a textbox for the user to type into. Then when they click the command button you build your criteria. For example: -
stLinkCriteria = "[field] = " & Me.txtSearch
-
[field] would be the field in the query that you want to search on like and ID and Me.txtSearch would be the reference to the textbox on your search form.
Now when we pass this into the FindFirst method of the recordset it will search for the first occurence in the query that matches the user's input for that field. If there's no matches prompt the user and dont open the form. If there is a match open the form with that criteria.
If this seems confusing and you require a little more help with examples more specific to your own database then I'll need you to supply me with a few more things. I'll need to see the sql for the query behind your second form, the name of the field you want to search by and it's data type(i.e. text, number, date) and the name of any controls(i.e. textbox, combobox, listbox) on your search form that you're using to build your criteria.
Thanks very much. I use vb badly and access badly so between the two, confusion arises for me.
That makes sense what you're saying. I have to go now and will have a go at this tomorrow.
Appreciate your help!
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: qsweetbee |
last post by:
I have a form(fAddUsers) in my database. It is continue form for data
entry. Some fields are required fields. Some are optional fields.
There is 1 particular filed(TokenExpirationDate)on the form which is
depended on the other field(TokenID)whether it is blank or not. If
the "TokenID" field is blank, the "TokenExpirationDate" field can be
blank also. But if the "TokenID" field is not blank or null, the
"TokenExpirationDate" field must be...
|
by: Joe Scully |
last post by:
Hi all,
I am having trouble with access adding a blank record to a subform
everytime I finish entering data and closing the form. (The form and
subform are based on the one table)
When entering data into the subform I get the pencil icon to show data
addition, but underneath the record I am inputting data the record is
blank with a star(*)icon and this is the record(blank)that always gets
saved with all the records that actually have...
|
by: Johm |
last post by:
How can i go to the next blank record in the table , such a blank
record exists ?
In my form Customers, i have a command button with the following
command :
DoCmd.GoToRecord , "", acNewRec
DoCmd.GoToControl "CompanyName"
The above command is OK, but i want to amend it somehow . Do to some
|
by: Tim Graichen |
last post by:
Hello,
I have a main form with one subform. The main form displays a record from
TableOne (PCID)
This is a corporate software tracking utility that works like this:
Main form...Choose PCID (from TableOne)
Subform..Display / Add software (from TableTwo) (linked via PCID)
|
by: ChadDiesel |
last post by:
My basic question is why does my print report button on my subform print a
blank report when my cursor is on a blank entry line? Here is a more
detailed explanation of my problem.
I have a form that contains shipment destination information and a subform
that contains boxes for that shipment. The main form is a table called
Ship_Info, and the subform is a table called Label_Info. They both contain
and are linked by a field called...
| |
by: bobh |
last post by:
Hi All,
A clients database (built by another and not very well but, its what I
have to work with) has bound forms that display customer information
and when printed is three pages long. The information on page 3 is set
to screen display only. When printing it prints 3 pages with the last
page being a blank page. I have tried to surpress the third page by
putting a print button on the form and adding the vba code -
Docmd.Printout acPages,...
|
by: sparks |
last post by:
I am trying to fix a database that someone did about 4 yrs ago in
access97.
The main table just contains demographics and is on the main form of
the database.
It has a subform on a tab that contains the other information.
it is set 1 to 1 in the relationships.
simplify if I can
|
by: ManningFan |
last post by:
I've got a continuous form. The query behind it is updateable, but I
don't want the continuous form to display a blank record at the
bottom. I've set AllowDeletions to false, AllowAdditions to false and
DataEntry to false and I can still see the blank record. Is there any
way (besides making the query behind it "not updateable") to prevent
that blank record from showing?
|
by: EManning |
last post by:
I posted a question on 5/5/08 asking how to trap an error caused by
multiple users trying to access the same patient. Here's what I
posted:
"Using A2003. I've got an FE with a main form with a subform. The
subform is a mixture of bound and unbound fields. The main form is
unbound and all it has on it is a combobox to choose a patient's
name. Once the patient is chosen, the subform refreshes to show that
patient's data. The problem...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |