Showing Subform Based On Whether Or Not A Field Is Blank Or Null
Sep 15, 2015
I am trying to show a certain subform based on whether or not a field is blank or null. This database is not owned by me--it's what we use to run queries and reports on the data on a centralized server for the library. It's not even in the same city--so changing the tables is not an option for me.
So, I have a subform (subform1) that is linked to the main form based on 2 fields (BibID and Item_Enum). The trouble is that the second field (Item_Enum) is sometimes blank, so the subform will not display. So I created another subform (subform2), which is a duplicate of the first one, but this time only linked on the first field (BibID). So, if that second field (Item_Enum) is blank, I want subform2 to display and subform1 to be hidden. If the second field (Item_Enum) is not blank, I want subform1 to display, and subform2 to be hidden.
I've tried putting the VBA code on different events for the form, but I can't get it to work consistently.
There are two problems, one of which I think I have fixed--but I would prefer a better solution. The VBA code doesn't run unless the form is newly loaded. I've tried placing the VBA code on different form events, but nothing works. I want the code to activate whenever a new record is displayed. The work around is that the form IS newly loaded every time the user scans a barcode for a new book. That works now, but if I need to change the way the user looks up new records, this might not work anymore.
The second problem is that the VBA code doesn't display subform1 when Item_Enum is blank. It doesn't display subform2 either, but that's the desired result.
Private Sub Form_Load()
If Me.ITEM_ENUM = "*" Then
Me.subform2.Visible = False
Me.subform1.Visible = True
Else
Me.subform2.Visible = True
Me.subform1.Visible = False
End If
End Sub
I've tried changing the condition to Me.Item_Enum = null, and changing the false/true results accordingly, but that didn't work either. In that case, only subform2 is visible, whether it should be or not.
View Replies
ADVERTISEMENT
Feb 21, 2014
I have a form with a subform. The subform is used to report a sales total, for today and for the month. When there are no sales today (a null result in one of the fields) the entire subform goes blank.
How can I make it report a zero in the field instead of blanking the entire subform?
View 1 Replies
View Related
Feb 26, 2015
In the Purchase Order details form a Supplier has to be chosen via a combo box. Based on that another combo box in the subform displays products only from this supplier (Products table is linked to supplier table).
I managed to let the subform combo show only relevant products using criteria referring to the main form combo box. Also other product data will show accordingly in text boxes. So far so good.
However when I close the form and reopen it the subform combo box is blank, other text boxes still show the right values. If I remove the filter criteria for the subform combo then all fields show all data correctly. (However the combo box is unfiltered again 8-/)
So somehow the 'criteria' prevents the combo to show the value that was previously chosen.
View 2 Replies
View Related
Aug 4, 2015
The unbound Parent form has a listbox (SQL Server) , the selection updates the SubForm with a primary key ID. The Subform is a ReadOnly view from an Oracle Linked table. It populates with a Select * From Oracleview where ID = listboxID The code is at the link shown below update: The subform uses textboxes linked to the read-only record.
The main form can populate, no problem. However the SubForm data source is either populated OR is Null.
If it is Null, the subform stays blank. Two Objectives:
1. Set a boolean variable flag in the parent (main form) that subform record exist or that it is null. e.g. Parent obtain recordset count property from Sub form
2. Display the subform with blanks.
[URL]
View 3 Replies
View Related
Feb 11, 2014
I'm working with a report that totals the number of times a topic is returned from a query. If a topic is not returned at all, i don't want it to show at all. Currently it is showing a blank field for that topic name and blanks in the count as well. Here's the filter i've put in to pull the right data out of my query: =Sum(IIf([Caller Used Resources]="No",1,0))
View 2 Replies
View Related
May 3, 2015
The code below always displays opens form even when field being tested is null.
Private Sub Assign_Classes_Click()
On Error GoTo Err_Assign_Classes_Click
Dim stDocName As String
Dim msgboxstring As String
Dim stLinkCriteria As String
Me.name_found.Requery
[code]....
View 14 Replies
View Related
May 15, 2013
All. Using access 2010. I have a query that returns 92 records. When I put in the criteria for one field to leave out records with “approved” which totals to 9 records, the query only returns 10 records. It is not returning the records that are blank(not null) for that field. I want those records. Why is this happening and how can I get the blanks for this query?
View 2 Replies
View Related
Jul 8, 2014
I was wondering if it is possible to determine weather to show a field based on the values from another field.
Field1, SHOW_Field1, Field2, SHOW_Field2
Field1 and Field are text, SHOW_Field1 and SHOW_Field2 are Boolean values
So if SHOW_Field1 is true, then show Field1 in the query else hide. I was wondering how to do this, I seem to not be able to do this for some reason.
View 3 Replies
View Related
Apr 8, 2013
I need to show an image called PAID if my hidden field Paid Date has a date in.
I thought this would be straight forward but so far nothing.
I have my images within the database itself on Access 2010, so I am not referencing any in a C drive or other location.
Code:
Private Sub Report_Current()
On Error GoTo ErrHandler
If paiddate = Date Then
Paid.Visible = True
Else
Paid.Visible = False
End If
ErrHandler:
MsgBox "Error detected, error # " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"
End Sub
But with no joy.
View 8 Replies
View Related
Jul 13, 2015
I'm an Access novice. I have a query based report that is based on several tables. All tables are joined by the same field "customer ID," but 1 table is not available under "Add Existing Fields." I cannot figure out why that table isn't available, but I need to add a field.
View 8 Replies
View Related
Oct 15, 2005
I have a downtime database that tracks units down, time on, reason down, etc. When a unit goes down, I log the name of the unit and the time down in separate fields. When a unit goes back on line, I log the time on and the reason in separate fields. All this is in a form. When the unit goes on line, I want the user to be forced to enter a reason only after he has entered a time on, not before. The reason field must be left blank until the unit is on. How do I do that? I have searched the forum for this and have not found leaving a field blank based on another fields data. I will supply whatever you need to help me. Thank you.
View 6 Replies
View Related
Jul 18, 2013
I have a couple different reference files that get updated each week. Sometimes there are missing data elements, so I'd like to structure a select query to show me those records that have blank elements but I'd like the similar records to be pulled in as well, so I can make a determination as to how to populate the blank records..
See attached example: I have a client ID reference table that gets populated with forecast owner names (individuals responsible for the customer) from a couple of different sources. Sometimes there are names attached and sometimes the field is blank.
How can I structure a query to show me just those Client ID's that have multiple entries with blank AND non-blank forecast owners? I'd also like to exclude single/multiple records where there are only blank records...
View 3 Replies
View Related
Sep 1, 2006
I almost have this form done.
I have a form with a combo box, and 2 subforms from it. When you select in the combo box (Group), it brings up the choices (Sections) for that group in a subform. Then there is another subform, that is supposed to bring up a series of questions that are related to the section that has been selected. The question field has a drop down to a list table, that has all the questions. Once the question has been selected, it stores in a Master table. The relationship is there. If you try to select any questions that do not pertain to the section, it gives you an error message.
What I need it to do, is when the section is selected, filter out the questions that pertain to that section, and have those questions be the only ones available in the drop-down list to choose.
I know that I need to have an After Update code once the section has been selected, but not sure how the coding should be.
I hope that someone can help with this!
:confused:
View 1 Replies
View Related
Sep 1, 2006
I have a form that is almost complete
I have a form with a combo box, and 2 subforms from it. When you select in the combo box (Group), it brings up the choices (Sections) for that group in a subform. Then there is another subform, that is supposed to bring up a series of questions that are related to the section that has been selected. The question field has a drop down to a list table, that has all the questions. Once the question has been selected, it stores in a Master table. The relationship is there. If you try to select any questions that do not pertain to the section, it gives you an error message.
What I need it to do, is when the section is selected, filter out the questions that pertain to that section, and have those questions be the only ones available in the drop-down list to choose.
I know that I need to have an After Update code once the section has been selected, but not sure how the coding should be.
I hope that someone can help with this!
View 1 Replies
View Related
Oct 20, 2004
I've got a text box, populated by a query.
(it looks like a list because it's tabular view)
you can always see the empty record... any way you can stop it showing?
or failing that, I have a dblclick event on all the textboxes. But It doesn't work with the blank record..
this is quite frustrating.. any help please?
View 5 Replies
View Related
Sep 23, 2005
Have a strange problem. I've created a form with multiple subforms and if there is a record for the subform to display everything looks fine. However, if there is no record for the subform it does not show anything on the screen or if printed.
I would like for the fields on the subforms to show no matter what but I can't figure out why it doing this. I've checked the display parameters and everything is set right.
How do I make the forms show no matter if there is data to display?
Any help is greatly appreciated -
Thanks
View 2 Replies
View Related
May 3, 2005
Hi,
Each client has either an entry with a correspondence number or a notes number. So, the table would look somewhat like this:
Client: 333, Correspondence: 1, Notes: Blank
Client: 333, Correspondence: 2, Notes: Blank
Client: 333, Correspondence: Blank, Notes: 3
Client: 333, Correspondence: Blank, Notes: 4
How would I make a report that will make it look like this
Client: 333, Correspondence: 1, Notes: 3
Client: 333, Correspondence: 2, Notes: 4
Thanks! Hope that was clear.
G
View 4 Replies
View Related
Jan 13, 2014
I have a report that is based on a query I have made. It currently shows the values as kept in the table. It is for a MAR sheet. Here is how it looks...
Breakfast 0
Lunch 0
Teatime 2
Bedtime 1
I want it to show blank for each 0 that is shown. I have tried an Iif statement to do this but it doesn't seem to work.
View 9 Replies
View Related
Nov 24, 2012
I have one combo box bounded with two fields, Id and Description through a query.
I entered a new id in bonded table but for some reason I don't want to enter description now.
And if I delete the entered id then I will loss that unique id which is using for description, which I don't want.Now the problem is one blank space showing in my combo box.
So what criteria I should use in query criteria to avoid blank spaces?
View 5 Replies
View Related
Dec 4, 2014
I have a form with a query assigned to it . it has also some controls to filter the data when i filter the data and there are any relevantr data for the results the form shows nothing for the query and it's true but the controls will be disappeared and i have to go out from the form and come in again to be able to filter the query again.
View 3 Replies
View Related
Jan 27, 2014
I have a query which selects a material ID and material name from one table and the associated manufacturer, supplier, and packaging type from three other tables. Some of the manufacturer, supplier, and packaging data were imported from an Excel spreadsheet and did not have data for those fields, so those fields are blank. When I run the query, I only get the records which have all fields filled out. How can I get the records where the material ID and material name are filled in, but the manufacturer, supplier, or packaging type are blank? Here is the query I'm using currently:
Code:
SELECT tblMaterialSpecifications.ID, tblMaterialSpecifications.Critical, tblMaterialSpecifications.MaterialSupply, tblManufacturer.Manufacturer, tblSupplier.Supplier, tblPackaging.PackageType
FROM tblPackaging INNER JOIN (tblSupplier INNER JOIN (tblManufacturer INNER JOIN tblMaterialSpecifications ON tblManufacturer.ID = tblMaterialSpecifications.ManufacturerID) ON tblSupplier.ID = tblMaterialSpecifications.SupplierID) ON tblPackaging.ID = tblMaterialSpecifications.PackagingID
WHERE (((tblMaterialSpecifications.ActiveInactive)=-1))
ORDER BY tblMaterialSpecifications.Critical, tblMaterialSpecifications.MaterialSupply;
View 1 Replies
View Related
Apr 26, 2006
Ok this null value thing is killing me. I have a parameter query that works great. I know if I use Is Null in the criteria field it will show me this. The problem is I made a search form which the users type the value in and it opens a form based on the parameter query, the parameter on the query is [Forms]![Search_frm]![txtClosed]. If they type a date in here it opens the form and shows the user all the closed records. The problem is they want to see all the records that have no value or Null. I've tried eveything to make this work. I'm ready to jump!! Just kidding. Is there a way to do this with out creating another query. Thanks a bunch!!!
View 2 Replies
View Related
Mar 20, 2007
I have a chart that shows the amount of complaints every month in the last year of a selected costumer. The goal is to see if the amount of complaints from this costumer is decreasing or increasing.
The problem is that some smaller costumers have months without complaints (in real life that isnt that bad). But my graph only displays a dot for the months were the amount of complaints is not null. This makes that the graph line doesnt show the complete picture.
I know that I should be able to solve this with an ISNULL expression. I have tried the following SQL code as Row Source of the graph:
SELECT (Format([Complaintdate],"MMM 'YY")), ISNULL(Count([Complaintnumber])),0 AS [CountOfComplaintnumber] FROM [Complaints] WHERE [Complaintdate] > (Date() -365) AND [Costumername] = Forms!Report_complaintscostumer!Combocustumername GROUP BY (Year([Complaintdate])*12 + Month([Complaintdate])-1),(Format([Complaintdate],"MMM 'YY"));
The result of this is that all the months with complaints now get a 0 value and still the months without complaints arent shown.
What am I doing wrong? Could someone help me solve this problem? Any help is greatly appreciated!
View 1 Replies
View Related
Jun 9, 2014
I have a list of departments, 1-17, where each needs a SUM of their price for each end of day.
At first I was going to make 17 queries, and place each into a new sub-report, but there must be a way to list all 17, even if they haven't had a sale put through.
I've tried linking using "show all values in tblDept and only those that match in tblOrder" - but I cam across a very obvious issue.
The items are grouped by Z1 Number, a unique number for the end of day sales. If there is no department linked to a Z1 number, then it won't show it. For example, if there were no sales in dept01, then there is no record under tblOrder to show a Z1 number for dept01 - so there is nothing to link to in the report.
I was then thinking of creating false data at the end of day so the Z1 number mentioned each department at least once, but that would get messy and not 'normal'
I'm thinking of a type of loop to generate the report so a 17 row report is generated,
View 5 Replies
View Related
Mar 15, 2007
Hi. This might sound stupid but it is a real issue for us.
In our datafile some fields' values were updated to " " . A government entity wants the value to be blank, instead.
How do I change this?:confused:
Likewise, there are some instances where the reverse is true.
How do I change those?
Russ
View 6 Replies
View Related
Jun 6, 2013
I have one table containing name of restaurant with its address etc. Then i created another table to list out the restaurant workers names and details. Just as an example,
Table:Restaurant
Restaurant name
Address line 1
Address line 2
Restaurant #
Website
Table:StaffContact
Staff Role
Name
speciality
email
phone
I have the main form that has all the restaurant details only. And i have another form containing the Staff information. Please note the two table have a relation and it works well.
Now to make it user friendly(basically easier for the lazy ones), I dragged the staff contact form on to my main form and displayed it as a datasheet(basically a sub form).
Now, my boss does not want users to add/delete on this sub form(datasheet). So,he wants me to create buttons to open new record of staff for each restaurant(new form)
My issue is with opening a new record to enter a new person to the staff list and give them a role as well in form view.The new form has
So i ran a Macro, with open form with Where condition
Code:
[Staffcontact]![Rest Name]=[Forms]![MainForm]![RestaurantName]
But, it does not work .
View 2 Replies
View Related
