Hi
I have a set of membership records which have data input via a simple form. When looking for a particular record I use the standard Find menu item on the whole record set from the Form view, and move through the results using the Find Next button. Is there a way to move back through the records when I have got to the end of the results. eg. there may be numerous members with a surname Smith and I can move the the Smith records by using Find and Find Next but if I have moved past the record that I want is there a way of stepping back through the records?
Apologies if this is a really obvious question
in my db i have used in one scenario two separate forms to fill in one record in a respective table.
the user fills out the details in the first form for record 1, presses NEXT, and fills out the remaining details in the second form for record 1.
every time form 1 will be opened, it will need to be at the end of the records (to add a new one of course) so i have used this code in my form 1 open event DoCmd.GoToRecord , , acNewRec
this is fine. however, i have a dilema. when i place that code in form2 also, it skips one further place to the next record(record 2).. and if i dont put any code in form 2, it will always display the record that has just been added previously..
so basically what i need is something to tell form 2 to carry on adding details into the same record that form1 was just on..
I have a main form EMPLOYEE RECORDS with employees information, with a Subform, where pay records are entered. Mysubform always shows record 1 of 1, and I cannot scroll back to see previous records. How can I see the previous entries.
I have an unbound form with a combobox which needs to be validated before the control is updated.In the Before Update event I check that the value is valid and need to reset the value back to the previous value if not valid and then change focus to another control.I thought that the following code would work but the selected value remains in the control and I cannot move the focus to another control.
Code: Private Sub cboYears_BeforeUpdate(Cancel As Integer) Dim dteNewDate As Date dteNewDate = DateSerial(Val(Me.cboYears), Month(Me.txtCalendarHeading), 1)
Code: (SELECT SUM(Cash) FROM qryAccountSetup AS T2 WHERE T2.ClientID = [qryAccountSetup].ClientID AND T2.MonthlyDate < [qryAccountSetup].MonthlyDate) AS PriorCash,
How do you change this to allow multiple values. I looked up subqueries online and it said to nest a second sub for the where clause....
Code: (SELECT (Cash) FROM qryAccountSetup AS T2 WHERE (Select (SELECT MAX(T3.MonthlyDate) FROM qryAccountSetup AS T3 WHERE T3.ClientID = qryAccountSetup.ClientID AND T3.MonthlyDate < qryAccountSetup.MonthlyDate)) AS PriorCash, But i keep getting error.
What i am trying to do is get the previous cash value.... For now, a good indicator is MonthlyDate, as only one date per client will exist. Something can only happen once in a day....IE.
ClientID..MonthlyDAte 1...........03/01/2013 1...........31/01/2013 1...........01/02/2013 1...........and so on.
There can be multiple dates, however the clientID would not match then.... Does this make sense? There is also a PositionID... This is an autonumber field in its respective table.. I have that so i can use it for domain purposes in the future.
Basically what I have is a form where operators can input certain information about a production process, in particular the kg input which has gone into the 'basket'. However this is divided by customer order number and sometimes more than one customers order is placed into the same basket. This means that the weight of the record is not representative of the weight in the basket, and it is the weight of the basket which determines efficiency in the process.
Each basket is designated a 'Disc Number' which is put in the basket so the parts contained can be identified after the process. What I am trying to do is right a code, whether in my table or form (or somehow creating a query) which compares the disc number of the previous record (or previous 2 or 3) and if they are the same the totals are combined. Each record the user places has a hidden ID which keeps them in order, and the records with the same disc number will always be entered after one another in that sequence.
How would I be able to code something to deal with this and where would I put it?The table in question which contains the records is called Thetatbl and then form it links to is Thetafrm. The weight of each record is contained in Orderkgtxt.
I have an option box 1=Yes, 2=Np 3=Unknown. the result is stored in a field in my table. When i run a query I pull up the numbers 1, 2 or 3 but to put in a report I actually want the written answers. How do i convert them back?
i've got a Form that contains Subform with an embedded Query that contains 2 tables only (Payments & Invoices) the Join properties between them show all records from payments and what matches it from Invoices where the joined fields are equal (Invoice no).. So, when i enter certain data in the main form the Subform show the results for it from Payments table and only one field needed from the Invoices table !!
The problem is.. i can't edit or add any data in the Subform results with the previous setting, but when i completely remove the Invoices table from the embedded Query then swift to the Form and it's Subform.. i become able to edit and add data in the Subform easily... !!
So, how to enable the edit/add in the Subform with the 2 tables in the embedded Query ?! Cause it's really needed to show that field from the Invoices table.
Hi. I have recently made a video database for a project, and have have come to a small problem. What I wanted to do was to, using a query search, find the total number of results found, in a form.
Basically, I have a query, in which I have the Film Name, Movie Cost e.t.c, with - Like ("*" & [Film Name], as the Expression (under Film Name).
When using the query, it retrieves results for what you have typed.
I use the query on a form "Film Search", which when clicked on Automatically brings up the Search Box. I also have another form "Films", which has a "Search Films" command button on it, run from a Macro. So basically everything works fine, but I when I search for a film, I want to have a text box, on the form, which has a count for the results.
For example, if I searched say "Shrek", and I got results for Shrek, Shrek 2 and Shrek 3, the text box would say - " 3 results found".
This brings me to the conclusion of asking if I should do an expression, from the original query I am using, or to use Visual Basic, and more importantly - HOW?!?
Any help would be GREATLY appreciated, and I am sorry in advance if one, I have not explained my problem well enough, and two, this is in the wrong section.
Put simply - when I do a find it displays the first account that matches it, then, when you leave the find screen and hit the "next record" key it simply displays the next account (i.e it no longer matches the search criteria).
So my question is, How do I search records, then skip through ALL accounts that match the criteria.
example: 100 accounts in a database. 50 accounts are assigned "Mr A" and 50 "Mr B". Mr A and Mr B sit down to work - hit find, and type in their name. The form shows 1 account, and when mr A hits next fifty times it should not move on to mr Bs (possibly a "no more matches/records" message?).
I hope this is clear enough - and thanks in advance.
ps - I heard something about one form to search - and a subform to display results - the search criteria is static in form one, where as the subform can be skipped ("next record" pressed). But trust me - Im inexperianced :p and would need a nice newbie friendly description of such technological witchcraft.
i have created a parameter query which will find a students best and worst time for each exercise they have done. so you enter the student ID when you run the query and it works fine but i have a problem i need to query to find two students in particular and then i have to create a report from this query on the two students identified and this is where i come up stuck. i have tried typing in both student id's into the criteria but this doesnt work no matter if i put and in it or not and im not sure how to get the job done.
I am not sure where to start or go about creating message box that would display "No results found" if my queries return no results. My search of the forums hasn't been fruitful.
I have created one form that has a text box and a button that works as a search function to run through the tables and displays the applicable queries. Currently, if no results are returned nothing happens. Preferably I would like a message box to display stating that there were "No results found". I would assume that the code would be associated with the "Find" button. Here is the VBA code as it stands, again, I haven't even attempted to add code for a message box.
Private Sub Find_Click() On Error GoTo Find_Click_Err
If DCount("Heading", "Service Desk Manual Query") > 0 Then DoCmd.OpenQuery "Service Desk Manual Query", acViewNormal, acReadOnly End If
Setup a query to find the result in a table containing the highest date value.
The query is linked to two tables : Payment information containing the date, and tenant information containing the tenant.
In the query i have selected the tenant name from the payment table (which is linked to the tenant name in the payment table) and the payment terms - ie weekly / monthly etc. I've then selected the payment date from the payments table.
The query should return for each tenant the latest date they paid.
On the pay date i selected the Max option.
But it shows me more than one record.
SQL query is shown here
SELECT Max(tblPayments.DateDue) AS MaxOfDateDue, tblLease.cboPaymentTerms, IIf([cboPaymentTerms]=2,DateAdd("ww",1,[DateDue]),IIf([cboPaymentTerms]=3,DateAdd("ww",2,[DateDue]),IIf([cboPaymentTerms]=4,DateAdd("ww",4,[DateDue]),IIf([cboPaymentTerms]=1,DateAdd("m",1,[DateDue]),"n/a")))) AS calcNextPayDueDate, tblPayments.cboTenant FROM tblPayments INNER JOIN tblLease ON tblPayments.cboTenant = tblLease.cboTenant GROUP BY tblLease.cboPaymentTerms, IIf([cboPaymentTerms]=2,DateAdd("ww",1,[DateDue]),IIf([cboPaymentTerms]=3,DateAdd("ww",2,[DateDue]),IIf([cboPaymentTerms]=4,DateAdd("ww",4,[DateDue]),IIf([cboPaymentTerms]=1,DateAdd("m",1,[DateDue]),"n/a")))), tblPayments.cboTenant;
I have a DB and I have a button that triggers the find and replace. this will search over 100K entries and filter a specific client. The clients record can be 100's of entries.
This all works fine, but its always ascending and I have to scroll to the bottom for the latest record.
I can't set it to show Descending permanently as some employees can't cope with change. so...
I have a checkbox on my form for either Ascending or descending results. Is there any way I can get the find / replace to reference it, Or can I re-sort them after the search.
I've tried putting re-sort code on the 'find' button as lostfocus and mouseup but it doesn't work.
I want to find out the last 6 months date from todays date. So as todays date is 27th january 2015 so the code should give me the date which is 6 months back from todays date so it will be something like 27th July 2014.
OK, the syntax below works awesomely in a form. The SSN number is pulled from the main table and dropped into a bunch of text boxes. The only text box that is bound is called [sub#] and is stored in the supervisors table, 6 sub's per supervisor and the supervisors SSN is a child of the main table (tried to keep redundant data to a minimum). Now I need to know if there is any way to perform a similar trick in either a query or report using the [sub#](SSN equal to that of a main table entry) field to call up the record from the main table or from a query to use in a report?
Example of working syntax from form... Private Sub Combo48_AfterUpdate() Rank1 = Combo48.Column(4) Sub1 = Combo48.Column(0) Last1 = Combo48.Column(1) Rank1 = Combo48.Column(4) First1 = Combo48.Column(2) MI1 = Combo48.Column(3) EPR1 = Combo48.Column(5) Rating1 = Combo48.Column(6) Next1 = Combo48.Column(7) End Sub
Can you step into an Event Procedure line by line in Access? I toggled the Breakpoint at the End Sub of the Event Procedure, clicked in the middle of the sub, and pressed F8. Nothing! I do this all the time in Excel.
I have been struggling with what should be a very simple task, at least I would think so. Here is what I have. My customer provides me a rolling 8 week forecast. It looks like this.
So you can see that the first week is always the closest to the current date, and every week a new forecast comes out. The problem is, there are over 900 rows for each 8 week rolling forecast (lots of parts and ship to locations).
I think the best way to handle this is to use Access Database and query the data. But I can't seem to get the query right.
I would like to be able to compare the closest week's forecast with the actual quantity bought. Which means I need to line up the calendar week of the forecast given date with the calendar week of the forecast week. I have attached an example.
So you know, I have been using pivot tables for a year now, but the excel file is 50mb and I am out of rows...so access would be nice. Plus, once I can get this figured out, I will be able to link it to my consumption table and generate on the fly reports for management.
It's been a while since I've been on the boards. Good to see lots of the same folks are still here. :D
I've got an interesting problem. I have a sub that calls another sub. Pretty simple there. The problem is that when sub A calls sub B, sub B does not appear to run. This is all sub B does:If Not IsNull(Me.txtNumber) Then Me.txtNumber = Me.txtNumber + 1 End If The weird thing is, sub B runs just fine if I run the sub B in step mode. If I set a break point and use F8 to step through the code line-by-line, it runs just fine. If I take out the breakpoint, the code no longer works.
Anybody have any ideas? I'm running Access XP Sp2 on Windows XP SP1. Thanks.
I have a switchboard and when I click a certain button ... it opens a prompt popup.
In the popup you enter the required "item" and you hit enter or "ok"
It then does a search for the specified "item" and brings up that information. At that time I would like to have a calculator pop up for the user to use. (newb) How in laymans terms do I get this to work ?
In my "forms" Design View I have: Switchboard and Calculator
In my "Queries" I have query 1
In the tables section I have : Sheet one (this is were the query is getting its search info from. Switchboard items
Is there something I can add to my query to get it to open the calculator form ? Or is there some other way without using additional buttons to do this.
Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath(" coordinatordbTC.mdb")& ";PASSWORD=123456") ================================================== ===================================
I have been trying solve this problem for a few days. however, the best i can come into is the above code, however , the following error still approached me.
================================================== =================================== Microsoft JET Database Engine error '80040e4d'
Cannot start your application. The workgroup information file is missing or opened exclusively by another user.
I'm new to access, and have discovered its easy to write large queries by breaking them up into smaller queries and having each subsequent query reference the one before it. My problem now is that I want to combine these queries into a single query, so as to make it easier for anyone who comes after me to edit/understand what was done. I can't seem to find a good explanation of how to do this, but it seems like there should be a way.
I am trying to filter a form to show the entire weekend's activity on Monday but only yesterday's activity Tuesday through Friday. Using this code I can return Friday's results on Monday and yesterday's for the rest. How do I get the range Friday to Sunday?
