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 Replies


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

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

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

Moving A Deleted Record From One Table To Another - Version: 2002 (10.0) XP

How do I move a record that I want to delete from one table to another, the record also has an associated table that contains record updates. I want to move both items from the two tables into one history table. Any help would be appreciated.

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

Creating A Record In A Table When Moving From Form To Form - Version: 2003 (11.0)


I've got two tables that are linked by an ID number (Only one is a primary key the other has it's own within the table).

However when I want to create a record with the corresponding value in the second table and link to it with reference when moving from Form A (Data Entry Only) to Form B (Standard Form Limited To One Looked Up Record) I can't seem to get it to work?

Any ideas?


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

Record Position - Version: 2002 (10.0) XP

I don't want to use the standard Access record navigators on a bound form. How do I show on 2 text boxes the total records and the current record number.


View Replies View Related

Set Record Position - Version: 2003 (11.0)

I have a subform, that when the record changes, values are recalculated on the Parent Form. When the values of the Parent Form are requeried, to reflect changes, the subform resets to the first position (first record). This is annoying because if user is editing records, he always gets reset. To avoid this I thought if I can store the position of the current record, and when record is saved (AfterUpdate event) I could move to the next record by setting the Record's Index position. Any thoughs on how can I do this? Thanks for your help.

View Replies View Related

Record Position - Version: 2003 (11.0)

hi all,

i have search the answer of my question and found the link. unfortunately it's been closed due to long period thread. anybody could help me how to show record position in textbox?

thank you,


View Replies View Related

Update Subform Record Without Moving To Next Record - Version: 2003 (11.0)

I'm hoping someone can answer this simple question: I have a subform and in that I have an oncurrent event that looks at a checkbox on the form. If the box is checked, the form shows date and notes fields.

Private Sub Form_Current()
If Me![EfRef] = -1 Then
Me![Notes].Visible = True
Me![Date].Visible = True

Me![Notes].Visible = False
Me![Date].Visible = False

End If

End Sub

That pretty much works for me, but the fields only appear/disappear once I move on to the next record and then back to this record. Is there a simple way to update the record without going to next record and back again?

I couldn't quirte get my head around setting update properties despite spending the last 6 hours on it! Any help appreciated.


View Replies View Related

Undoing New Record And Moving To Existing Record - Version: 2003 (11.0)

I have a CustomerID form control where the table does not allow dups on that field. The form opens with a new record rather than at the first record. If the user selects a CustomerID name that already has a record in the associated table, the code will cancel the new record and move to that record.

My code generates a "can't save the record because there is already one..." error. I've tried to cancel the record using Me.Undo and SendKeys "{ESC}" to no avail. Any help would be appreciated

Randal Johnson

Here's the code:

Private Sub cboStdCustomerID_AfterUpdate()
On Error GoTo cboStdCustomerID_AfterUpdate_Err
Dim rst As Object
' If an order for that customer already exists, open it; otherwise fill in a new record for that customer.
If DCount("idsStdOrderID", "tblStandingOrders", "[CustomerID]=" & Me.CustomerID.Value) > 0 Then
' MsgBox "There is already a standing order for this person which will be opened to edit.", vbExclamation
If (Me.NewRecord And Me.Dirty) = True Then
' SendKeys "{ESC}"
End If
Set rst = Me.Recordset.Clone
rst.FindFirst "[CustomerID]=" & Nz(Me![CustomerID], 0)
DoCmd.SetWarnings False
On Error Resume Next
DoCmd.SetWarnings True
If Not rst.EOF Then Me.Bookmark = rst.Bookmark
End If
Exit Sub
MsgBox "Error Number: " & Err.Number & vbCrLf & _
"Description: " & Err.Description, vbCritical, "Error!"
Resume cboStdCustomerID_AfterUpdate_Exit
End Sub

View Replies View Related

Problem With Record Positioning/moving To Next Record - Version: 97 (8.0)

I am trying to position the recordset for form "Examiners" using a value from a table ("Next_LT_ID"). At start-up, I want to get the value from "Next_LT_ID", and use it to show the the correct record on the form. The code below will do this, but I am unable to move to the next record (error 2105). Any help would be greatly appreciated!


Note: This code is setup so that cmdNextExaminer_Click will cycle though the records in the recordset.

Private Sub cmdNextExaminer_Click()
On Error GoTo Err_cmdNextExaminer_Click

DoCmd.GoToRecord , , acNext

Exit Sub

Select Case Err.Number
Case 2105
DoCmd.GoToRecord , , acFirst
Case 3021
DoCmd.GoToRecord , , acFirst
Case Else
MsgBox Err.Description
Resume Exit_cmdNextExaminer_Click
End Select

End Sub

Private Sub Form_Current()
On Error GoTo Err_Form_Current

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rstNext_LT_ID As DAO.Recordset

Set db = CurrentDb

Set rstNext_LT_ID = db.OpenRecordset("Next_LT_ID")
Set rst = Me.RecordsetClone()

Me.RecordsetClone.FindFirst "[ID] = " & rstNext_LT_ID![Next_ID_To_Use]

Me.Bookmark = Me.RecordsetClone.Bookmark


Exit Sub

Select Case Err.Number
Case 2105
DoCmd.GoToRecord , , acFirst
Case 3021
DoCmd.GoToRecord , , acFirst
Case Else
MsgBox Err.Description
Resume Exit_Form_Current
End Select

End Sub

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

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

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

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

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

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

Inserting Pictures To A Record - Version: 97 (8.0)

I need some help on attaching pictures to a particular record. Maybe up to 15 pics per record. The database will get used on a network of users who do not know much about computers never mind access. Is there anyway/code i can use to create an upload picture button that will prompt the user to find a file on there hard drive andupload it to a unbound frame??

I cant find or think of anything along these lines....i am stumped


View Replies View Related

Start Position Error While Exporting Table - Version: 2002 (10.0) XP

I need to export a table from access to a text file, deliminated. This table is actually data dumped into access by another program. The problem is that it has 200 columns of data, and we need all of it as a text file. I could get a programmer to skip access and just make it a txt to start but they still use it in access too. Any way to my question, when I export this table, I get an error " The field 'field130' contains a start position of '32896'. The maximum start position allowed is 32767."
Does access limit the amount of data you can export, or columns ????? Can someone give me some background on start position and what it means???

Thanks so much.

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

Copyrights 2005-15, All rights reserved