I just want to compare the current record's value in a field with the previous record's value in a field.
Like
If recordset.fields("Field1").value <> recordset.previous.fields("Field1").value
otherwise any code like this:
If recordset.fields("Field1").value <> recordset.position(recordset.absoluteposition-1).fields("Field1").value
I have a table tblExtInc that has multiple records with fields
Code:
StartYear EndYear Amount Inflation Freq Source
2 8 1000.00 .02 12 SS
6 10 2000.00 .00 12 Pension
Here is the code I have to Update a table tblExternalIncomeSchedule
It works fine and updates the table for the first record.
How do I move to the next record StartYear 6 thru EndYear 10 and write that data to the same table without overwriting the data from the first record?
It should add 5 more records to the table
Yr 6 2000
Yr 7 2000 (since inflation = 0)
Yr 8 2000 (since inflation = 0)
Yr 9 2000 (since inflation = 0)
Yr 10 2000 (since inflation = 0)
Code:
Function FillExternalIncomeSchedule(nClientID As Long, nProposalId As Integer, nProposalYears As Integer, nRetireAge As Integer)
Dim rst As Recordset
Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef("", "DELETE * FROM [tblExternalIncomeSchedule] WHERE [ProposalID] = " & nProposalId)
qdf.Execute
[Code] .....
The below SQL is basically trying move a record from one table to another, the table a copies of one another and the AlphaName variable is a string.
I keep getting the incomplete query error, somethings missing but what.
Code:
strSQL = "INSERT INTO [Holding Table].* SELECT ([Import Table].* FROM [Import Table]
WHERE ([Import Table].[Alpha Name] = '" & AlphaName & "'));"
When I add a record in the subform and then move to the next record whilst still in the subform, the main form jumps back to its first record? I then have to move back to the right record in the main form to update the next record in the subform.
I want to move to the next record in the subform without affecting the main form.
I have a subform with records of call details (date, time, subject) - the date and time are autogenerated and subject is a text field. After entering text in the subject field and then clicking on the close button in the main form, or clicking anywhere else in the main form, the record is saved to the table. However, if I enter text in the subject field and then move up to view previous records (within the subform) and then click the close button in the main form the record is not saved in the table. How can I either save the record before allowing the user to move to another record within the subform or before exiting the subform set the focus to the new record so that it will be saved on exit?
View 4 Replies View RelatedHi 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
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
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?
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?
Can anyone tell me how do i move to the previous record in the database using asp. i have tried the following
Code:rs.moveprevrs.moveprevious
hint's will also help
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.
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?
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.
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,
Aidan
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
Hi,
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.
Thanks
k0r54
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
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....
Eddie..
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
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
When moving from a subform back to the mainform and requerying one of the mainform controls, the record selector of the subform moves back to its first record. ...
GoToRecord (,,Next) '(in the subform-this is correct)
SelectObject
Object Type Form
Object Name pv '(go back to the main form)
In Database Window No
GoToControl (SubTot)
Requery (SubTot)If I remove the Requery, the record selector remains where it is supposed to be. The SubTot data source does include the aggregate dsum value of the subform's underlying table. What is strange is that this has been running properly for many years until it was upgraded from .mdb to .accdb format. Is there an easy way to make the record selector stay put?
I am trying to get a record from tbl_new_accounts to tbl_closed_accounts, based on the number entered in a input box.
Code:
Dim StoreNumber As String
StoreNumber = InputBox("Please Enter The Store Number", "")
CurrentDb.Execute ("INSERT INTO Tbl_Closed_Accounts SELECT Tbl_New_Accounts.* From Tbl_New_Accounts WHERE (((Tbl_New_Accounts.Fld_Store_Number)=" & StoreNumber & "));")
I have a tab control with a Counsellor Training tab on it inmy form frmCounsellors. The issue I am having is that when I select a training type from the drop down in the sub form on the tab control on the counsellor training tab and then go to a new record of the main form, not on the sub form, the training type I selected on the form stays the same. If I change it on the second record, it stays at whatever I changed it to, even if I return to the first record it stays at what I changed it to as well.
How do I get it to stay with the counsellor and have it zero out for a new counsellor and it to allow me to enter something different for the new counsellor then save it for that counsellor?I just realised, I am having the same issue with the location combo box but I am sure I could apply a fix to both boxes if I knew one.
Lets say,
Form A has fields: ID, LN and FN
ID LN FN
1 A F
2 B G
3 C H
4 D I
5 E J
Form B has fields: ID, LN and TR
ID LN TR
1 A Y
2 B N
3 C Y
4 D Y
5 E N
I would like to be able to switch between these two forms and the form automatically set focus to the record that I was on the form prior. Meaning if I am on record 3 on Form A and navigate to Form B, I would like to automatically set focus to record 3. Is this possible?
A possible issue after this gets sorted out is what happens if with the same scenario above record 3 does not exits? is it possible in that instance to simply requery the form?
I have a Form named Clients with a multi-page tab named TabCtl0.
The record source tables - primary key is [ID] , TabCtl0 has 7 tabs, the 1st named General.
The 1st 6 tabs show information from the same record.
The 7th tab contains a subform named Focus displaying continuous forms with 3 fields in each record including [ID].
What I am trying (for hours and hours without success) to do is to create a Macro that fires when I double click on a control within subform Focus that takes me to the page named General and selects the record where ID = the ID within the selected record of subform focus.
I have two listbox (SearchResults5 has two columns) and list_asset_add (one column) both have Extended multi-selection active. I have this code that automatically moves the items between the two listbox (it runs after pushing a button):
Code:
Sub CopiTo_Click()
Dim Msg As String
Dim i As Variant
If SearchResults5.ListIndex = -1 Then
Msg = "Nothing"
[Code] ....
This works quite well if I manually select the items that I want to move. Most of the times SearchResults5 has lots of elements so I have decided to create a "Select All" button to speed up the process, here the code:
Private Sub Command271_Click()
Dim n As Integer
With Me.SearchResults5
For n = 0 To .ListCount - 1
.Selected(n) = True
Next n
End With
End Sub
When I use the "Select All" button and I try to move the items between the two listbox, the function does NOT work.
BUT if I manually select one or more items in the first listbox, then I clear the selection and finally I use the subroutine to move the items between the two listbox, then the it works well again. How to make it work properly.