Showing A Value If The Data In A Form Field = Something
Aug 18, 2005
I have a form field with a date in it, is there any way I can make the value in that field display one thing, but still run a query based on the original text?
for example:
txtDate is 1/1/1000
if txtDate = 1/1/1000 then display = "*"
but the query still runs on 1/1/1000
I am asking this because I am trying to run a between statement for a date range and the only way I can do the entire range of dates on my table is to use the highest and lowest date possible (1/1/1000 and 1/1/3000) but I only want the users to see "*" so they are not confused.
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
One of my forms in the database is not showing some records in a particular field. Although those records are entered through the same form and are shown in the table, yet some of them are not visible in the form.
This is happening only in a particular field, and in that field, some records are shown , and some are not shown.
When editing a record in Access 2003, the status bar would show the value of each field's description from the table design grid. Is there a way to get Access 2010 to do the same?
I have a table called welding, I want to create a user friendly form for input. The database ultimately wants to go on share point so I am building it in the web database option.
One of the fields is called location, if the location is 'Field' I want 10 more fields to appear for data entry such as weld reason, rail temperature etc . If the location is 'Depot' I want these fields to be hidden as they aren't relevant.how would I do this?
In a table I have three fields 1. City - which is a combo box linked to a query, 2. State - a text box, 3. postal Code - a text box
When i put them on a form I use the info selected in the City box to populate the info in the State and Postcal Code boxes.
The Control source for the city is SELECT tblPostcodes.ID, tblPostcodes.Pcode, tblPostcodes.Locality, tblPostcodes.State FROM tblPostcodes;
Then on the form i put the control source of the state box as =City.column(1) and Postal code as =City.column(3)
This works fine on the form- and the information is retained - but the datasheet view of the table does not record either the State or the Postcode data that was entered. Is this because I put the control source on the form and the table is not recognising it? if this is the case how do enter it from the table?? really confused...
I need some normalizing my data properly, and then showing the values in a form.
Currently, my table relationships look like this;
However, there can be multiple Genres per Band and each Genre will be applied to multiple Bands, and I know this is a Many-To-Many relationship, but I'm uncertain on how to create this properly and then show it in a subform in a form.
I am relatively new to Access 2007, I am having an issue with a sub form not showing all the data from a table.
Basically I have 2 tables: Headers and Line Details, they are linked via an order number. The headers will only ever have 1 record per order number but there can be multiple records on the line details table.
I have imported the data into both tables. When I open my main form, all the data from the header file is all visable and all correct for all records. However when I look at the data in the sub form, it is only showing data for some of the records.
I have checked and the data is correctly linked, and if I change the "source object" to point directly at the table in the sub form it shows the record is there. When I point it back to my sub form it is blank.
I have a form based on a table and I just added 3 fields to the table. When I go to the form to add these fields, they do not show up in the field list. I've also tried creating a textbox and going to the Data Tab and choosing the data source but those 3 new fields are still not showing up. How can I go about adding these 3 fields?
We have a recent issue on client PC. After application running for a while, all the sudden the calculated field on the form not showing the details, though there is data on the control. After restart the PC, the calculated field display correctly. We guess this may be due to the theme we use.
I am trying to run graph on a report from query but what's happening is query shows name but report on graph shows ID'S of the field .
Below is my query
SELECT tblMainTWTTPSheet.txtRootCause, Count(tblMainTWTTPSheet.txtRootCause) AS CountOftxtRootCause FROM tblMainTWTTPSheet GROUP BY tblMainTWTTPSheet.txtRootCause, tblMainTWTTPSheet.Date HAVING (((tblMainTWTTPSheet.txtRootCause) Is Not Null) AND ((Count(tblMainTWTTPSheet.txtRootCause)) Is Not Null) AND ((tblMainTWTTPSheet.Date) Between [Forms]![frmStratificationOfRootCauses]![startDate] And [Forms]![frmStratificationOfRootCauses]![endDate]));
My bound column on main menu form and back end table is 1
This I think is easy, but I can't put my finger on it. I have a table of processes 12 of them. In the query I have them by date. What I want to do is to be able to show all 12 processes even if there is no info in them. I have a completed button, but if there is no info in the process the completed or not completed won't show up. The process is associated to an event. Hope this makes sense.
Any suggestions? I tried in the report to have a grouping, but if no information is present for that process it doesn't show up. I want the process to show up with or without info. So the end users can track the progress on a week by week basis.
I don't know if anyone can help me with this one at all.
I have a query that contains all the possible data that my users will wish to get data from but I want them to have the option to show/hide particular columns to make the query ad-hoc.
I have some code that copies my query to another of a unique name during the course of their Access session so they end up with a query with the name 'ABC' for example that they can run.
If this query ABC has columns A,B,C,D which are all defaulted to be shown and the user decides they are not interested in columns B and C is there Access code that is equivilent to this pseudo-code?
I an trying to create a data entry form (IndividualsEntryFm) to input data for fields such as (First Name),(Birthdate) etc., these to be saved to the (IndividualsTbl)
I also have another table (NamesTbl) which has family names etc. The two tables are linked by a (MainID) field. I want a combo box on the individualsEntryFm so that I can select the family name. Then I wish the empty fields for the IndividualsTbl to be available to enter data.When I press the save button I then want this data saved, together with the MainID from the combo box to the IndividualsTbl.
I have set the IndividualsTbl with a (PersonID) field as an auto number each individual therefore has a unique PersonID but may well share the MainID. I'm trying to link many people to the same address.
I have an unbound field that counts the number of records (=count(emplnumber)). When I open the form the value in the field is not there, if I click on the field the value appears. It's almost like the field is being highlighted by the cursor and covering up the contents, but the cursor has it's focus set to another field. Oh, the field is on the form footer if that matters.
How can I get the value from a field in one table (in the sub form) to copy/insert into a field in another table (in the main form) when adding a new record?The main form and sub form are linked using parent/child linking, and the sub form is in a tab.I have table A (Visit Dates) in the main form which is used to record the date of a visit to a church. Table B (Quarters and Peals) is used to record an event that took place at that church during that visit. Note that not all visits in table A require a record to be created in table B - but half or more do.
In tables A and B I have a field called "QuarterOrPealID" and these are both primary keys, though the field in table B is set to 'no duplicates' and in table A it's set to 'duplicates allowed', as table A has its own auto number/pk. They are both linked in the relationships.
So, when I add a new record to table A using the main form, I might then need to click on the tab in the sub form to create a new record in table B, which has to be linked to the same record in table A. When the "QuarterOrPealID" auto number/pk is generated in the sub form (table B), I need that value to update to the "QuarterOrPealID" field of the main form (table A), so that when I'm viewing these records the form pulls all the information nicely together.
I want to use a column in a query to show where data is missing in other fields.
In excel I have used this statement:
=IF(COUNTA(I5:J5)=2,"","error")
Basically, I have two fields PRICE and WEIGHT. I want a column in the query to show 'Error' (or any kind of flag) when either (or both) of these fields are blank.
In one table i Have component group Id and the description as fields then another table has the sub groupings for each Component Group Id
Example Component Group ID = 1 which is the primary key Description = Keyboards
Component Sub datasheet has Group ID = 1 foreign key Descriptive Options = USB, Wireless, PS2, Serial
I have joined both of these in a query but what i want is the query when i link it to the combo box on the form i have to only show the Group ID once and not repeat it for as many times as each subdatasheet has records.
Example: When i click External Device I dont want to see the word Keyboard being repeated 4 times (which is because there are four subrecords/types of keyboards) Please Advise.
This one is driving me NUTS! I have a form with a combobox, a few textboxes, and a sub-form. On Load the form is populated with a sql command/rcSet.Fields() results. The user then chooses a status from the combobox to narrow down the results. The combobox OnChange event looks like this... sDate = Forms("frm-MENU").txtS_Date.Value eDate = Forms("frm-MENU").txtE_Date.Value xJob = Forms("frm-HD/DVR_CC").lstJob.Value
On Error GoTo hd_dvrErr 'this gets the top of the HD/DVR form sSQL = "SELECT Count(*) AS Total, Sum(IIf([pmt_meth] In ('C','E'),0,1)) AS Error, Sum(IIf([pmt_meth]='C',1,0)) AS [Credit Card], Sum(IIf([pmt_meth]='E',1,0)) AS EFT " & _ "FROM [tbl_HD/DVR_CreditCard(*)] " & _ "WHERE ((([tbl_HD/DVR_CreditCard(*)].CDATE) Between #" & sDate & "# And #" & eDate & "#) AND (([tbl_HD/DVR_CreditCard(*)].JOB_TYPE) Like '" & xJob & "*'));"
Set db = CurrentDb() Set rcSet = db.OpenRecordset(sSQL)
With Forms("frm-HD/DVR_CC") .txtTotal.Value = rcSet.Fields(0) .txtError.Value = rcSet.Fields(1) .txtCC.Value = rcSet.Fields(2) .txtEFT.Value = rcSet.Fields(3) End With
'this gets the bottom or subform of the HD/DVR form sSQL = "SELECT IIf([Agent]='UNKNOWN','xxxxx',[REP]) AS [Agent ID], [tbl_HD/DVR_CreditCard(*)].Agent, [tbl_HD/DVR_CreditCard(*)].JOB_TYPE, Count(*) AS Total, Sum(IIf([pmt_meth] In ('C','E'),0,1)) AS Error, Sum(IIf([pmt_meth]='C',1,0)) AS [Credit Card], Sum(IIf([pmt_meth]='E',1,0)) AS EFT, Sum(IIf([pmt_meth] In ('C','E'),0,1))/Count(*) AS [Error Rate] " & _ "FROM [tbl_HD/DVR_CreditCard(*)] " & _ "WHERE ((([tbl_HD/DVR_CreditCard(*)].CDATE) Between #" & sDate & "# And #" & eDate & "#)) " & _ "GROUP BY IIf([Agent]='UNKNOWN','xxxxx',[REP]), [tbl_HD/DVR_CreditCard(*)].Agent, [tbl_HD/DVR_CreditCard(*)].JOB_TYPE " & _ "HAVING ((([tbl_HD/DVR_CreditCard(*)].JOB_TYPE) Like '" & xJob & "*')); "
Set qdTemp = db.QueryDefs("qry_HD/DVR") qdTemp.SQL = sSQL qdTemp.Close
If Not Forms("frm-HD/DVR_CC").FormFooter.Visible Then Forms("frm-HD/DVR_CC").FormFooter.Visible = True Else 'DoCmd.Close acForm, "frm-HD/DVR_CC(Footer)", acSaveNo End If DoCmd.OpenForm ("frm-HD/DVR_CC(Footer)"), acNormal, , , , acHidden Forms("frm-HD/DVR_CC(Footer)").Recalc Forms("frm-HD/DVR_CC").Refresh Forms("frm-HD/DVR_CC(Footer)").Refresh ' DoCmd.MoveSize Height:=Forms("frm-HD/DVR_CC(Footer)").WindowHeight + Forms("frm-HD/DVR_CC(Footer)").FormFooter.Height
I have used similar code on another form and everytime the search criteria changes the sub form updates to reflect such. I know I am missing something small; can somebody please point it out?:D I need to have the subform show the updated (choice from combo) criteria.
If this seems to cloudy, please let me know and I will try and revise
I have a couple of subforms on one particular PC where the data is showing in some strange font, WingDings or something like that. On my PC the data shows correctly.
Please see screenshot here: http://www.confetti.ie/screenshot1.jpg
I tried changing the Font Name for all the controls in the subform to common fonts Arial, Times etc but it still shows as WingDings.
I also tried changing the default font under Edit > Options to common fonts with no change. In the Options I tried changing font options on the Datasheet and Tables/Queries tab.
I have two forms. The first form has individual data to include the person's Social Security Number (SSN). I have another form (subform) that has other data to include the person's SSN. I added the subform to the form (linked the SSN). Unfortunately, only the SSN field appears in the subform. None of the other data in the subform appears in the subform.
I have a subform on a form which I load up after the user enters a value in the main form by using the sourceobject property of the subform, the problem i have is that after reloading the main form and the user entering another value, the textbox.value of a control on the subform will sometimes not show the correct value. I am aware that textbox.value doesn't change until the after update event, however this particular textbox is just showing information that has just been queried from the database.
I have a Subform that is inside a Subform that its only purpose is to display some calculations but for some reason it is blank. Here is a screenshot of what I am dealing with:
See that area under "Pump Calc" that is blank...it should have data in it:
Here is the query that drives that data:
And here is a copy of that subform running in form view: