Forms :: Produce Datasheet That Derives Its Information From Query
Mar 6, 2013
I have a form that produces a datasheet that derives its information from a query. The query works fine giving the desired information.
In this datasheet on "PurchaseOrderID" i have a On Dbl Click event that states this.
Private Sub PurchaseOrderID_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "FrmPO_Received"
stLinkCriteria = "[ItemID]=" & Me.ItemID
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub
Now it works sometimes, but sometimes it pulls up a different "PurchaseOrderID". Is there a way to fix this. I thought maybe there would be a way to put two criteria, "ItemID" and "PurchaseOrderID".
I have over 100 queries of weather data, for each month, and would like to display certain or filtered information for a selected month.
I have been playing around with a combo box to select a month from a given year, and was hoping that maybe an 'After Update' would do what I want. The problem I have is that I don't know how to do this, as the month picked and displayed in the combo box is variable , and so a sort of wildcard might do the job, if I knew how.
I'm trying to develop a werehouse database for me.and I'm in a terrible dead end.Formely I have a table Articles where I have all the articles on the market with the description, EAN, price. and a table Movement where with a code (related with the article's table) I can store all the item I fisically have in my werehouse and items I sell. (with a boolen field that set if the item is an income or an outcome).
I have a form linked to the Movement Table.I'd like to write an EAN in a textbox query that EAN to the article table and output the results in a datasheet.
The datasheet is populated in a subform, based upon a table.
Code: SELECT DEV_List.* FROM DEV_List;
Within this datasheet, I have several combo boxes.It is easy to give them a query to show all values possible.
Code: SELECT tbl_PPV_RPM.Createdby FROM tbl_PPV_RPM GROUP BY tbl_PPV_RPM.Createdby;
This gives me a list of all possible values in "tbl_PPV_RPM.Createdby".What I want, is to be able to select only the relevant values in this combobox, based upon a materialnumber in the same row. "tbl_PPV_RPM" contains a row named "Material". The datasheet also contains a row named "Material". These need to be linked. When I try to link them through the query builder, I get this:
Code: SELECT tbl_PPV_RPM.Createdby FROM tbl_PPV_RPM WHERE (((tbl_PPV_RPM.Material)=[Forms]![frmSub_TD_List_Edit].[Material])) GROUP BY tbl_PPV_RPM.Createdby;
But then I get a popup box, requesting for the Material number in "frmSub_TD_List_Edit".So, it does not recognise the Materialnumber in the row I'm trying to select a value.
So, it just converts it to ASCII; however, when I go to scan it, it actually reads that information also. I need the 1E, 1D, and 04 to actually be scanned as HEX for the validation to occur for our client.
Is it possible using an Access query to return these desired results? If not, I will have to look atother piece of software to produce the bar code labels.
I've created a query called "JobHistoryQuery" and a form called "JobHistoryMultiForm", this displays all Jobs related to an Asset and all the information about that job. There are some fields (BilledMonth, BilledYear, BilledValue, Parts Required, PartsUsed), however, that I would only like to see if I need to see them (as there is quite a lot of information on the form and ideally, I'd like to be able to see the key information from each job from that asset with ease). What I would like is to create a button that says "Billing" and "Parts" for each job and it brings up a pop up window displaying that information.
I am working on setting up a loop that will get information from a query. I can handle the rest of the code once I get the primary piece of information. Which at this point I can not get. I am trying to get to the next record of my query. For some reason I am stuck on just the first record and it is repeated the exact number of times equal to the number of records in the query. Here is what I have so far.
Code: Dim FSO As Object Dim FromPath As String Dim FromPathCheck As String Dim ToPath As String Dim FinalDestination As String
[Code]....
Yes I do know that I am doubled up on the
Code: rs.MoveNext
The reason is because with out this added rs it goes into an infinite loop. Need generating each value in the TRNumber field in the query.
I have a table of events, tblActivitiesDates which have an activity type and a date.I have a form which has a datasheet with a query source (a separate datasheet form embedded in this one), and 2 radio buttons. The query that is used uses radio buttons this way:
-Activity Type Filter: A radio button, that when clicked a combo box appears, with a list of activity types to choose from. Basically, filtering by Activity Type. -Date Type Filter: A radio button, that when clicked 2 text boxes appear, in which you write the dates between which you want to see the events organized in the table. Basically filtering by dates.
To make reading easier, Activity Type Filter=ATF, Data Type Filter=DTF.There are 4 possible options:
-ATF=ON and DTF=ON -ATF=ON and DTF=OFF -ATF=OFF and DTF=ON -ATF=OFF and DTF=OFF.
For each option, I have written a line of code in the appropriate OnChange/AfterUpdate/Whatnot events in the form: CurrentDb. QueryDef ("qry ActivitiesDates"). SQL = "SELECT..."according to whatever option is currently active.
When I enter values into the form, and instead of embedding my datasheet form I simply open it every time from the navigation pane in a new window, the datasheet is filled appropriately and works 100%. Of course, I have to reopen it every time I change something so the query will rerun.After the table crashes (will be explained soon), I re-embed it to the form before trying it again. The first time I re-embed, no matter what I do, the query doesn't update nor crash.
Say I start with the option ATF=ON, DTF=OFF, it will work fine, updating when needed, but if I tick DTF=ON, regardless of the dates I enter, the table will only change according to the first option (While when open in a new window will show the correct table entries), ignoring the fact that the QueryDef. Sql has changed. Once I tick ATF=OFF, the table crashes and I get the error:"Run-time error'3420': Object invalid or no longer set".The exact same thing happens when I go DTF=ON,ATF=OFF. It ignores the event types I enter, and once I tick DTF=OFF, it crashes with the exact same error.
I added Me.Activities_DS.Form.Requery (datasheet's name is Activities_DS) in every place it should update.
I would like to build a query that has a calculated field, involving value from a previous record. I have five columns, Year (sorted ascending), InvestmentRate, StartingBalance, AnnualContribution, and InterestIncome.
The first row, being the smallest year value, uses a previously defined elsewhere starting balance, annual contribution, and interest income. This is recognized via an Iif statement and establishing the first set of values which the second row should use to calculate.
The second record, and until the end of the query, automatically calculates the new starting balance value based on the sum previous row record's starting balance, annual contribution, and interest income.
Furthermore, for the second record, and untill the end of the query, the InterestIncome is calculated value incorporating the second row's StartingBalance value (which is the product of the sum of the previous record's values).
This calculated referencing to the previous record is causing difficulties.
My computer has been updated to 2010 whilst I've been off sick (was 2003 before my accident).
I've created a main table, for devises across the company, and a combo box/selection box based on another table which holds a list of all the "Responsible" employee's aswell as another combo box/selection box for the device location.
So the person entering the information, can enter all the information for a device (torq wrench, socket set etc), who is responsible for it and which department they belong to (where to find the device).
Which all works fine
However, I'd like to create 2 queries, one to enable the user to run a report of all device's allocated with an employee or to be able to run a query for all device's stored in a particular department.
But I have been unable to set the correct query criteria, to enable to query user to be able to selection from a drop down list, which responsible person or location to pull back the correct list.
I was getting an error asking me to set the parenthesis, I have now deleted criteria for both queries, as even if I put [Enter] and type a Responsible person's name exactly as its held on the table, the report comes back blank.
I need to create a query that will take the results of another query and produce data. The first query shows a bar chart of items showing downtime. The new query needs to take the item with the most downtime and produce a report that shows the reasons for that downtime. The downtime changes all the time so I need to be able to show different results each time. Can anyone help with this?:confused:
New to the navigation form: I need several forms in the navigation form to all relate to the same client ID field. I have this information on the main form but the form with in the navigation form will not pick it up.
I have tried =[Forms]![Frm Client Information Navigation Panel]![ClientID] But just cant get it to pick up that information...
I would like to have something like the master link fields and, child link fields but when I add a form to the navigation form this option in not available.
I have a table which keeps a record of days off sick.
The table has the field [First Day] = the first day they were off sick, in a dd/mm/yy format
and a [Last Day] = last day they were off sick, again in the same date format.
I need to have a query that will produce the actual day, and all of the days in between; so - if the First Day is 25/10/06, it will produce "Wednesday" and then the last day is 27/10/06, it will produce "Friday" But, I also need it to produce "Thursday" (which would be the 26/10/06)
I have 2 comboboxes to select a product no, and a unit of measurement
I have a materialprices table with unique records, keyed on productno and unitno, so i should get either one or no records returned from the following SQL
However, if i compare the values of the returned records with the search values and the tests, it shows a difference, although the difference is then showing as zero when I evaluate it.
(I found this because initially I had written the code using seek, and was trying to ensure I had located the correct record)
I have actually got round this by testing the abs difference as being greater than a minuscule value.
ie If abs(rs!pmpprodno - cboProd)>0.01 Or abs(rs!pmpunit - cboUnit)>0.01 which does not produce an error
I could understand this if I was testing real numbers, but these are all integers - any ideas anyone?
so ignoring the dims - ignore any typos - the code compiles and executes properly - its just the equality test thats the problem
function lookupmat as boolean
strsql = "select * from tblmatprices where [pmpprodno] = " & cboProd & _ " and [pmpunit] = " & cboUnit
Set rs = CurrentDb.OpenRecordset(strsql)
If rs.eof Then lookupmat = False rs.Close GoTo exitproc End If
'having used the above where clause to find an item, this test now seems to produce a difference, even though the figures are the same! If rs!pmpprodno <> cboProd Or rs!pmpunit <> cboUnit Then MsgBox ("Unexpected - There is a difference " & vbCrLf & _ "cboProd = " & Format(cboProd, "###.0000000000") & _ " Lookup = " & Format(rs!pmpprodno, "###.0000000000") & vbCrLf & _ "cboUnit = " & Format(cboUnit, "###.0000000000") & _ " Lookup = " & Format(rs!pmpunit, "###.0000000000") & vbCrLf)
'and now both of these tests are showing a difference! of zero If cboProd <> rs!pmpprodno Then MsgBox ("Prod Was different " & cboProd - rs!pmpprodno) If cboUnit <> rs!pmpunit Then MsgBox ("Unit Was different " & cboUnit - rs!pmpunit)
into the following data format (13 columns in total, with 7 rows):
Project Jan Feb Mar Apr May Jun Jul ... Dec ------------------------------------------------------------------- A............100.................................. .......................................... B.....................200......................... .......................................... C.....................300......................... ........................................... D...............................................40 0......................................... E...............................................50 0......................................... F...............................................60 0......................................... G................................................. .........700...............................
Assuming today is January 1st 2007, the 2nd column must starts with current month.
If someone can post me the SQL, or give me some tips or direct me to an article on how to achieve the results above..it'll be most appreciated.
There is a requirement for 1)Populating a datasheet in a subform with a querystring which is dynamically built in VBA.This querystring is constructed based on the search criteria fields selected by the user in the main form.How can this be immplemented?
2_To click/double click a record in the datasheet,extract data and populate textfields,comboboxes with it which are in the main form.How is this achieved?Also,I can't find click events in a datasheet.
I've got a Datasheet form which has a Datasheet Subform within it. By default, none of the subforms display, and the user clicks the "plus" on the leftmost column. What I'd like to do is prevent a user from having multiple subforms open at once. Basically, when the user clicks to open another subform, the first one closes.
How can this be done?
Also, is there a way to do conditional formatting (either using the wizard or with vba) on datasheet forms?
Hi, I am looking for help with a database I am trying to set up for a dog club. I have created databases before although not for more than 6 years and am now probably at the bottom end of Intermediate in terms of skill and have no experience of using code builder as yet. I need to create a database of dogs, their parents, owners and breeders and a few other bits of information such that I can print a catalogue of dogs entered at dog shows (see below) and am finding it very difficult.
Owner(s), address, name of dog, sex (Dog or Bitch), Date of Birth, breeder, Sire - Dam
I've created various tables: Dogs, Owners, breeders, address, Dogs/Owners (junction table) and Query to do the pedigree of the Dog (ie the parents) which is a self join.
An owner can have more than one dog and a dog can have between 1 and 5 owners at the same time. Where there is more than one owner then the address is that of the first named.
I also need to be able to add and subtract both dogs and owners to/from their respective tables although subtraction will occur less frequently. I then need to be able to produce catalogues with the details laid out as above. Not all of the dogs or owners will be in each catalogue. I anticipate doing this using a query and then using mailmerge in Word.
I appreciate that this may be too big a task to help me with but do not like to give up until I have explored all avenues. If you can help then I can let you know the field and joins that I have and other important details.
I've got a small employee training tracking database - no problem there. What the users currently do is to run a report from the database that shows who received what training, and when. They then open Powerpoint, pull up a training certificate document and manually type the employee name (by referring to the MS Access report), course taken and date the course was completed.
Here's my question: Can Access automatically pull up or go into Powerpoint and produce these Powerpoint certificates automatically? If so, how would I go about doing this? The users would love me forever (or at least until the next request) if this can be automated.
Yhank you in advance - it's amazing how knowledgeable you folks are!!
Hello, hoping for some help on the following scenario:
I need to produce a query which results in one record for each part number.
In a sample table of purchasing history (tblPODetails): Fields: PartNumber, Description, Cost, DatePurchased
We have purchased any given part number numerous times over the years. From time to time, descriptions have changed, as have our cost and of course the date received.
I am attempting to create a query which gives me a list of part numbers with no duplicates, showing the part number, description and cost by max date received.
The resulting description field would of course be the one in use for the latest received date.
I am not having much luck. When I add more fields to the totals query (other than part number and max date received), I get duplicates. I know this should be simple for me...
I have a "date" field and a "Expire dare" field, what I need is automatically obtain a expire date wich will be the last day on the month of the date field plus 1 year. example date = 15/02/2006 expire data 28/02/2007
I'm wondering whether it won't produce only these records because of the design of my report (attached), with the Site Name under the SiteID Header...?? I wouldn't have thought it would matter, but...
I am creating a new form and right now I only have 3 controls: Employee name, Employee ID, and Hire date.
How can I type in an Employee's Name and when I hit enter I get the info in the other boxes to show up?So what I am wanting is to be able to type in a name and hit enter and have all the other boxes populate in the form?
My form is a datasheet with 2 fields: "Product" and "Quantity". Obviously I can't add a calculated field in this type of form; however I would like the word " Total" to display in the "Quantity" field just under the last record. I would also like the sum of the "Quantity" field to display at the bottom of the "Quantity" field in the empty record below the last record.
