Queries :: Make All Data Disappear When In Datasheet View
Nov 22, 2013
I have a query that's been working fine for years, however...the powers that be have requested a change. We need to track the lining owners in cars. We've recently gone to SQL on the back end (which I basically know NOTHING about) but I set up the new table, refreshed the connection, etc.
I can see the table fine and it says it has a primary key, auto numbers, etc. however, as you can see from the jpg below when I add the table to the query and link the id as in all the other tables, for some reason, this one makes all the data disappear when in datasheet view.If I delete the table from the query - it all comes back.
View Replies
ADVERTISEMENT
Nov 20, 2013
Is it possible to append two queries? The queries have the same layout and labels...it's just a matter of combining the data into one datasheet view.
The two queries are below:
1)
SELECT prc.Market AS Geography, trp.Product, trp.Indication, trp.[Variable Name], trp.Period, Round(prc.Index*trp.[Variable Value],2) AS [Variable Value], trp.Outlet, trp.Daypart, trp.[Program Name], trp.Len, trp.Creative, trp.Campaign, trp.[Campaign Name], trp.[Media Type], trp.Vendor, trp.Channel
FROM tblTRP AS trp, tblTRPpercent AS prc
WHERE (((trp.Indication)=prc.Indication) And ((trp.[Media Type])=prc.Type) And ((trp.Geography)="National") And ((trp.Month)=prc.Month));
2)
SELECT prct.DMA AS Geography, tv.Product, tv.Indication, tv.[Variable Name], tv.Period, Round(prct.percentUniverse*tv.[Variable Value],6) AS [Variable Value], tv.Outlet, tv.Daypart, tv.[Program Name], tv.Len, tv.Creative, tv.Campaign, tv.[Campaign Name], tv.[Media Type], tv.Vendor, tv.Channel
FROM tblTVOtherMetrics AS tv, tblDMApercent AS prct
WHERE (((tv.Geography)="National"));
View 3 Replies
View Related
May 1, 2013
I am trying to create a form with a datasheet view that will show me all exceptions worked on all member accounts. I have over 10k members that each could have multiple issues (exceptions) worked on their account. I am using MS Access 2007.
Example: I have a member John Smith, with member id of 1234-00 that has multiple "exceptions" worked on his membership, say 5 database records for his account. My initial query I only want to see him listed once. I then would double click his Member ID to open all activity of his account.
I found the code below and modified it to fit my database and it brings me back the results I need for the above. However, if a material change is made on John Smith's account, his number is incremented. His number would be incremented to 1234-01. The next material change 1234-02, etc. I am looking to modify the code below, or another way to do it, that ignores the "-" and any numbers after it. The numbers before the "-" will never change. I searched and saw the TRIM function but couldn't get it to work.
Below is the code I'm using that has the correct names from my database. I have a primary key set up that increments and is tied to the exception, not the member id.
SELECT firstname, lastname, exceptionswrkd.memberid
FROM
exceptionswrkd AS base
INNER JOIN (
SELECT memberid, Min(id) AS which_id
FROM exceptionswrkd
GROUP BY memberid
) AS sub
ON base.id = sub.which_id;
View 3 Replies
View Related
May 21, 2013
I have a query that returns records in datasheet view. I need a field to accept the user selection from a drop down, then save that to a table. The query is based on a couple queries and one of those queries is based on the table i need to save the user input into. When i make a selection from the drop down list it puts the selection in that field for all the records and then doesn't save.
View 1 Replies
View Related
Dec 21, 2004
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...
View 2 Replies
View Related
Oct 20, 2014
tell me if it is possible to calculate a value in a column in a datasheet view of a query. i.e. I want to take a value in 'Column A' and subtract the value in 'Column B' to create a value in 'Column C'?
View 2 Replies
View Related
Aug 1, 2014
I have windows 7 access 2013, I had a query made, and wanted to add fields to it, so i went into the SQL and added several fields. Only the first one i entered didn't work and on the query is called "Expr1003" when in datasheet view. Its right in layout view, and even when i tried adding the same field in layout view, it made it "Expr1004". spelling is correct, in both source table and query, i don't know why this field doesn't work especially when the others did.
View 14 Replies
View Related
Aug 28, 2005
hey, i hav seen a similar thread about this (i think bout a year ago or so) and i didnt no whether to continue it or start a new thread.Option boxes - Making items visible
that thread did not fully answer my question and after searchin around n finding nuthin i hav decided to ask
in my form i have the payment methods=> cash, cheque, credit. i did have these in a drop down menu, but i wasnt sure how to do what i wanted...which is based on the selection either make a couple of fields (credit card details etc) visible or invisible. so what i have done is changed the drop down list into an option group with radio buttons.
i currently have this coding
Code:Private Sub Frame_Click()If Me.Frame.Value = 3 ThenMe.CardHolder_Name.Visible = TrueMe.Credit_Card_Number.Visible = TrueMe.Expiry_Date.Visible = TrueMe.Type.Visible = TrueElseIf Me.Frame.Value = 2 ThenMe.CardHolder_Name.Visible = FalseMe.Credit_Card_Number.Visible = FalseMe.Expiry_Date.Visible = FalseMe.Type.Visible = FalseElseIf Me.Frame.Value = 1 ThenMe.CardHolder_Name.Visible = FalseMe.Credit_Card_Number.Visible = FalseMe.Expiry_Date.Visible = FalseMe.Type.Visible = FalseExit SubEnd IfEnd Sub
this seems to work although say if my first record has credit card selected, then all the fields are visible, but wen i move onto the next record which is cash or cheque...they are still visible until i reselect cash or credit again..(and vice versa)
i hope this makes sense :P
thanks
View 3 Replies
View Related
Jul 17, 2013
I seem to be having an issue with a split form I've been working on. I created this split form on Monday and it was working just fine but today, I have added a couple minor text boxes. Since then, I can view the form contents in Design View but when I switch to "Form View" or "Layout View", it's all gone minus the logo in the top left corner and the title for the split form.
View 12 Replies
View Related
Dec 10, 2012
I have recently been modifying an existing Access 2007 database, everything seems to be working correctly, except that the main table holding most of the data is acting strangely.
If opened in database view, it is completely blank - no column headers, no data. Record count is correct and I can step through/select records, but there's just nothing visible at all.
The data clearly still exists as it can still be viewed in forms / queries / reports.
Only thing I can think of is that I have deleted a bunch of outdated queries & reports and it's possible that one or more of them may be cross-referenced with the table in another query, but still can't see why that should be causing this problem.
View 2 Replies
View Related
May 23, 2013
I have an image field on a report that keeps showing up even if the field value is null.
=IIf(IsNull([Image_tbl4mgr21]),"",[image_tbl4mgr21])
How to make the white space completely disappear when that image field is empty?
View 3 Replies
View Related
May 25, 2015
I have a linked table(tblxyz) having property set as ORDER BY ID DESC, ID is autonumber, so my table view gives me latest record on top.
Now I have a subform , where i am calling this Table.....
[Forms]![MainForm]![Sub_DisplayFm].SourceObject = "Table.tblxyz"
This gives me datasheet view of the table inside subform but its not showing sorted data
Interesting thing is it give sorted data view when my table is not linked and is in same access file.
View 4 Replies
View Related
Dec 23, 2013
A have a query that selects a multivalue field. The query forms the record source for a subform. The text values in the multivalue field are displayed in the query, but when I save this and view the data in the subform datasheet view the values revert to the primary key values. They are 1,2,3,4 instead of the text values
View 3 Replies
View Related
Oct 15, 2014
How Mark disappear field data based on field data last.
Example:I have a field type in the name and on behalf of another field No.
In the case of the Type-B data is deleted Number field, which is before the character.
View 1 Replies
View Related
Jul 19, 2007
See title :)
I have seen that in some sample db's rightclicking the mouse showed the menu with an option to check or uncheck the dataview option, unchecked the formview was applied, but when i 'just' import the subform, i cannot find somewhere the possibility to change from dataview to formview.
Maybe someone can help me with this, probably very simple(?), question?
View 4 Replies
View Related
Nov 14, 2013
I want to "zoom" to the underliyng data from pivot view. When in excel someone doubleclicks a field in a pivot table, it automatically creates a table containing all the lines that field were made up from. I want to achieve the same behaviour in Access.I started to think towards a VBA coda, that could be initiated from the form's double click event. It should go to datasheet view with the prpoer filter criterias.
View 1 Replies
View Related
Apr 6, 2006
I have a form which is opened with a filter from another form. For some reason it views the form in form view, although it was set up as datasheet view. I even went to the form properties and the default was datasheet and i made it not to allow other views. This still didnt solve it. I want it to be in datasheet view because i want to show multiple records at the same time.
If u kno how i could resolve this please offer ur help
thanks
View 6 Replies
View Related
Jun 3, 2015
I use Access2007 under Windows 8.1 64 bits.Just ignore any message related to a splash form.The form will register revenues in the table TblLanc Receita sPJ. When either saving record input or closing the form, incomplete records must be eliminated from the table. When opening the form you should be able to see the unbound list of revenue inputs made. The list will show empty though because the last time I closed the form thru the button Close, table data disappears. Also, the same happens if I get into the form design mode to analyze the code and re-execute the form.
Please, click on the button Add to input a couple of revenue records thru the form to verify what I just said.I translated relevant input fields so the input should not be a problem. Fields named Quotation, Direct Purchase, and Contract, can be filled in with any data... just make sure only one of these fields per record be input, otherwise you'll get a pokayoke message. Save each input. After 2 or 3 record inputs, close the form and re-open it. Table will be empty. Redo and save new inputs. Go to the form design mode just because. Re-execute the form. Table will be empty again.
Another point is I want to click on a record of the unbound list and have the clicked record fields properly filled in the form. From that point I would decide if the record would be deleted or changed. The functionality of the entire code works but the selection on the list. In other words I do not understand why I am not able to fill in the form with the selected record fields. My code should do that but it does not.
View 1 Replies
View Related
Nov 3, 2006
when you select the Data sheet View you display error messages in the Accounts Sub reports fields.
The database has been included as a reference:
Forms- Double click Accounts Form By Discipline -data sheet view
Under Current balance,Spent, and Encumbered Columns you get error message instead of the actuall totals.
Please tell me how to correct this prolsdms
View 1 Replies
View Related
Aug 20, 2004
Is it possible to switch the view of a subform to datasheet when a command button on the mainform is selected?
I know it is something involving acFormDS...but I cant figure out the rest of the code! I appreciate your help in advance!
(P.S. I know you can right click the form and select datasheet. Unfortunately there are not many people here that are familiar with MS Access and I dont trust that they will know this)
View 1 Replies
View Related
Jun 18, 2007
I am reviewing a program that in datasheet view has '+' signs in the left-hand column -- it displays rows of companies. When I select a '+' another 'sub-datasheet view' of employees of that company appears, partially covering the original datasheet view of companies.
Is this the way sub-forms look in datasheet view or is something else involved?
Thanks
View 4 Replies
View Related
Jun 2, 2014
I'm relatively new to access. I have a database i created for my school project everything works fine until I try running a query with 2 or more tables, the queries return a datasheet with all the table headings but no data content, I ensured that there is data in the tables that I'm trying to join for the query.
View 6 Replies
View Related
Jul 17, 2006
Hi. I am trying to create subform in datasheet view with one column (total 3)as drop down list. Is this doable? Thanks.
View 3 Replies
View Related
Apr 25, 2007
Is it possible to do a query of the datasheet view of a subform? If so, how does one go about it.
Thanks.
eileen
View 6 Replies
View Related
Mar 9, 2005
hi
i have created subforms and set the Default View to Datasheet
Now i have a form where i am adding command buttons on it to load those subforms but They are not Loading it as DATASHEET VIEW.
Any Clue?
Thanks
View 5 Replies
View Related
Jan 5, 2006
I have a subform that is in databsheet view. When the user logs in to the db it sets the global variable with the group level they have.
I would like a column to be hidden for a certain group of users and have tried the following.
On the subform opening event i have
if vlevel = "engineer" then
me.Hours.columnhidden = True
Else
me.Hours.columnhidden = False
End If
And i have done the same on the Main Form open event identifying the subform by Form!Subform![Hours].columnhidden = True
But neither worked.
Can any suggest something.
View 4 Replies
View Related
