Hello,
Please tell me if there is a way to retrieve a value from a field in the previous form record in a query that don't have ID (autonumber), using data/time ascending.The function PrevRecVal() from QrySampl can only be used for query that include ID.
Thanks
Okay, here's the query as simplified as possible:
Month - Days - PreviousMonth
Month and Days are pulled from a table called Calendar. How do I get PreviousMonth to display the Month from the previous record?
I was looking at a ranking formula, and it seems like it compares the field to the previous one, but I am not sure how to apply it to my situation. The ranking formula I was looking at:
(Select Count(*) from Data Where [ProductA1] > [Data1].[ProductA1];)+1
Thanks in advance for the help.
I have an 'add new order' form which opens with all the fields blank and focus set to a combo box. The first thing the user must do is select a contract name or number from an unbound combo box. I then want the form to update and show all the relevant information in the form fields.
In the row source of the combo box I select all 3 fields from the same table (Project_Info). Only the contract number is visible all others are set to 0cm width in combo box properties.
In the afterupdate event I have the following code to set the field to the values from the select query
My code looks like this:
[code]
Private Sub ProjectContractNum_AfterUpdate()
Dim sOrder_idSource As String
Dim rs As Object
ProjectName = ProjectContractNum.Column(1)
proj_status = ProjectContractNum.Column(2)
End Sub
[end code]
My SQL in rowSource looks like this:
SELECT Project_Info.proj_name, Project_Info.proj_contract_no, Project_Info.proj_status FROM Project_Info;
Access tells me that I don't have a join key in my record set.
message reads as follows:-
Run-time error '2147352567 (80020009)':
Cannot add record(s);Join key of table Project_Info' not in record set.
Debug highlights this line:
proj_status = ProjectContractNum.Column(2)
'proj_status' is the name of the text box and the control source.
If I just use just the first 2 fields (project number and project name) these two work!!
What am I doing wrong?
Thx, Kev.
Hi everyone,
I have a form with a date field on it. I would like the date field to automatically enter today's date, which it does now, but also I would like it to retrieve the previous record's date and if it is different from today's date, I want the form to use the previous date as the default value.
Does anyone know of how I would go about this?
Thanks,
Chris
i have a form based on a table.The key column of the table is a combo box in the form , if i select a particular value in the combo box then i have to diaplay the all the fields in the record on the form automatically based on that particular value.
Ofcourse it is a simple task, but today only i am trying my hand in VB for the first time in my life.... so i find it difficult to find the answer.......
please help me.......
I'm working on a "Product Details" form somewhat similar to form in northwind, which the record source in "Products" table. what i'm trying to do is after clicking "save and new" command button to go to new record in form, i would like all text boxes to automatically retrieve the value from last saved record set as default value.
after google searching this subject i found the LastModified Syntax but i just don't understand whether i use it in VB or marco, or even how to implicate this. Should I make macro on each txtbox or should i create module? if i have to create either macro or module.
I have code that is executed with the click of a button to enter a new response using 2 fields to differentiate the records: a combobox "cboSrvID" and a textbox "RspnsName". I have a different set of questions for each value in the cboSrvID. Upon selecting my button the record is saved correctly although I would like it to open the blank form associated on the subform "sfrmSurveyResponses" when pushed. I have tried some DoCmd.FindRecord and DoCmd. GoTo functions to try and retrieve the last acLast RspnsID inputted. So far I have had no luck.
Below is the code for the onClick action of my button.
Code:
Private Sub cmdEnterResults_Click()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.SetWarnings False
DoCmd.OpenQuery "qappNewResponses"
Me![sfrmResponses].Requery
End Sub
I have an Access 2010-form which inserts a record in a MS SQL 2008-database by using an ADODB-connection. I need to retrieve the primary key of the newly added record.
With code at the bottom I create a SQL-string which is stored in variable strSQL. If I execute the SQL-string directly in a MS SQL 2008 Query Window, the record is inserted and the MS SQL 2008-result pane shows a column "ID" with the primary key of the newly added record.
If I execute same SQL-string in MS Access 2010 the record is inserted. However, the code fails on Debug.Print rs![ID] with error "Item cannot be found in the collection corresponding to the requested name or ordinal". Same error appears if I use Debug.Print rs.Fields(0). I've enable the reference "Microsoft ActiveX Data Objects 2.8 Library". It looks as the recordset rs is closed as soon the command "Set rs = cn.Execute(strSQL)" finished.
Why I can't retrieve the Primary Key with VBA-code below?
Code:
Private Sub cmdSave_Click()
dim cn As ADODB.Connection
dim rs As ADODB.Recordset
set cn=New ADODB.Connection
[Code] .....
Can I look up and verify data on a "second" form based on a selected record from first (still open) form.
I am trying to allow users to select a User Name from a combo box list and then open "Change Password" form when they select "Change Password" for that selected user name.
My problem is that I can't figure out how to associate and verify the data tied to the user name selected on the previous (Login) form ( I am trying to validate the old password tied to that selected record).
I have the first login form created, and it's working just fine. I also have the change password form created (and it's displaying the user name selected from the first form using:
Code:
Private Sub Form_Load()
With Forms![frmLogin]![cboUserName]
Me.txtPwdChgUserID = .Column(2, .ListIndex)
End With
EndSub
I also have the code written to validate and confirm old password, new password and validate new password (when the save button is clicked). I have yet to update the password with the new password (still trying to figure that out).
Attached zip file has screen shots of the two forms.
I have a table that has 5M+ accounting line entries. Below is an example of one accounting journal in the table.
BUSN_UNIT_IJRNL_DJRNL_ICNCY_CMONY_A
CB0014/07/20140002888269323AUD16797
CB0014/07/20140002888269323AUD-16797
CB0017/07/20140002888269323AUD16797
CB0017/07/20140002888269323AUD-16797
The journal ID above was an accounting entry, debit $16,797 and credit $-16,797. because it was entered as a reversing journal in the system, the table has captured the Journal ID with 2 dates. For my purpose i only want the one date (MIN) date, the total amount of the journal (either the debit or credit amount 16,797) and the total number of lines the journal ID has so in this instance I want the count to be 2 and not 4.
Right now this is what i get
BUSN_UNIT_I JRNL_I CNCY_C SumOfMONY_A CountOfJRNL_I MinOfJRNL_D
CB001 0002888269 AUD 0 4 4/07/2014
This is the output i would like
BUSN_UNIT_I JRNL_I CNCY_C SumofMONY_A CountofJRNL_I MinOfJRNL_D
CB0010002888269323 AUD16797 2 4/07/2014
Im thinking with the total sum because theres debits and credits is there a way to do the absolute value of the journal MONY_A then divide by 2?
current SQL
SELECT [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, Count([One Year Data Lines].JRNL_I) AS CountOfJRNL_I, Min([One Year Data Lines].JRNL_D) AS MinOfJRNL_D, [One Year Data Lines].BUSN_UNIT_I, Sum([One Year Data Lines].MONY_A) AS SumOfMONY_A
FROM [One Year Data Lines]
GROUP BY [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, [One Year Data Lines].BUSN_UNIT_I
HAVING ((([One Year Data Lines].JRNL_I)="0002888269") AND (([One Year Data Lines].CNCY_C)="aud"));
hi there,
I have a form called frmOrganisation with a text field name called txtOrganisationID, and tables tblES and tblOrganisation
now I need to execute an SQL Query
Set rst = New ADODB.Recordset
Dim strsql As String
Dim strsqlInt As Interger
rst.CursorLocation = adUseClient
strqry = "SELECT Organisation_id from tblES WHERE Organisation_id = " & Me.Organisation_id.Value & ";"
rst.Open strqry, CurrentProject.Connection
' i tried DoCmd.RunSQL <strqry> and CurrentDb.Execute <strsql> with no luck...
' i need to get the value from strqry and then pass it to an interger variable, any idea guys?
strsqlInt = strqry ( i need to cast/convert the result to int)
rst.Close
Thanks in advanced guys.... im stucked...
WinXP+Access2000
Hi,
I have secured my db with password. I want to know the technique/technology so that i can read my password. Access must be storing the password in an encrypted form in any password file. My idea is that if I know the file where its stored and in what form then I hope it can be retrieved.
I find so many available sites who claims that they have their product which is able to retrieve access db passowrd. I want to learn on how to do the same.
Any idea or suggestions is highly appreciated.
Thanks.
Hi,
I create the table1 in SQL Server and then link the table to MS Access using ODBC. But, when I write do this statement
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Select * from table1")
for i = 1 to rs.recordcount
msgbox rs.field(0)
next i
It only retrieve 1 record only, but the table have 10 records.
What's wrong with it?
I check rs.recordcount, it only loop one time.
Additionally,
it doesn't work this function in the ODBC link table
rs.addnew
rs.field(0) = "hello"
rs.update
And, when we use ODBC link tables,
we cannot delete data in the table.
currentdb.execute "delete * from table1"
it will give out error message, the table is read-only.
Please let me know about it, thanks.
Hi,
Suppose I have sharing drive, let's say "\server1sharefolderDatabasesdb1.mdb"
if I run this CurrentProject.Path inside the database file, it will retreive the current path with drive letter, but how can I retreive the server, and sharefolder information?
I am able to get the user name of the computer using a function. The function returns the user name in this format: first Name.last name. I wish to display only the first name ( the name before the dot. ) in a text box. I think I can use the len function. How to retrieve only the first name. I also wish to capitalize the first letter.
View 2 Replies View Relatedi want to retrieve some data from a table i have pieced together a bit of code but get an error to few parameters.
Code:
Dim db As dao.Database
Dim Lrs As dao.Recordset
Dim LSQL As String
Dim Lname As String
'Open connection to current Access database
Set db = CurrentDb()
[code]....
when i paste the SQL into a query it works fine
I'm trying to build a form based on a query to use for data editing/entry purposes.
The only way I can think of to do this is to use a query to get data that is particular to my specified customer.
The first problem I have run into is that one query does not contain enough fields to retreive all the fields in my table.
I figure, perhaps I have made a flaw in my design and shouldn't go further with this until I verify it to be true. I don't think I've made a flaw, but the fact that a query has limited maximum number of fields leads me to think I might have.
Each record in the table I'm querying contains several pieces of numerical data that might be recorded during a service visit.
Obviously I could query the table twice but that doesn't feel like the right way to go about it.
Hi,
i have got a database which consists of table, queries, forms and reports.
I have one problem now is that I wan to do a query that will be able to bring the figure from another field to the current one.
ok say i have got Ref_No, Event_Date, Balance_BF, Adjustment, Balance.
so if say this is the first event for this particular ref_no (1234) it will bring the amount from another table (Principle_Table) linking the same ref_no (1234) to the Balance_BF fields,
If this is not the first event for this particular ref_no (1234) it will then bring the amount from the previous event Balance field to the current event Balance_BF field.
I am very lost how to do this. Is there a way doing this using query?
This is wat i mean
Ref No Event Date Balance_BFAdjustment Balance
06/00014 31-May-06 115140-5,757.00 $109,383.00
06/00014 31-Aug-06 109383-5,757.00 $103,626.00
06/00014 30-Nov-06 103626-5,757.00 $97,869.00
06/00014 28-Feb-07 97869-5,757.00 $92,112.00
U can see that the Balance_BF is actually brought down figure from the previous Balance starting from the second events. For the first event, the Balance_BF is actually draw down from another table (Principles_Table)
Is there a way to do this using query?
Can someone please tell me what I'm doing wrong here. I have just added a record to a table. I would now like to get the record number (FamilyID), which as an AutoNumber field, and pass it to another form. My query is giving me an error that says,
"A RunSQL action requires an argument consisting of an SQL statement."
Here is the code snippet:
DoCmd.RunSQL "SELECT FamilyID FROM Families WHERE LastName = '" & _
txtLast.Value & "';"
Thanks,
Lisa
P.S. No one ever replies to my posts. Please respond if you can help. :)
I have a query and using a [text string] as criteria in the Design Window - when the query runs, entering the appropriate search string retrieves the appropriate results. I have created a form using this query as the recordsource - and as such the user is asked for the string before the form loads and the results are displayed.
Is it possible to retrieve the string the user entered and display this value on the form?
Thanks in advance
Searched birthday in threads and couldn't figure this out.
I have a select query and I am trying to retrieve a birthday that is between two dates.
This is how I set up my expressions:
chkBirthDay: Format([BirthDay],"m/d")
Criteria for chkBirthDay Between Format([LaneDate]-3,"m/d") And Format([LaneDate]+3,"m/d"
So if LaneDate = 9/7/2005 I should be able to retrieve all of the Birthdays that fall within 9/4 and 9/10. Correct?
I do retrieve records but they are dates such as 9/17/1921 and 9/18/1948 but of course they do not satisfy the criteria. There is a date in the table that does, 9/7/1981, but that record does not show up?
Any suggestions?
Hey Everybody,
I have 2 tables, one called [Addresses] and the other called [2006_Contacts]. The table [Addresses] has a number of various fields relating to contact data of people. The [2006_Contact] table has 2 fields [Last Name] and [First Name]. I want to run a query that pulls the contact data from the main [Addresses] table, but only if the contact name ([First Name] and [Last Name]) are present in the table [2006_Contacts].
Does anyone have any suggestions?
Thanks,
Chris C.
:confused:
I have been working on a normalized db for the past few months and it works wonderfully. But now I need to find a way to create a form so that the rest of the company can update their data.
My Table has the 5 following fields. Also listed is an example of the types of data I have there
StoreID_Catagory_Customer Year_Month_MixPercentage
IN00001_Japanese_____2005_____Jul_______.01______
IN00001_Japanese_____2005_____Aug______.02______
IN00001_Japanese_____2005_____Sep______.01______
IN00001_Chinese______2005_____Jul_______.35______
IN00001_Chinese______2005_____Aug______.25______
IN00001_Chinese______2005_____Sep______.26______
IN00001_Japanese_____2006_____Jul_______.01______
IN00001_Japanese_____2006_____Aug______.02______
IN00001_Japanese_____2006_____Sep______.01______
IN00001_Chinese______2006_____Jul_______.35______
IN00001_Chinese______2006_____Aug______.25______
IN00001_Chinese______2006_____Sep______.26______
As you can see I have multiple StoreID's for one store, IN00001. My db goes up to IN00419 so it ends up displaying 29,664 records. There are 4 different categories (Japanese, Chinese, Local, and Other). Until now everything has been taken from a datasheet and put into an Update Query but now that other people have to quickly access the system I need a slick interface.
What I need is a way to have a form that can update the table and add new entries that looks like this in the form:
IN00001____2005________Jul__Aug__Sep__Nov__Dec__EC T.
Japanese MixPercentage__.01___.02__.01___.03__.01
Chinese Mix Percentage___.35___.25__.26___.33__.30
Local Mix Percentage_____.60___.70__.69___.62__.65
Other Mix Percentage_____.04___.03__.04___.02__.04
IN00001____2006________Jul__Aug__Sep__Nov__Dec__EC T.
Japanese MixPercentage__.01___.02__.01___.03__.01
Chinese Mix Percentage___.35___.25__.26___.33__.30
Local Mix Percentage_____.60___.70__.69___.62__.65
Other Mix Percentage_____.04___.03__.04___.02__.04
I hope this gives you a clearer picture. I need to be able to update 96 entries at a time (12months, 4 customer types, 2 years) but do it in an efficient manner. The table only has 5 fields so I need to figure out how to display multiple entries on the same form. I also need a way to add new entries. In other words for me to add another StoreID such as IN00420 then I would have to enter in a value for every field for 96 records in the table. When in reality I should only need to update the mix percentages since the months and years remain constant. HELP!!
Any help would be much appreciated. Due to the variable nature of the db I cannot establish very many one-to-one relationships. So therein lies another problem.
a form should open when a record in a listbox is double clicked.. the form doesnt open.. and when i open it independantly i am unable to edit any of the fields.the form is based on a multi table query.if anyone can suggest why it doesnt work i would be most grateful.2003 version on my webspace cos it didnt fit by 100k or something..
View 2 Replies View Relatedhow can I retreive the value (and not the index order with listIndex) from a list box? I have a list-box based on a parameter query and I want to retreive only the selected record (in the list box) data from that query.
View 2 Replies View RelatedIs there a function, or an object property, which will return the name of the Sub routine or Function that is currently processing?
View 10 Replies View Related