Inserting Or Moving A Record To A Different Position In The Table
Hi,
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
Combo Box Not Inserting Record In Table
hi 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 = " & Order_ID,dbFailOnError CurrentDb.Execute "DELETE Order_ID FROM tblOrders WHERE Order_ID = " & Order_ID, dbFailOnError Me.Requery Me.sfmOrderDetails.Form.Requery Me.Bookmark = rst.Bookmark Set rst = Nothing Thanks!
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... Thanks, Peter
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 :-) Cheers
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. THANKS 2 ANYONE
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
Problems With Inserting New Record To Db
Hi, 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") connection.Open(strCon) strSQL = "INSERT INTO UserTable (UserID, FirstName, LastName, Password) values ('" & userName & "', '" & userFName & "', '" & userLName & "', '" & userPWD & "')" connection.execute(strSQL) 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. Thanks.
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" Else Form_frmbooking.Recordset.MoveLast 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
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_Next_Record_Click: Exit Sub Err_Next_Record_Click: MsgBox Err.Description Resume Exit_Next_Record_Click End Sub
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 Forms!frmCustomer!txtUnitsOrdered.Requery Forms!frmCustomer.Refresh Me.Bookmark = varBookmark Me.QuotedPrice.SetFocus rs.Close 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
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
Hi, 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. Darrell....
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
Hi, 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 & "');" conn.Execute(strSQL) ... 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
|
TRACKER
