Hello my database looks like
Name Age
Hello 21
Hi 25
Bye 27
Welcome 16
Say 19
XYZ 18
Now i want to Retrive All the record between Column 3 to column 6 How i can do that In Microsoft Access
Iam using the following code for inserting the values from access form in to the access database table ITEMS, but iam getting compile errors, may i know where iam going wrong in the code.
Also help me in updating, deleting and viewing of records from database table ITEMS to the form when i run the program.
Following is my code :
Private Sub ADD_Click()
On Error GoTo Err_ADD_Click
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.AccessConnection
'Create an instance of the ADO Recordset class, and
'set its properties
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM ITEMS"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseServer
.Open
End With
'Set the form's Recordset property to the ADO recordset
DoCmd.RunSQL "INSERT INTO ITEMS(ITEM_CODE, ITEM_NAME, ITEM_CATEGORY) VALUES('" & ITEM_CODE.Text & "','" & ITEM_NAME.Text & "','" & CATEGORY.Text & "')"
Set Me.Recordset = rs
Set rs = Nothing
Set cn = Nothing
Exit_ADD_Click:
Exit Sub
Err_ADD_Click:
MsgBox Err.Description
Resume Exit_ADD_Click
End Sub
compile ERROR as follows:
you can't reference a property or method for a contro unless the control has the focus.
hello,
I am trying to get a form to insert info into a access database. I am using dreamweaver 2004, i created the form with 4 text fields and a submit button. Ive added a ODBC connection and dreamweaver can qurery my table.
I added an insert record server behaviour, and i figured dreamweaver does all the work for you but when i saved and uploaded the page to my test server it would not submit and redirect to my chosen page, it only refresh the form.
Has anyone out there done this before and can notice a step that im missing.
Do i need to setup a recordset? because ive tried that as well and no success
help is much appreciated.
I have 3 Dell laptops with data that was used in registrating participants for a conference (names, address, position title, email, and amount paid). Is there a way to combine the data from each computer to make one database? Please provide step by step instructions?
Mona
I have an ASP file that retrieves records from an Access database and displays them in the browser. The problem is that the records come out unsorted. I need to sort them by entry date. I have a field in the table called "EntryDate" that is in the format MM/DD/YYYY. Can I sort by this field? If so, what would the SQL statement be? Thank you.
View 2 Replies View RelatedI need some to PLEASE point me in the right direction.
I have a database of magazine articles that are listed like this:
Magazine Date Article
Time 2003-01 Iran War
Time 2003-01 Pres Campaign
Time 2003-01 Polution
Time 2003-02 Senate Race
Time 2003-02 New Cars
Time 2003-02 Cold Wave Hits
I need to merge that down to look like this:
Time 2003-01 Iran War,Pres Campaign,Polution
Time 2003-02 Senate Race,New Cars,Cold Wave Hits
Can anybody help?
I have recently encountered some issues with my database. When I load data from an .xlsm file to my front end table, I get additional records with no data only the ID. The data I loaded comes in fine, however I get many of these records with no data only the ID. This only happens in one of my tables.
View 2 Replies View RelatedI have put some VBA code into my database to link and text boxes to different tables. The code works, but once I enter a Tracking Number that finds a NABP number I am unable to go to a dofferent record or save. I also want to edit the fields so I am unable to use a query since you cannnot edit queries. Here is the sample of my code.
Code:
Private Sub Form_AfterUpdate()
On Error Resume Next
Dim db As Database
Dim rs As Recordset
[code]....
I want to automatically delete records from a table in MS Access 2002, I want to keep the existing table structure and therefore I do not want to delete the entire table. I cannot find a way to do this through a macro. Any help on this would be appreciated
View 1 Replies View RelatedHi,
I am cloning records in Access 2000 and once a record is cloned, I want to give a record number such as if I clone record with autonumber = 23 the cloned record will be = 23-a and if I clone 32-a will be given id of 23-b. This should be for any record within the database.
Right now when I clone a record is given a new unique autonumber.
Is there a way to acomplish that? If so how?
I think this maybe done through vba but I have no clue how and where to start.
Thanks
df
Hi,
I apologise if this has been posted in the wrong place.
I am currently making changes to a VB front end for a database of jobs in our company. we have approx 2000 job records in the job table. The program uses a recordset to move sequentially up or down the table, record by record as it searches.
The code is doing its job fine, but for some reason or another, the records in the database are not in order. They loosely follow the order you would expect from 1 to 2000, but every now and then deviates. Obviously, when pulling data out one record at a time, this gives the data in the wrong order.
I did not design the database. There was orignally no primary key or indexing. The new job number (an obvious choice for a primary key) is generated by the exectuable program.
I have spent hours trying to apply sorts in different ways, and reading about how records are stored, and my limited understanding has led me to believe that merely sorting records in access is not enough to change the order that they are physically stored in.
My coding abillity is not good enough code round this problem, and the ideal fix would be to find out some way of forcing an ordering from now on (ie would making job number and indexed primary key now prevent this happening to future records) and to find some way of re ordering the previous records (not just sorting them to be viewed in order).
Am I being really silly and missing something or is this a problem people have had before?
Thanks in advance,
Jamie
Finally after 2 1/2 days I have the ability to bring a dbase 5.5 file in to Access 2007.
1.Do a normal export of a DBF file out all dbase.
2. Excel 2007 - open the DBF file. Note: dBase Files (*.dbf)
3. Save as a .xlsx file.
4. Access 2007 - right click on table in left columnImportexcel
5, Note: You'll be looking at a window with a yellow bar across the top.
Browse to your *.xlsx file and open.
6. Select "OK"show Worksheets nextFirst Row Contains Column HeadingsNextNextNo Primary KeyextFinishclose
7. Double Click on file in left column and there it is.
Never got other methods to work, sad to say.
After a day to get to this result, the problems begin. Every time we would change one character field from 50 char to 6 char, or whatever, the out of memory or disk space error would come up. I have 250 gig available and I'm running 4gig of memory with nothing loaded but Access 2007. I even shut down my virus checker.
So I spent a day or two trying to find a wining combo (A File Size That Would Work). And here it is, 148500 records that comprise of a 21.3m file is the breaking point. If I change a character field, 250char to 6 char, I'm ok. If I change a couple more it get the error message. 150,000 will get a memory/Drive error no matter what is changed.
Is there a option in Access that should be on. Because I find it hard to believe that an old dBase program, one step up from DOS, can handle 350,00 records all day long and Access chocks on 148000 records. Up until now I thought Access was the best thing that happened. There has to be something wrong, no software designer would have done this. I even checked for a corrupt file, the best I could. If there is a file checked out there, I’m open to it.
I'm using the following tools/systems:
* Oracle 9i Enterprise Edition Release 9.2.0.5.0
* Microsoft Access 2002
* Access is running on a XP Professional desktop
What I'm doing at the moment is:
* Linking the Oracle tables in Access via ODBC
* Using Access to run develop & run queries against the linked Oracle tables
What I'm trying to figure out:
* What is the easiest way to export the query results to a local (on my machine) Access table?
Some of the queries return < 65000 rows, so I can export to excel & import the excel file back into access. Unfortunately this doesn't work for the bigger queries.
Suggestions?
Hello & thank you in advance.
I am fairly new to using Access and would appreciate any help.
I have a file with 50,000 records
Example:
NAME CATEGORY
Karen Shoes
Karen Sports
Karen Collectibles
Karen Cars
Bob Music
Bob Sports
Bob Cars
The results that I am looking for are a sum of the total category purchases by each name.
EXAMPLE:
NAME CATEGORY
Karen 4
Bob 3
Is that something that I can do via Acces?
Thank you
I'm trying to add a record to the members table in the access database located at c:dbmyforum.mdb. So I looked it up and did a copy and paste from an example, and edited it to match what i need. here's what I've got:
Code:<html><body><%set conn=Server.CreateObject("ADODB.Connection")conn.Provider="Microsoft.Jet.OLEDB.4.0"conn.Open "c:/db/myforum.mdb"sql="INSERT INTO Members (SN,Password,)"sql=sql & " VALUES "sql=sql & "('" & Request.Form("sn") & "',"sql=sql & "'" & Request.Form("pw") & "')"on error resume nextconn.Execute sql,recaffectedif err<>0 then Response.Write("No update permissions!")else Response.Write("<h3>" & recaffected & " record added</h3>")end ifconn.close%></body></html>
But it keeps coming back with errors. What am I doing wrong? I'm sure that the info from the form is going through, and I've checked the sql several times, although I'm not sure about the vb script cause I'm new to asp(trying to learn it now).
Hi, i quite new to programming.
I want to insert some values into the database.
This is my sql statement written in the asp page
Code:SQL= "INSERT INTO Login ([User], Pass,Info,GroupID,UserRootFolder,Email,IsDisabled, IsGroupAdmin,LanguageFile,AccountExpires) VALUES ('"& str_name & "','" & str_password & "','" & str_aboutUser & "'," 5 , &"''"&", '"& str_email & "', " 0 , 0 , english.dat, NULL)
The values 5, 0 , 0 and english.dat are the values i want to imput into the database. They are not variables. The rest will be variables.
This is the error i get
Code:Error Type:Microsoft VBScript compilation (0x800A0401)Expected end of statement/temasek/register_action.asp, line 12, column 200SQL= "INSERT INTO Login ([User], Pass,Info,GroupID,UserRootFolder,Email,IsDisabled, IsGroupAdmin,LanguageFile,AccountExpires) VALUES ('"& str_name & "','" & str_password & "','" & str_aboutUser & "'," 5 , &"''"&", '"& str_email & "', " 0 , 0 , english.dat, NULL)-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------^
How do i write my sql statement
Thanks
I am relatively new to Access and have a simple database which I use to generate Publication numbers for our new publications. I would like to have a text box and button on a form that will generate duplicate records of the current record the same number of times as specified in the text box.
E.g. If I need 6 copies of a record I would like to enter 6 in a text box, click a command button that will generate 6 duplicates of that record.
The code in order to do this 'on click'.
Table = Publications
Form = Publicationsfrm
Text Box = Lines
Command Button = Submit
Primary Key = PublicationNo
I have tried the following code but it is not working.....
Dim i as Long, myRS as Recordset
Set myRS = CurrentDB.OpenRecordset("Publications")
For i = 1 to Me![Lines]
With myRS
.AddNew
![PublicationNo] = Me![PublicationNo]
.Update
End With
Next
Alternatively if I have a duplicate record button that uses a macro is there a way to run that macro the specified number of times in the 'Lines' text box.
I have a form on access, that displays data for the customer and products that he has purchased. I want to set it up in such a way that if I click on the customer it shows all the products he has purchased. And if i click on the products, it goes to the product information page(form).
I currently have a form in which I have put in two text boxes that have the product and customer fields as data source. The form displays records from a query that is used to filter the customer's purchased products sorted ascending.
The form is working partly correct. It displays only one product from the customer that is in ascending order but not all. If I add more text boxes, it displays the same first product repeatedly.How can I set it up as to display all the products that the customer bought for each customer?
If I open a 2010 database on the network, it only shows 128 records.If I open it locally on my workstation I see all 4800 rows/records.
View 7 Replies View RelatedHI THERE.
IVE GOT A QUICK QUESTION ABOUT DATA ACCESS PAGES AND THE DATA THAT IS SHOWN. IVE CREATED A DATABASE THAT HAS AROUND 25,000 RECORDS OF LANDUSE PROPERTIES AND HAVE CREATED A DATA ACCESS PAGE TO VIEW THESE RECORDS. THE PROBLEM IM HAVING IS THAT WHEN I LOAD THE FORM ONLY 1000 RECORDS SHOW OUT OF THE 25,000 THAT I HAVE IN MY DATABASE.
ARE THERE ANY SETTINGS THAT ALLOW YOU TO CHANGE HOW MANY RECORDS CAN BE SHOWN? IS THIS ONE OF THE SHORT FALLS OF DATA ACCESS PAGES? :confused:
ANY COMMENTS MUCH APPRECIATED.
I have a staff database in my office which holds, amongst other things a list of staff holiday taken. Presently the staff have no access to view holidays because they will also be able to view other employees details. What I need to do is enable members of staff to see their own holiday, but no others and Im really not sure how to go about it.
I have a workgroup in place; so each user has their own login/password.
Any ideas would be gratefully received.
Thanks,
Adam.
First time member, Hello.
And sadly i am saying hello with a problem. I am a relatively new user, and i have a amateur(i think) problem.
Doing a booking system, and working in VBA
I want to check if certain records exist by date.
My "bookings" table has 'roomname' and 'date' and 'timeslot' as fields.
My query is:
strSQL = "SELECT * FROM bookings WHERE (((bookings.room)=" & roomName & ") AND ((bookings.date)=#" & dateStr & "#));"
I need to get all those records, and be able do certain checks on them, but I dont know how to execute that in VBA, so i check the records to see which time slots have been booked.
In pseudocode:
If(bookings.date == #xxx# AND bookings.timeslot == xx)
then 'some sort of action'
In short how do i filter specific records using my query and how do i access record information directly in VBA? research suggests recordsets, but not sure how to implement that
Thanks in advance
Access 97 does not display records in their original order of entry in a report or its query whether they are indexed or not. Results
are always in ascending or descending order even when sorting and groupings are not set. Any suggestions?
Hi i hope someome can help. I am importing a data into a table, records are being rejected as they should due to validation rules. However access is not creating a table of rejected records. What am i doing wrong.
Thanks for any help
Hi, I was wondering if there was a way to put a setting on records or fields to expire automatically.
Thank you
Is it possible to access specific records from a Microsoft Access table without locking the whole table?. I have different processess accessing at the same time different records from the same table and I getting an execption, indicating the table is locked. Can any one provide me some help regarding how to access specifi records without locking the whole table in MS Access?
View 1 Replies View Related