Reports :: DLookup With Auto Date - On Load Sequence
Dec 17, 2014
Ok, so I have this table (tblPayPeriod) with the fields Start Date, Stop Date, Pay Period & Percent FY. The Percent FY field is a Number field that gives off the percent of the fiscal year (FY) that we are in.
Then I have a report (rptDistrictMgr) that in the header I want it to display what percent of the fiscal year we are in based on the Auto_Date that is in the header.
So, if the Auto_Date is December 17, 2014, we are (according to tblPayPeriod) 24.1% into the fiscal year and I want the 24.1% to display in the text box which called Text99.
I would just like a simple dlookup that I can use as the control source of the text box or an expression to put into the On Load sequence.
View Replies
ADVERTISEMENT
Oct 8, 2013
Occasionally a user of mine needs to reset a sequence number in a table.
A few months ago I made him a simple app that has two text entry boxes and a button. The first text entry box uses a dlookup:
Code:
=DLookUp("sSessionNumID","qryGetAPIsessionLastValue")
...the other textbox ties to an update query, which updates the sequence number with the new value supplied by the user. Simple.
Then about a week ago it stopped running the dlookup part - nothing shows in the text box that is to show the current sequence num.
Oddly, if I enter a value in the update text box and press the button to update the sequence number (which still works) -- the previously dormant dlookup textbox now shows the new value - so it works ...it just doesn't want to work on this one workstation unless the update query is run first.
What can this be? The update button merely calls a DoCmd.RunSQL with both an insert (storing old value locally in ms access table) and an update (updating the sql server table's seq number using an ODBC DSN and sql server driver).
It's almost like the dlookup falls asleep now and won't wake up until the update is run...
View 2 Replies
View Related
Dec 12, 2014
I am trying to create a multi-user web-based database where all users log-in and see different forms based on a field in the user table.
I am trying to use a DLookup to return the name of the Form I want open based on the command box that shows the userID and UserName
DoCmd.OpenForm , acNormal, DLookup("field", "table", "[UserID]=" & Me.cboName.Value)
cboName row source
SELECT [table].[UserID], [table].[UserName] FROM table ORDER BY [UserID]
View 5 Replies
View Related
Nov 21, 2011
At present I am running Revit Architecture with DBLink to access to edit fields for shelving etc.
What i am doing is fitting out warehouses with 600+ shelving units and they are all numbered.
These numbers are tagged in the drawing.
What I am hoping to do is export the data to access (completed) then amend any changes within access.
Normally, we would put say 600 shelving units on a drawing all tagged 1-600. But then the client may want to delete shelving unit 321.
Leaving the drawing with all shelving units tagged 1-320,322-600.
How would i go about it so access renumbers all data after the deleted field (ie number 321)?
View 2 Replies
View Related
Feb 28, 2005
I want my Switchboard, and all subsequent forms to be maximized on open...so that the form name doesn't show. Is that possible?
View 5 Replies
View Related
Sep 8, 2006
Hiya people, was wondering i know that its probably in search and i did have a look but as its the Weekend i won't be able to work on it and want a quick fix if possible, when i open the database i want a menu form to load automatically? Any Ideas?
View 5 Replies
View Related
Dec 11, 2013
I have a Report containing a sub-report which holds the pictures. Typically there are 3-4 pictures per A4 page and 2-3 pages, so not millions, but for some reason whenever I load I get between 1-3 pictures a page and the rest show as blank. I can repeatedly reload time after time and get a seemingly random number of successful images.
The original images are on a network drive (but connectivity is good). They are high quality images but the load time isn't that bad (Is there a 'normal' load time for these things?) I wonder if it's just timing out after the first X pictures..
View 5 Replies
View Related
Jun 17, 2005
Hi guys. I have some forms in my access database and i want load one of it automatically
when i double click on my access.mdb . I be happy if some one teach me how i can do that.Thanks
View 2 Replies
View Related
Oct 7, 2013
I have a booking sequence. Sales representatives take an order on a booking form and then then click a button to draw up the related report. The form is showing all Booking forms, not the specific one just taken.Do I need an OpenArgs statement? I've tried that but can't seem to get it to work.
View 10 Replies
View Related
Mar 22, 2013
I have 3 table table; Invoice table, Product table and Sale_product table. Sale product table records all sale from the product table
Invoice table has these fields
ID
TOTAL
CASH_TENDERED
CHANGE
Product table has
ID
CODE
QUANTITY
NAME
PRICE
and Sale_Product table has these
ID
PRODUCT_CODE
QUANTITY
PRODUCT_NAME
PRICE
SUBTOTAL
INVOICE
I did main form from Invoice table and sub form from Sale_product table. I want to use DLOOKUP function to load the name and price, quantity and calculate subtotal automatically from the product table based on the product code entered. i have being trying hard and i keep on getting "Name? error"
View 1 Replies
View Related
Mar 28, 2005
Alright, I've tried just about everything else, now I need to tap into the grapevine for some help. I've read every thread about auto pop info, DLookUP and auto fill. I'm so lost and confused. I'm an Access 97 beginner and can't seem to get my form to do the things I need. I'm working with an existing form and trying to get text fields to auto fill with an "After Update" command. I'm not familiar with VB coding and the function isn't working. If someone out there can help me, I'd really appreciate it.
View 12 Replies
View Related
Mar 28, 2005
Alright, I've tried just about everything else, now I need to tap into the grapevine for some help. I've read every thread about auto pop info, DLookUP and auto fill. I'm so lost and confused. I'm an Access 97 beginner and can't seem to get my form to do the things I need. I'm working with an existing form and trying to get text fields to auto fill with an "After Update" command. I'm not familiar with VB coding and the function isn't working. If someone out there can help me, I'd really appreciate it.
View 3 Replies
View Related
Nov 8, 2013
I have a database that stores information for lab testing. Each time a tech does a "step" in the test process he logs it in the table, using an input form. There are different categories, for example preparation, testing, analysis, etc, and each of those steps take time. I have the form autopopulate the date and time with NOW() evertime the form is updated. What i want to do is calculate the time it takes to do each in days. I can easily get how many days it was from now since they logged the test, =NOW()-TestDateTime. What I want to do is get the number of days it took to do each step, ie the number of days between each event. Is there a way to do this?
View 3 Replies
View Related
May 8, 2013
I have a main report with 3 sub reports.The Sub reports have onload events that fill up the text boxes with VBA code. When I open the sub report alone everything works fine. But when I open the main report, the on load of the subreport wont work.
View 1 Replies
View Related
Dec 8, 2005
The title probably doesn't give an idea of what I need, so here goes.
We currently have reference #'s for our bills that include the date requested in mmddyy format, the first 3 letters of the customer, the initials of the order taker and finally a sequential number to show the sumber of bills that day.
example: 120705SEAGMM02 2nd order taken by GMM for Seagate on 12/7
120705SEARLH01 1st order taken by RLH for Seagate on 12/7
I want to generate this number automatically based on the date entered and the initials given of the user. We only deal with one customer at our desk so that will always be "SEA".
I have a query that generates the first portion (date, customer, and initials):
SELECT Format([REQDATE],"mm") AS [Month], Format([REQDATE],"dd") AS [Day], Format([REQDATE],"yy") AS [Year], Format([REQDATE],"mmddyy") AS [Both], [Both] & "SEA" & [PickUpReqData]![INITIALS] AS REFNO
FROM PickUpReqData;
But I can't seem to get my head around the part of generating the number. I know I had done this in a database I created 2 jobs ago but all my files were flooded out in Katrina. (I have since relocated to Atlanta, although I'm not looking for sympathy. But I will take what I can get!)
Anything to point me in the right direction will be appreciated. I am looking to have this on a form and feed the REFNO field in the PickUpReqData table.
Thanks.
Gary
View 3 Replies
View Related
Apr 16, 2014
I have a report that pulls data from a crosstab query. The report works perfectly and prompts for a "StartDate" when it is run.I need a form with a date field that can be selected. Then a command button which when pressed opens the report with the selected date passed as the parameter.the code I have so far is in the on click event of the button:
DoCmd.OpenReport "rpt_12MonthlyInvoices", acViewPreview, , "StartDate=" & Me.txtStartDate
I was hoping that this would pass the txt.startDate field on the form to the report's "StartDate" when it is opened, but it is still prompting for the parameter when the report loads.Should I be using openArgs rather than the where clause? Or do I need to configure something in the "on load" event of the report also?
View 1 Replies
View Related
Mar 7, 2014
I'm using Access 2010. I set up conditional formatting in some areas of my report. They're very simple like if a cell is between 0 and .4, then the background color of the field is red. They work just fine...when I click on the field. I don't remember ever running into this issue in the past. I've been reading potential solutions on the web, but haven't found a way to make the conditional formatting rules kick in when the report is loaded.
View 5 Replies
View Related
May 29, 2015
I have a report that runs code in Report_load, and based on the value of a field it sets some objects Visible=False and others Visible=True etc..
Now I'm trying to move away from acViewPreview and print direct to the printer with acViewNormal but the Report_Load event doesn't run. Is there a different way to run that code when printing directly?
View 10 Replies
View Related
Jul 29, 2014
I have a set of transactions with a Unique ID field and a date. I want to add a field based that gives me the sequence of events for each Unique ID in order of when it happened.
For example, if customer X has 6 transactions, the sequence field would have a number (1-6) in each record that corresponds to the order in which those transactions occurred. The first transaction would have the number 1, the second would have the number 2 and so on.
View 3 Replies
View Related
Jul 27, 2007
I have a form, that when I create a new record it puts a a =Now() time stamp in the Date Field, but I would like for that time to be updated if anything on the record is changed, later on. How do I update that time?
View 3 Replies
View Related
Jul 19, 2013
I'm trying to open a specific set of records when the form is first opened (namely the most recent date that was added). I'm trying something along these lines and am simply unable to get it to work:
Private Sub Form_Load()
Dim strQry As String
strQry = DMax("TranDte", "Transaction")
DoCmd.OpenForm "FrmSalesInp", acNormal, "", "[Transaction]![TranDte]="
& strQry, , acNormal
End Sub
View 5 Replies
View Related
Dec 11, 2005
Hi, i cannot load posts from a specified date and time
PostDate = now()
'AID = accountID of the poster
'read post after added into DB
Set rsForum = Server.CreateObject("ADODB.Recordset")
strForumSQL = "Select * From tblPost where tblPost.PostDate = '"&PostDate&"' and tblPost.PostBy = "&AID&""
rsForum.Open strForumSQL, strForumCon,3,3
PostID = rsForum("PostID")
I gets an error when posting messages, i think is because: tblPost.PostDate = '"&PostDate&"'
PostDate is no text field, its a Date field and i tried to use this symbol: ' around postdate, i also tried to not use it, but then i gets another error:
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'tblPost.PostDate = 12/11/2005 10:32:50 AM and tblPost.PostBy = 6'.
but that is because the date/time format uses spaces.
How can i fix this?
Thanks in advance
View 4 Replies
View Related
Mar 25, 2015
I know in Access that you can filter your work load criteria for each employee which is fine using Como boxes to filter down specific criteria for that employee, however I'm trying to achieve it with date filters between certain dates, and it works but ends up filtering the dates for every employee, I just want it to filter that specific employee .
This is what I have so far.
PHP Code:
Private Sub Date_Filter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "#mm/dd/yyyy#"
If Not IsNull(Me.txtStartDate) Then
[Code] ....
View 2 Replies
View Related
May 28, 2014
I have a table that consist of pricing for different types of products:
Table name: Costs
ID TYPE EastNew EastRenew WestNew WestRenew
1 500 4100 1000 4400 900
2 501 4100 1000 4400 900
3 600 3400 900 4600 1200
4 700 3700 1300 4900 3300
I have a form that consist of many fields that the user needs to manually input data. 3 fields on that form are the criteria i need to determine which pricing to use back on the table. The first field, which name is LTYPE, is a combobox that the user needs to choose as its type (i.e 500,501,600,700). The second field, which name is EastCoast, is a checkbox (Yes/No) that tells me if it's East Coast (East = yes and West = No). The third field, which name is NewRenewal, is a combobox that the user chooses NEW or Renewal).
Example 1, if the user chooses type 501, checkbox is YES for East, Product is NEW. Then the pricing is $4,100
Example 2, if the user chooses type 700, checkbox is unchecked for NO (which is West), Product is Renewal. Then pricing is $3,300
I would like the unbound field to auto determine the pricing based on the 3 criterias chosen. I read around the forums that Dlookup would be the function for this, however, i tried many times but it did not work.
View 6 Replies
View Related
Dec 12, 2014
Cost can fluctuate through the year on a month to month basis. I want to create a report January - Feb... that has the cost for each month Table CostChange has [Date] [Item] [Cost] with a record ONLY when there is a change so: Item X has a cost in Jan and a new cost in Mar
I want report:
Jan Dlookup([Cost],[CostChange],[Date]<=1/31/2104 (should return Jan Cost)
Feb Dlookup([Cost],[CostChange],[Date]<=2/28/2104 (should return Jan Cost)
Mar Dlookup([Cost],[CostChange],[Date]<=3/31/2104 (should return Mar Cost)
What am I missing?
View 6 Replies
View Related
Jul 26, 2015
I have a form (Access 2010) in which i insert contracts; each contract has a start and an expiry date, but instead of dd/mm/yyyy i would like the user to be able to insert just the year, while the day and month are predefined values and they are automatically inserted (i.e. 01/10/yyyy; the year being the only value that changes, and it is manually inserted by the user).
I would like to set a pop up remainder (on form load) x days before the expiry date, but, because too many of them have the same expiry date i am wondering if the reminder can be set on different days, based on another field (i.e. partner location [country]).. i.e. reminder for contracts with Austria to pop up 60 days before expiring, for UK = 67 days and so on.. or even a specific day for each, i.e. for Austria = 01/08/yyyy, for UK 01/09/yyyy).
View 2 Replies
View Related
