Retrieve AutoNumber Problem

Sep 22, 2005

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. :)

View Replies

Controlling Autonumber Field - Start Autonumber From 1 Everyday (Composite Key)

Nov 21, 2013

I have two tables linked to each other in one to many relationship. Instead of auto number, the date and shift (Text) is being used as the primary keys (Composite Primary Key). Here is the tables structures,

Payouts Table:
Date: Primary Key
Shift (Day or Night) : Primary Key

Bills Table:
Date: Primary Key
Shift (Day or Night): Primary Key
Autonumber: Primary Key

The tables Payouts and Bills has one to many relationship. One payout row can have many bills. The problem is that I want to start the Autonumber in bills table everyday from 1. As date and shift are different for every day so even if i start bills from 1 everyday, it wont make same primary key. I can do it manually but I want to make it automatically.

View 4 Replies View Related

How To Retrieve A Value From An SQL Statement

Nov 9, 2005

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

View 1 Replies View Related

Retrieve My Db Password

Apr 10, 2006

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.

View 2 Replies View Related

Retrieve Data

Dec 22, 2006

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.

View 3 Replies View Related

Retrieve Info.

Mar 21, 2007

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?

View 1 Replies View Related

Forms :: How To Retrieve First Name

Oct 13, 2014

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 Related

Using SQL In VBA To Retrieve Data?

Aug 24, 2015

i 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

View 8 Replies View Related

Query Cannot Retrieve Enough Fields

Feb 7, 2007

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.

View 3 Replies View Related

Retrieve Figure Using Query?

Jun 14, 2007

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?

View 4 Replies View Related

Retrieve Query Parameter?

Oct 19, 2005

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?

View 2 Replies View Related

Retrieve Previuos Record

Mar 19, 2006

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

View 8 Replies View Related

Trying To Retrieve A Date Between Two Dates

Apr 28, 2006

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?

View 3 Replies View Related

Retrieve Specific Contacts

Aug 10, 2006

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.

View 4 Replies View Related

Cannot Retrieve Unique Records

Mar 14, 2006

: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.

View 3 Replies View Related

Search And Retrieve Problem

Dec 8, 2006

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 Related

Retrieve The Selection Value From A List Box

Jan 14, 2005

how 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 Related

Retrieve Name Of Current Sub Or Function

Feb 22, 2005

Is 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

Retrieve A Value From A Previously Inserted Row

Feb 19, 2006

Hello,
Im using a ASP and MS Access 2000 database. What im trying to do is: I insert a row into a table, in that table is a field that auto increments. after I insert this row I want to immediately after that be able to retrieve the value of the column containing the auto increment and hold it in a variable so i can use it for other things....

can anyone help me with this? I have been messing around with SQl cursors but cant seem to get it to work right..

Thank you very much
-Justin B.
Edit/Delete Message

View 4 Replies View Related

Retrieve The Table Names

Jun 28, 2006

Hi:

In the database, I want to create a combox in the form, it the dropdown list show all the table names I created, or linked.

I don't want to create a table to store the table names.

Can we retrieve the table names from the Tables sections directly?

Thanks.

View 1 Replies View Related

Queries :: Need A Way To Retrieve First Highest Value

Oct 3, 2013

using access 2010. I have a table where I need to pull out the highest value. But sometimes; there are two or more with the same high value. I need a way to retreive the first highest value. I've done this before a while ago and I can't make it work again. I started off with grouping highest value but are getting all three same values.

View 2 Replies View Related

Queries :: How To Retrieve Column Name

Oct 31, 2013

I have table in which i want to update reason column as "def missing" on 1st record and "abc missing" on second record.

View 4 Replies View Related

Query - Retrieve A Set Number Of Records

Nov 4, 2005

Hi all,

I want to retrieve a set number of records from a table, using a query. I'm not sure of how to do this.

I can't use the ID, because the table gets its data replaced contantly by imports (so the ID doesn;t start at 1...)

I want to be able to retrieve, say, the first 25 records on that table.

Any help is appreciated

View 2 Replies View Related

How To Retrieve Field From Previous Record?

Sep 8, 2006

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.

View 3 Replies View Related

Retrieve Records For The Past Year

Mar 21, 2008

I trying to create a query that just returns all of the records in my database for the past year. I've tried to somehow do it using the date() function but haven't had much luck yet.
Any help is most appreciated.

View 3 Replies View Related

Help !! Runtime Error Trying To Retrieve Record

May 4, 2005

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.

View 3 Replies View Related