Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS ACCESS & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Inserting Or Moving A Record To A Different Position In The Table

With a table in open view can a record be moved to a different position, i'm using access 2003. Also is there any way to sort besides ascending or descending?
I've got a lot of tables to make and if I forget a single column/field and have to add it later (like I have done all ready numerious times) I'm almost having to recreate the table again in order to have the field fit in the correct spot in the table. These tables are bound to combo boxes so its important every thing is grouped for easy choosing.

Thanks--All help will be very appreciated

View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Moving A Record From One Table To Another
I have a database of assets.

For example a computer will have an asset .

This database will show the asset, where it is, what it is and who its with .
It will display this on a form.

When this asset gets given to someone else i want a button on the form which displays the assets current data to be clicked and for that info to be moved from the asset current table into the asset history table.

any idea what vb code would do this?

View Replies !   View Related
Moving A Record From One Table To Another
Database is an asset register.

I have a table called assetcurrent

It displays the asset and where it is, who its with and what it is.

I want a button on the form which when clicked will move the record from the table assetcurrent into another table called asset history.

Any ideas?

View Replies !   View Related
Newbie Help!!! Moving Record To Another Table....
Quick Scenrio:

I have a current employee's table and Retired/Resigned table, when an employee either retires, resigns or gets fired, i want to be able by the click of a switch or check box of some sort to move that record to the retired table. Your input is greatly apprectiated....


View Replies !   View Related
Combo Box Not Inserting Record In Table

i have attached a document with print screen of the form and combo box properties i am working on

the way it works is, when the user selects a code, the appropriate desciption, uoi and price is displayed. but for some reason the price combo value is not being written to the price field in the table. all the data on the form except for item desc and uoi is written to tbl_waste. the item info for the combo boxes comes from qry_items.

please help..i have been banging my head for days now...thanks

View Replies !   View Related
Deleting Record Using Main/Subform While Maintaining Record Position
Hello All,

I have an ordering db that utilyzes the typical Form / Subform layout. I
would like to be able to delete a record from the Main form and maintain the
same postion relative to the other records. For example, if I am scrolling
through the records and want to delete record #45, After I delete it I would
like to be able to have record #44 visible to the user.

I am currently using (2) SQL statements to delete the records from the main
Order table and the Order Details table which are linked without Cascade
Delete Related Records being on.

I had set-up a recordset procedure to try and find the bookmark set before
deletion but the only way I can get the sub-form to not show a blank form (no
controls visible for the record just deleted and the #deleted in controls on
the main form) is to requery after the delete, which loses the bookmark. If
i place the ReQuery at the end it displays the records properly, (without the
blank record) but returns the record to the first record. I have tried
turning off any sorting references to OrderBy, etc. but it still returns to
the first record.

I have also tried using "DoCmd.RunCommand acCmdDeleteRecord" but it doesn't delete the record from both tables.

Dim rst As Recordset
Dim strSearchName As String

Me.AllowDeletions = True
Me.sfmOrderDetails.Form.AllowDeletions = True

Set rst = Me.RecordsetClone
strSearchName = Str(Me.sfmOrderDetails.Form.txt_Order_No.Value)
rst.FindFirst "Order_No = " & strSearchName

CurrentDb.Execute "DELETE Order_ID FROM tblOrderDetails WHERE Order_ID = " &
CurrentDb.Execute "DELETE Order_ID FROM tblOrders WHERE Order_ID = " &
Order_ID, dbFailOnError


Me.Bookmark = rst.Bookmark
Set rst = Nothing


View Replies !   View Related
Record Position
if i open a form which has 100 records in it, in the navigational buttons at the bottom i can see the number of the record which i am viewing ... for instance record 24 of 100 ... Now if i decide to set the navigational buttons to not be displayed, is there any way through code, in which i can get the record number (for example Record 24) ?

Thank You

View Replies !   View Related
Position Of Cursor On New Record On A Form
I'm trying to ensure that each time a new record is opened in a form, that the cursor is positioned in the first control/field... I have this throughout most of my application but not in a few individual forms.

I know I've done something in the past to ensure that this happens, but I can't remember what it was...


View Replies !   View Related
Can A Hyperlink Subaddress Position To A Specific Record In A Word Doc?
Does anyone know if it would be possible to have a hyperlink subaddress in ACCESS that opened a particular record in WORD? I know that by putting the URL in “hyperlink” I can open up the merged document in WORD, but I'd like to go to the specific record too.

Is there some way I could do this? :confused: :)

View Replies !   View Related
Start Position In A Table
I have a database table which is basically a calendar containing 4 columns (i) the date, (ii) special info about that date, (iii) morning volunteers and (iv) afternoon volunteers.When I go to this table the cursor is positioned at the very first date and i then have to scroll though record after record to get to the current date.How can I get it to remember the last date used?I am using Vista and Access 2003

View Replies !   View Related
Moving To Last Record
Hi guys,

I has a subform in datasheet view. Now when this is loaded I always want to be at the last record in the subform.

I've searched through the forum and am completely unsure of how to do this...

Any hints or tips appreciated :-)


View Replies !   View Related
Record Moving
I need SQL Statements to use in VBS code for microsoft access that will move the current record being displyed on the form (lets say the table name is called 'old') to another table called 'new'. Also the record that is being moved to the new table needs to be deleted from the 'old table'. All of this needs to be done with the click of the mouse on the 'old' form.


View Replies !   View Related
Find A Records Position In A Table
Is there a way, aside from using auto number, to find a records postion. I am using a list and want to be able to double click an entry and have it open a form and go to that record. However I can't use auto number because I allow for the deleting of records, and once deleted the auto number no longer corrilates to the records position.

If anyone knows of a different sytem that renumbers after deleting a record that would be great if you could share it with me. Or if there is a way to open a form and use goto record but instead of using record postion use where statement.

If anyone has any other ideas on this please share. Thanks.

View Replies !   View Related
Moving To Previous Record
Can anyone tell me how do i move to the previous record in the database using asp. i have tried the following
hint's will also help

View Replies !   View Related
Problems With Inserting New Record To Db

I am working with a simple MS Access database. I am trying to add a new user with the following SQL statement:

Set connection = Server.CreateObject("ADODB.Connection")
strSQL = "INSERT INTO UserTable (UserID, FirstName, LastName, Password) values ('" & userName & "', '" & userFName & "', '" & userLName & "', '" & userPWD & "')"

However when trying to run the code, IE gives me the following error:

Microsoft JET Database Engine error '80040e14'

Syntax error in INSERT INTO statement.

/dvdcollection/registration.asp, line 60

Line 60 is the connection.execute(strSQL) line.

I have double check the statement but cannot see anything wrong with it.

I would appreciate it if someone can help me out on this one.


View Replies !   View Related
Retrieve A Field Value When Moving To Next Record.
I use a form that is attached to a Table in order to retrieve/add/change the data of this table (Record source = TempTable).
Every Form field is bounded with a corresponding Table field.

All I need is, when moving to the next record (this can be a new record) using the navigation button, to keep the values in each field from the previous record.
Something like "copying" the previous record values to the next one.

I tried the following:
Form -> Properties -> On DataChange -> [Event Procedure] ->
If Form_MainForm.Text1.Value <> "" Then
Form_MainForm.Text1.Value = Form_MainForm.Text1.Value
End If

No luck. Any ideas?

View Replies !   View Related
Moving A Form To A New Record On Open
Ok i'm opening a form with the intention of grabbing the last reference number and adding one then moving to a new record.

However the form opens on the first record. Here is my code.

Private Sub Form_Open(Cancel As Integer)
Dim lastbookref As String
Dim expans As String
Dim Newbookref As Integer
Form_frmbooking.RecordSource = "SELECT tblbooking.Bookingno, tblbooking.Clientno, tblbooking.Sampledate, tblbooking.Sampleref, tblbooking.[Sample place], tblbooking.Recieveddate, tblbooking.Recievedby FROM tblbooking WHERE (((tblbooking.Bookingno) Like """ & Left(Form_frmClient.Clientname, 3) & "*""));"

If Form_frmbooking.Recordset.BOF = True Then
Form_frmbooking.Bookingno = Left(Form_frmClient.Clientname, 3) & "/00/0001"
lastbookref = Form_frmbooking.Bookingno
DoCmd.GoToRecord , , acNewRec

Newbookref = Val(Right(lastbookref, 4)) + 1
Select Case Newbookref
Case Is < 10
expans = "000" & Newbookref
Case Is < 100
expans = "00" & Newbookref
Case Is < 1000
expans = "0" & Newbookref
End Select
Form_frmbooking.Bookingno = Left(Form_frmClient.Clientname, 3) & "/00/" & expans
End If
End Sub

Anyone have any idea why this is not moving the new record.

Even more bizarrely the new booking reference is inserted into the new record....its just that the form opens up on the first record.

View Replies !   View Related
Moving Information From Previous Record
I need to be able to move 2 fields from the previous record to the next record. Does anyone know how to do this?

What I am doing is the following:

I have a table with 3 fields. I am using a form to enter the information. Two of the fields on the form will be information from the previous record. How can I bring that information forward to automatically be on the form and in the table?

View Replies !   View Related
Moving Record Information To New Records
Hi, found this forum looking for an answer to this question. Hope someone can help.

I'm trying to build a database that tracks electronic components. Mostly the database tracks location movements, but also repair issues and other possible changes. I'm thinking the best way is a database that has a record for each move, and any other changes are included in each record.

My question is, is there a way I can easily have the current information be propogated over to a new record, where it could then be modified? Example: I'm looking at a record of an item that was just returned to me. I would like to click a button on the form which would create a new record, then move all currently displayed information into that record.

Thanks for any help.

View Replies !   View Related
Moving To Tabbbook Moves To Next Record
I have a tabbook on a bound form, and an unbound combobox just before the tabbook. Whenever I tab or enter out of the combobox the form goes to the next record. Is this supposed to happen? If so, how do I stop it.

Many thanks in advance,


View Replies !   View Related
Stop Moving Passed Last Record
I am writing a database with a number of tables with forms attached to each table. I have placed navigation buttons on each form to move to first, last, next, previous. I have set up the code associated with the buttons as "public sub" so it is available for each form to use.

This was working OK except that when I used the NEXT button at the last record it would open a new blank record & keep opening a new blank record on each click.

To over overcome this I put in an If statement to check if it had moved to a new record. To do this I used the following:

If Me!NewRecord Then ..... etc

Now I get an error "Invalid use of Me key word" I suspect it is because I am using ME in a public sub

How do I overcome the problem of moving passed the last record and still have the coding available to all forms?

The Code is below.

Thanks for any help.

Public Sub Next_Record_Click()
On Error GoTo Err_Next_Record_Click

DoCmd.GoToRecord , , acNext

If Me!NewRecord Then
' If new record move back to previous
DoCmd.GoToRecord , , acNext
' Send message
MsgBox "This is the last record", , "No More Records"

End If

Exit Sub

MsgBox Err.Description
Resume Exit_Next_Record_Click

End Sub

View Replies !   View Related
Running A Function When Moving To Next Record

How can i run some vba cose directly after i click the move next button on the navigation but at the bottom of the form.


View Replies !   View Related
Moving To Current Record After A Requery
I have a subform where the user enters the number of training days on a quotation. After the record is updated I do a requery on a text boxt on the main form that shows the total number of training days ordered for that particular customer.

The problem is the focus then resorts to somewhere else on the main form but I want it to return to the same record on the subform. I have managed to get it to go to the first record in the subform but obvisouly from users perception that is not god enough.

I have got the following code in the sub form's after update event but nothing seems to work although I do not get any errors.

Private Sub Form_AfterUpdate()
If Me.TrgType = 1 Then

Dim rs As DAO.Recordset
Dim varBookmark As Variant
Set rs = Me.RecordsetClone
rs.FindFirst ("QuoteItemID = " & Me.QuoteItemID)
varBookmark = rs.Bookmark
Me.Bookmark = varBookmark
Set rs = Nothing

End If

Any assistance would be most appreciated :)

And yes Ghudson I have done a search - LOL :p

View Replies !   View Related
Inserting Text In Front Of Record
I have a field and in front of all records in this feild I would like to add a fixed text. e.g. if the record is XYZ i want to add abc in front to make it abcXYZ. Sorry newbie here.

View Replies !   View Related
Getting The Autonumber Into The Form After Inserting New Record
I really hope I can explain this right :o

When a customer makes a purchase I INSERT the transaction into the 'transactions' table, which I have no problems with. My problem stems from that I can't seem to get the 'OrderNumber' back from the 'transaction' table (OrderNumber is an AutoNumber by the way).

I have tried the following code -
TempOrder = DLookup("OrderNumber", "transactions", "customerID = '" & Forms![frmOrder]!TempID & "'")
"Forms![frmOrder]!TempID" is the value I use from another form (to see who the customer is). I want "TempOrder" to be the OrderNumber that is created in the transactions table.

Regards, James

View Replies !   View Related
Problem Inserting A Record After A Deletion
I have a form and a subform. There are a number of buttons on the form, allowing me to add, update, delete, etc. records from the subform. The process is that I select a record on the subform, its details are displayed on the form, then clicking on <Delete>, for example, removes it.

Each of these processes work fine on their own and also if carried out one after the other, for the most part. The one problem I'm finding is when I delete a record.

Firstly, the subform appears to refresh and the record is no longer shown. However, if I then try to select another row from the subform, I get a message saying 'record is deleted'. I click on <OK> to remove this message and I can then select the same row or another row without any problems.

Secondly, if I now try to add a new record (the code behind the button is just 'DoCmd.GoToRecord , , acNewRec'), I get a message saying that I can't go to the selected record.

If I close and reopen the form between each activity, all works fine, suggesting that something isn't refreshing properly. I've tried putting 'Me.refresh' and 'Me.requery' immediately after running the deletion, but to no effect.

Any suggestions?

View Replies !   View Related
How To Create A Field Showing Numerical Position Within A Table

I am looking for Idea's of how to create a report.

Basically the report is a league table showing Month and YTD, this is the easy bit.

The part I need suggestions and guidance on:

The report also needs to show peoples movements in the league table i.e: whether they have moved up, down or not moved at all from the previous months position.

I was thinking of creating 2 tables: Current Month + Previous Month, in each table the persons position is indicated (an ascending record number is created throughout the table - not sure how to do this yet)

Then using code, lookup each persons position in previous month and write that position number against the persons UID and current position in current months table.

Am I on the right tracks or does someone have an easier solution?

Thanks in advance.


View Replies !   View Related
Prompting Save Message Before Moving To New Record
Hi all,

I Have a form bound to a table.

The form will be used by poor experienced users so to avoid problems i would like whenever we move to a new record(or changing the contents of a stored one) a save record message to appear refering to the one we are just leaving.
Generally i would like a solution which will restrict users from entering data by mistake using scroll mouse or ctr+page down.
If i could design it ,i would choose that nothing in the form is saved until a save all button is clicked.This means that the user will enter virtually as much data as he likes but they will be stored in the table only when the button is clicked,but i don't know if this is possible since my form is bound to table.
Thank you all in advance,
black ailouros

View Replies !   View Related
Inserting A Record With A Date/time Field

I'm trying to insert a record into an Access database from some ASP Code.

I have:

strSQL = "INSERT INTO tblUser (UserID, Date, Comments) VALUES " & _
(" & nID & ", #" & Now() & "#, " & "''" & strComments & "');"

I'd have thought this should work but it doesn't.I just get a "Syntax error in INSERT INTO statement".

My date field, called "Date" (just in case that's a problem!) in my database has no input mask defined, it's just a basic date/time field. I've tried replacing the '#' symbols with single quotes, and doesn't work either. I haven't had any luck finding a definitive example of how to do this, even though it's trivial surely. All the similar examples I've found talk about how to update a record set with a date, or how to set the system locale, etc.

Any ideas as to what I might be doing wrong?

View Replies !   View Related

Copyright © 2005-08, All rights reserved