Modules & VBA :: Generate A Msg If Zero Records Found?
Aug 7, 2015
The following is code to open a form and records with a specific date as input by the user:
Private Sub cmdDisplayCovers_Click()
On Error GoTo Err_cmdDisplayCovers_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmInventory"
stLinkCriteria = "[tblCovers].[Date] = [Forms]![frmCoversByDate]![txtDate]"
DoCmd.OpenForm stDocName, , , stLinkCriteria
etc. etc.
generate a message to user if no records match the input and then return to the input form. Currently, the form opens even with no records.
I am trying to provide the user a custom search feature. They want to enter a keyword or phrase and search 3 memo fields and filter the form base on the records found. they also want to be able to search the whole phrase or any part of the phrase.
I have a like expression for any part of the phrase but I when I set it up for whole phrase it doesn't work. Even if I run a simple query and use
For example: There's an acronym the user is looking for : ACA
If I set my query up like this: [field1] like "*ACA*" or [field2] like "*ACA*" or [field3] like "*ACA*"
it not only finds records with that acronym but it also finds records where that combination is found in a word, for instance vacate.
I am building a simplified re-order point system - if inventory position drops below a certain level (the yellow level is this case) one or more purchase order lines has to be created in another table.
I have one table with the following field and data:
ItemId Red Yellow Green Multiple Inventory position 0001 10 30 50 5 45 0002 5 40 47 5 23 0003 11 20 30 10 5
I would like to generate new records (in another table) based on the above fields and three records.Basically the end result should look as the following:
The logic is quite simple - if inventory position is less than the yellow value new order lines should be created in multiple qty (based on the multiple field) until the aggregated value (in table 2) is above the green value.The priotization value should be based on the start inv (in tbl 2) compared to the values in red, yellow and green in tbl 1.
Hey everyone, Even though I'm going to feel like a moron for not knowing this I'm hoping there is a simple answer. I'm far from a master at access, and seem to be having a little trouble when it come to formatting the output of my query. I have a database made the keeps track of about 200 hundred crime reports. I want to be able to do a query that will output the 15 possible crimes as rows, and then have 4 columns which are the locations of where the crimes happened. And then for the results have how many of crime where commited there. Pretty much in an excel format (see below example). However there are some crimes that have never happened. So there are no records of them. Unfortunatly when i do my query since there are no records, they are not even listed. I need them to list the crime, and place zeros if there are none. Since this probably isn't to clear I'll put a little example below.
Data:
Robbery: 4 on campus, 5 off campus, 6 in city, 0 in apartments Rape: 0 on cmpus, 3 off campus, 4 in city, 0 in apartments murder: 0 on campus 0 off campus, o in city, 0 in apartments
I have a database that holds hardware data. It's working fine, but there's a slight itch I'd like to scratch and I'm not sure how.
Very simply, I have a table listing printers. I have a search form with combo boxes called 'cboRoom' and 'cboDepartment'. The room and department fields in the printers table are lookups to a room table and a departments table respectivley.
The search form works fine by using the combo boxes to select a room and/or department, click search, and a query is run using the combo box selections as parameters. The query is also made to show all records if the combo boxes contain null. A form is then displayed with the query results.
Say for example, we have a room called B24. If B24 is selected in the combo box and the query run, I want a popup to appear that says 'No data with these search parameters' if there are no records containing 'B24'.
The search button (which is actually a label for design reasons) on the search form currently does nothing more than this;
Private Sub lblSearch_Click() DoCmd.OpenForm "frmPrinters", acNormal DoCmd.Close acForm, "frmPrinterSearch" End Sub
'frmPrinters' is obviously using the query (qryPrinterSearch) as its recordsource. Obviously, the popup needs to appear as soon as the query has been run, but I'm not sure what code to use or where to put it...
I know I need some sort of (pseudo)
If frmPrinters.cboRooms Is Null MsgBox "No Data" Close frmPrinters Open frmPrinterSearch End If
Something like that. But obviously I need to do it for both cboRooms and cboDepartments, after the form has attempted to populate itself with data from the query (otherwise it return null values anyway I guess).
Any help with the code and where to put it much appreciated.
A form i have gains its records from a query. I know that the number of records found is displayed down the bottom, but is it possible to have a text box displaying this, so i can choose where on the form i have it?
I am using a query to search for records and I'd like there to be a message box that pops up on the search page if there are no records found (so the query is empty). I'm guessing there is a simple solution since I think I just need an "If" statement checking to see if a field in the query is null or not. However, I'm not familiar with Access code and what I've tried so far does not work. Any help is greatly appreciated! p.s. I am using Microsoft Access 1997...old school...
Code: SELECT Table1.[Material Key], Table1.[Material Name], Table1.[Info] FROM Table1 INNER JOIN search_MaterialKeys ON [Material Key] = search_MaterialKeys.[MaterialKeySearch];
So at the moment, users paste material numbers in the search_materialKeys table and the query is created with the necessary information. If the number they paste into the search_materialKeys table is not found in Table1 then the query doesnt return anything. I need the query to list all the entered values in the temptable in the query so that users know which of their inputs were not found. Is there any way to do this?
Any way to build something into a sub-query that says 'if no records are found, return '0' or some other string'?
Otherwise is there a way to make a master query ignore sub-queries if they return no records?
Allow me to explain in more detail: I have a series of sub-queries, most of these take the sum of several fields from a number of different tables, and I have a main query which combines all of these, to be used as the basis of a summary report.
These queries aren't a problem, but I have a few other essential queries which take the modal (most common....) entry for fields which aren't numerical. So I can't use the sum function.
Now, if all the sub-queries are working then so does the main query, however if one of them fails to find a result, then none of them show up in the main query. I don't know why.
The issue is that depending on the date range selected, some of the tables targeted by the sub-queries don't have any records at all, so when they are run they return nothing. The sum queries can handle this since they just return 0, but those searching for modal records just find nothing (not 0's and not null fields, just blank across all rows).
Here's an example of my sql statement for the modal queries.
Code: SELECT TOP 1 Trends.Trend AS ModeTboxTalk, "1" AS [Key] FROM Trends INNER JOIN [Toolbox Talks] ON Trends.TrendID = [Toolbox Talks].TrendID GROUP BY Trends.Trend, [Toolbox Talks].TrendID, [Toolbox Talks].TalkDate
HAVING ((([Toolbox Talks].TalkDate)<=[Forms]![WeeklyReportSelect]![WeekBox] And ([Toolbox Talks].TalkDate)>[Forms]![WeeklyReportSelect]![WeekBox]-7) AND (([Toolbox Talks].SiteID)=[Forms]![WeeklyReportSelect]![SiteBox]))
ORDER BY Count([Toolbox Talks].TrendID) DESC;
- FYI the weekly select form is where users select the week and site they want to report against. So it would be really peachy if I could tell the above to say something like 'no trend this week' if indeed there were no records.
Table: Relationship PK: RelationshipID int (Indexed No Dup) FK: ContactID int (Indexed No Dup)
Table: Contact PK: ContactID int (Indexed No Dup) FK: RelationshipID int (Indexed Dup Allowed)
I also have following Local vars: @Contact1 int @Contact2 int
How do I create an UPDATE statement that will read RelationshipID in Relationship where Relationship.ContactID=@Contact1 and use it as the value to update the Contact.RelationshipID for all records in Contact where Contact.ContactID = @Contact1 or @Contact2
Hope this makes sense?
Also please let me know if I should post this in the SQL area instead.
Code: Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[OwnerID] = '" & Me![lstUsers] & "'" Me.Bookmark = rs.Bookmark <---- error is here for the datatype mismatch or No record found
While there are lots of detailed issues discussed and resolved here, my problem is at a much higher level. Here's what I want to do.
I want to generate records in a file based on the user entering some 9 different values. The first two values will be used to get records from file A (they are a partial key). The values from the records in file A, plus the other values entered by the user will be used to generate records in file B. I hope this is clear enough.
I have created a query which prompts the user for each field and does the job just fine, but I want to replace the 9 individual prompts with a single form which allows the use of combo boxes for the partial key and a date picker for the single date field.
I've fluffed through creating a form with unbound controls to suit and even worked out a form of cascading combo box for the partial key, but I am struggling to find the best way to make a button at the bottom generate those records.
A nice optional would be to then display the added records (i.e. another query I guess).
Can somebody give me some pointers as to how to approach this? I've done very little in the way of Access programming, being mostly an SQL guy.
I am setting up a database to hold staff details, and would like a query to show each member of staff's total hours and FTE.
Staff name etc is in tblStaff Staff shift details are in tblShifts linked via staffID
tblShifts will contain details of the shift worked on each day of the week, but the majority of our staff work a standard shift - e.g 8-4, 9-5. Therefore what I wanted to do was in tblStaff set a field named shiftPattern to 1 2 3 or 4. 1 indicating a custom shift, and any other number indicating a set shift defined in a separate table.
The problem I have is that my query only returns people who have details in tblShifts - regardless of their shiftPattern value. If I enter a blank record in tblShifts it will do the above as intended.
I've got a table, where each record is a group of students. So the fields include Student1, Student2, Student3, etc. I know this is bad database design, but I've done this because I want the group number (primary key) to be automatically generated.
Now, I want to add more information for each of these students as if they're seperate records but I also want an autonumber for the group.
L=Letter , X=number. Examples consisting of three parts (it can be more than three).
1. LLLLXXXXXX_LLLLXXXXXX_LLLLXXXXXX OR 2. LLLLXXXXXX LLLLXXXXXX LLLLXXXXXX OR 3. LLLLXXXXXX&LLLLXXXXXX&LLLLXXXXXX OR 4. LLLL_LLLLXXXXXX&LLLLXXXXXX OR 5. LL LLLLXXXXXX&LLLLXXXXXX
I would like to develop a function that takes a string that is made up of three parts or more and each part is separated by an underscore "_", space, or "&". There is no character that takes precedence over the other. It will always be only a single character which seperates the parts.
The check will always be done from the left. It should carry out the following actions:
1. Display all characters from the left until it reaches the first seperator "_" underscore OR "space" or "&" 2. Check only Part1 if it is made up of 4 letters and the rest 6 numbers (see 1-3 above). If that is the case only return part 1. 3. If part one only has letters and no numbers (see above 4-5) return all parts. Part one can consist of two letters or more.
I have started the function for step 1 above that searches for the character "_" underscore and display all characters until the first "_" is reached. I would like to include the same for "Space" or "&" in this function.
Once that is done I would like to do step 2 and step 3.
Code: Function SearchforChar(strTest As String) As String Dim test2 As String Dim strUntil As String strUntil = "_" test2 = Left(strTest, InStr(1, strTest, strUntil) - 1)
[Code] ....
Answer3: Full name with all parts should be returned for all above four cases under situation 3.
Somehow it doesn't take this variable msoFileDialogSaveAs. It tells me it's unknown.
Code: Public Sub Command2_Click() On Error GoTo MyError3 Dim filelocation As Variant Dim g As Object Set g = Application.FileDialog(msoFileDialogSaveAs)
i have the following code in a function but when i want to compile it i have the following code will be blue i have error in this line:
.NamedParameters = True
the error message is this :
method or data member not found
my code is as following:
Function Get_Bearbeiter_Detail(ByVal vBearbeiter_Code As String, Optional ByRef vBearbeiter_Name As String, Optional ByRef vReadOnly_Flag As Boolean, Optional ByRef vAnalyser_Flag As Boolean, Optional ByRef vAdmin_Flag As Boolean) As Boolean On Error GoTo Err DoCmd.Hourglass True Dim rst As ADODB.Recordset
When I run the following sql (in a query) I enter information once for [enter cty id]. and it all works fine.
What I would like to do now, is write the vba code so that the sql runs multiple times looping through a list of items.
For instance instead of the popup window asking "enter cty id", I'd like to automatically have the code loop through a list. For example a list of 1 through 10.
Then, in the loop vba code:
after item 1 is run, do an export of the results. after item 2, do an export. etc.
Code:
select a.south_index_id, c.cnty_cd, a.southwest_name_source_cd, sp.south_ssn, sp.estimated_birth_dt, [a.LAST_NAME] & ', ' & [a.FIRST_NAME) PERSONNAME], a.BIRTH_DT, a.GENDER_CD, sp.ms_pmi, cnty_person_id FROM SW_alias_name AS a, SW_south_person AS sp, SW_county_pid AS c WHERE EXISTS (select * from SW_alias_name a2 where ucase(a2.last_name) = ucase(a.last_name)
The Setup: There is a clients table, a property table and a jobs table. Each client can have multiple properties and there can be multiple jobs done on each property.
The form: I created a form that uses a drop down to select the client, once the client is selected the subform shows the properties for that client. I can navigate through the properties to select any of the required properties and on this same subform, there is a button to open the jobs related to the property selected.
The problem: The form and subform work fine. The last form (the button that opens up the jobs) works ONLY IF there are records available. If there are no jobs for that property, it doesn't allow me to enter a new record. I'm pretty sure that the issue is because the jobs form has a query record source that uses all three tables linked (I'm pulling the clients name from the clients table and the property name from property table).
What I need is some vba coding to check if the recordset is empty and if it is then add this entry. Also, I'm not sure if this should add this code to the button or on the on_load of the jobs form.
I have a table called bookings, three records within that table are;
arrivaldate departuredate breakfast
This Table (Bookings) is linked to the guest table via GUESTID..Assuming that a guest arrive on the 5th and leaves on the 10th, I am trying to create a breakfast voucher for the guest for each day of their stay.
So 5 vouchers, 1 each for the 6th, 7th, 8th, 9th, and 10th.I have created the breakfast voucher as a report and a query to include data but I dont know how to propagate the new data to actually create the vouchers. All I can get is data for the arrival or departure fields.
