Requery Causes Record Change
Oct 6, 2005
I have a Form [customers], which contains a tabbed control and within one of the tabs I have a subform [Loans]. The Loans form is a "display only" form which lists clients loans. The loans can be updated or added to by a popup form which acts as a loan calculator. The "After Update" event of the popup form requeries the [customers] form when the amended loan record is entered via the selection of a new record on the popup. This has the effect of displaying the changed loans on the underlying [loans] form.
This works OK except that the record being displayed on the customers form immediately jumps to the first record in the customers table.
Has anybody any thoughts on why it would jump records after the requery and how I could stop it.
The requery code is as follows
Private Sub Form_AfterUpdate()
Forms![customers].Requery
End Sub
View Replies
ADVERTISEMENT
Jun 17, 2013
I have been using AYS2000 database example from Rogers access library. I have been in the process of modifying to meet my needs. Although I have run into a problem that was prevalent in the original database example. I have attached the original database for your reference.
Problem: At the bottom of the database beside the "New Response" button there are 2 combo boxes that allow you to select the survey and response in which you want to view in the subform. Although when selecting the response the subform does not always update the record in the subform. It would appear that the problem occurs only after a response has been previously selected. I have tried adding additional code to refresh and requery the subform already without any success.
View 3 Replies
View Related
Sep 2, 2013
I have a form which has 3 subforms based on a table with a combo box. When I select value from the combo box the data on the subform changes accordingly using the 'Requery'. However, they also show the record from the 1st value of the combo box. Is there a way I can get this solved?
View 1 Replies
View Related
May 15, 2005
Hi - My first time here, and I am fairly new to Access, but getting on OK. So as ever, be kind.
I have spent several hours reading this forum and learnt a few things, and been completely confused by other things. My Job is IT but Software, Networks etc configuration rather than programming.
I am trying to help a charity out by building a database for their Sportsday.
I have a Parent Form Called Team, with Sub forms in Tabs for Team Details, Competitors etc.
In the Team Details, They can Choose the Hospital and Team Manager.
I have a Combo box with a lookup query, which looks up the Manager. However, rather than just display a last name, I have changed the SQL behind the (Properties - Data - Row Source), so that I get a concatenation of the Surname and First name:
SELECT tblCompetitor.CompetitorID, [surname] & " " & [Firstname] AS Fullname FROM tblCompetitor ORDER BY tblCompetitor.Surname;
All Works fine
I know I can use a "Not In the List" command, but initially they want a button to open an Add Manager Form.
No Problem except when I close and try to enter the New Managers Details in the Combo Box and requery.
I have a button to close the Add Manager Form and requery the Manager Combo in the originating Form
Private Sub CloseCompetitorsEditPopUp_Click()
On Error GoTo Err_CloseCompetitorsEditPopUp_Click
Forms![frmTeam]![ChildTransplantTeam].Form![Manager] = Me.CompetitorID
(the Manager field is a lookup from the Competitor table)
Forms![frmTeam]![ChildTransplantTeam].Form![Manager].Requery
DoCmd.Close
Exit_CloseCompetitorsEditPopUp_Click:
Exit Sub
Err_CloseCompetitorsEditPopUp_Click:
MsgBox Err.Description
Resume Exit_CloseCompetitorsEditPopUp_Click
End Sub
This code works fine if I just use the Managers Surname as the Data Field, but leaves a blank field in the Combo Box if I am displaying concatenation, until I do some sort of refresh.
Any Ideas if I can do this seamlesly as it is driving me potty.
View 2 Replies
View Related
May 26, 2005
I have a form in spreadsheet view. Basically all I want is to update the information which is viewed, but Form.ReCalc doesn't requery the database. So I tried Form.Requery. But after doing Form.Requery, the first record of the form becomes the active record, and in some cases I have to scroll to see the record that was active before the requery. Is there any way to avoid this?
One possible I solution might be to requery that form and return to the record which was active before the requery. Here comes another problem. The form has a subform in spreadsheet view (which is viewed by clicking + to the left of any record in the first form). If I expand an record to view the sub form, it is also expanded after the requery, but I also want the main form to scroll down so the subform is fully visible (or as visible as it was before the requery), or alternatively, scroll down so the top most record in the main form that is visible also is the top most visible record after the requery. If this is not possible, maybe there is a way to make the record which was the active record before the requery to be the top most visible record after the requery.
Could someone point me in the right direction? Give any suggestion?
Any help is appreciated.
View 8 Replies
View Related
Sep 23, 2005
Hi. First off I want to say that these forums are a gold mine. You all do a great job. Keep it up!
I work at a University and keep a database of students. On the main form for the student, you select their name from a combo box and the whole form refreshes to show the data for your selection. There are four subforms that keep track of things like a questionnaire, career center visits, etc. The problem I am having is with the Career Center visits.
There is a button that allows you to make an entry into a log when they come into the career center. Clicking on it opens up a small window where you can put notes and with whom they spoke while they were here. Upon closing the window, it adds the entry to the log for that particular student. That all works great. Each student has their own log...all is good.
However, upon closing the window, the main form jumps back to the first student in the list. The entry is added to the correct student, but you must find the student from the combo box in order to see it. It is just annoying to have to do it and I would like to fix it. Can't figure out why it is doing it, though.
Any ideas? I would like it to add the entry to the student, and stay on their record.
View 5 Replies
View Related
Aug 17, 2007
Hello,
I have looked for ages for a solution to this and have not yet found one to suit me so here I am.
I am creating an Access 97 db in which I have a form (section1) which shows its records in datasheet format(results from a query). On this form is a button which opens another form (fileViewNewSec1) where the user can input the data for a new record. In the closeEvent() part of this form I have put:
=[Forms]![section1].[Requery]
This successfully refreshes the form and the new record can be found. However, there are many records in this database and it would be nice if once the form (section1) has been refreshed that it would focus on the newly created record. I am not sure if anyone can help me with this.
The records are sorted by "fileRef" and therefore the newly created record is not placed at the bottom of the records. They do however have an auto number primary key, I am not sure this will be useful but they you go.
Cheers
MArk
View 3 Replies
View Related
May 4, 2006
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 1 Replies
View Related
Oct 31, 2006
I have a sub form on a tabbed control. In the subform I have some calculated controls and I make them recalc when ever data in other fields change bu entering a recalc cmd in the update event of the required fields. That is all fine but the problem is that the forms focus moves to the first record and I need it to stay on the active record.
I have tried requery, recalc and refresh methods and they all do the same thing.
All I need to do is up date the calculated controls each time a field is updated and stay on the same form but it's eluding me.
Thanks
View 2 Replies
View Related
Dec 3, 2013
When I click the button "open new order" after that my form it's open with new id and it's ok, but when I push the key "F5" then Ms Access goes to another record (no new record). Why is that?
View 4 Replies
View Related
Apr 19, 2006
I'm requerying a form after an update.
I would like to have the form to be set to its current record when being requeried. How can I easily achieve this?
I've seen things about recordsets, I'm working with ADP (based on SQL Server) and don't know or all DAO events will help me.
View 3 Replies
View Related
Nov 29, 2004
Hi,
I have inherited a buggy database thas has a simple piece of code that won't behave the way I think it should!
The code should add a new record and have that new record appear on the list form (where the button On_Click event is called) making it available for user edditing. In 99% of cases though, the blank new record does not appear. The record is getting created, as i can see it in the table and have added a watch to the RecordCount property, it just doesn't display automatically. The existing workaround for users is to click a second button that invokes a simple one line function sub that performs only a Me.Requery. The user presses that up to two or three times before the new blank record finally appears.
One solution I read about, but hesitated in trying due to the unprofessional 'flash' that occurs, was to close the form and the re-open it...anyone experienced this or can comeup with a neat workaround?
The code
Private Sub CmdAddRule_Click()
Dim db As AO.Database, intnextseq As Integer
Dim TestThis As String
Set db = CurrentDb()
Dim rstDao As DAO.Recordset
Set rstDao = db.OpenRecordset("select * from stblAllocRules order by intRUSequence desc;", dbOpenDynaset)
If rstDao.RecordCount > 0 Then
intnextseq = rstDao!intRUSequence + 1
Else
intnextseq = 1
End If
With rstDao
.AddNew
!AutRU_id = 0
!intRUSequence = intnextseq
!bytAppendBack = 1
!bytUpdateBack = 0
!bytDeleteBack = 0
.Update
.Close
End With
Set rstDao = Nothing
Set db = Nothing
Me.Requery
Me.Repaint 'I have used this staement in addition to and instead of the Me.Requery to no effect
Me.Recordsource = Me.RecordSource 'I have used this statement also in addition to the above to and in isolation, to no effect.
End Sub
View 2 Replies
View Related
Nov 8, 2014
I'm trying to execute some lines of code right after deleting a record in a subform, with a right click on the row I want to delete (the idea is that if a record is deleted the other ones should be updated by module1.tblUpdateLatestDocuments).
I've tried putting this code into the events On Delete and After Del Confirm of the subform, none of that worked.
Code:
Option Compare Database
Private Sub Form_AfterDelConfirm(Status As Integer)
Call Module1.tblUpdateLatestDocuments
Me.Requery
End Sub
Private Sub Form_Delete(Cancel As Integer)
Call Module1.tblUpdateLatestDocuments
Me.Requery
End Sub
Aren't there any events that I could use right AFTER the record has been deleted?
View 10 Replies
View Related
Apr 14, 2015
I launch a 'CreateNewRecords' form from a 'MainForm' form. When the 'CreateNewRecords' form closes, the new record is visible on 'Main Form'.The underlying query is unsorted, so this code in the AfterUpdate event of 'CreateNewRecords' puts the cursor on the last record displayed on 'MainForm', which is the newly created record:
Forms!frmMainForm.Requery
With Forms!frmMainForm.RecordsetClone
.MoveLast
Forms!frmMainForm.Bookmark = .Bookmark
End With
However, I intend to sort the underlying query, which means that a newly created record may appear in the middle of the records displayed on 'MainForm'.
What I want to have happen is that after a new record is created by 'CreateNewRecords', the underlying query is requeried so that all current records are displayed on 'MainForm' but also that the cursor rests on the newly created record (rather than default to the first record).
View 7 Replies
View Related
Jul 30, 2013
I have a Main form that has button that loads a new Pop Up form for entering new data that will display in the Main form. When all the data is entered I click on a button that Saves the data and exits the form and then runs Re-query on the main form returning to the record that was current before the requery.
I have the following code:
Private Sub cmdSaveTradeAndExit_Click()
DoCmd.RunCommand acCmdSaveRecord 'Save the current record
DoCmd.Close 'Close the current form
Dim CrId As Integer
CrId = Forms!frmTransactionMainActivePopUp.CurrentRecord
Forms!frmTransactionMainActivePopUp.Requery
DoCmd.GoToRecord , Forms!frmTransactionMainActivePopUp, acGoTo, CrId
End Sub
But I am getting the following error:
Run-time error '2498':
An expression you entered is the wrong data type for one of the arguments
And the following is in yellow in the debug:
Code:
DoCmd.GoToRecord , Forms!frmTransactionMainActivePopUp, acGoTo, CrId
View 4 Replies
View Related
Apr 2, 2013
I have a form (frmAddManifest) with a subform (subfrmManifestTransporters).
When creating a new record, I can enter data into frmAddManifest, but the subform doesn't update to link with the record - I presume it's because the record from the main form hasn't been completed yet.
Is there a way to requery the form and/or subform so it stays on the record I was working on, and link the subform properly?
View 4 Replies
View Related
Jun 28, 2005
Hi guys,
Bit of a problem I'm hoping someone can help with. I have a telephone call logging system. The system is a tabbed form with each tab representing a person's Telephone Call Inbox. As new calls are entered into the database, they appear in each person's inbox until they are marked as dealt with.
This all works fine and there are no problems with the basics of it, but the problem I do have is that after I have split the database into a Back end and a Front end and distributed the Front ends to each user, when a new call is entered it doesn't appear in the persons inbox until they do a requery.
At first I sent the form to requery using the ontimer event, but the problem I have is that after every reqery the form goes back to the first record. A user on the office forum suggested I use the following code:
vCurrent = Me.Bookmark
Me.Requery
Me.Bookmark = vCurrent
But this gives me invalid bookmark errors, so I was then told to add
On Error Resume Next
But this stops the bookmark function working and instead the first record is loaded every 1 minute (as set by my timer).
Can anyone recommend a better way that actually works without invalid bookmark errors?
View 14 Replies
View Related
Aug 1, 2006
Hi! Please help!!!
I'm currently building a bookings database and have encountered an alert message that I cant seem to rectify -
"You cannot add or change a record because a related record is required in the table 'Booking Details'"
Basically - I have a 'Customer Database' form that is linked (via command button) to a 'Booking Details' form. Within 'Booking Details' I have 2 sub forms - 'Booking Quote' and 'Booking Payments'. Both subforms are linked to the 'Booking Details' form by the 'booking ref' field with RI.
I have no problem updating information in the 'Booking Quote' subform, but when I try to add information to 'Booking Payments' it states the above message.
Can anyone please advise as to how I can prevent this happening? I'm slowly losing my mind....!!!
Many thanks,
Stacey
View 10 Replies
View Related
Oct 22, 2012
Currently I keep getting this error: "You cannot add or change a record because a related record is required in table"..My current tables are this:
Primary Table with persons info:
Primary Key - Auto number generated
Name
Address
Email
Phone
I have 4 other tables with use check boxes.
ex:
Table 1 - Geographic locations visited
ID - Auto generated
USA
CANADA
ASIA
ECT...
Table 2 - Languages Spoken
ID - Auto generated
Spanish
Chinese
English
Table 3 - Skills
ID - Auto generated
Hunting
Dance
Singing
Weaving
Is this not a genuine one-to-one relationship table? I mean No two people would have had visited the same places and speak the same language no? I tried to create a one to one relationship with the primary key to the auto generated ID of the child tables but I'm sure that is not how you do it. Also when I try to save the check boxes in my form and I close it and come back it doesnt save and is blank again. Is it because my form gets its information from a query that takes all the information from all the tables.
how I can get this to work properly? Am i to make use of a foreign key? I've read a lot about it online and watched youtube videos but I dont see why I need it here in this case. Is there a way to set the IDs in the child tables to be the ones from the primary table? Or do I have to use a foreign key and manually input the primary ID into them?
Or would it be better to have all these child tables in the primary table and have one large table instead? I just didnt do that because one of them has like 20 checkboxes with cities and locations
View 12 Replies
View Related
Feb 22, 2015
I have created three tables, all of them are connected by one-to-one relationship by same field, as you can see in the screenshot. and at the same time I have created three forms for each table. then I brought two forms in one remaining form. so whenever I entered data in first form and click on the next tab in which another form exist, it gives me this error: "you cannot add or change a record because a related record is in table".
View 2 Replies
View Related
Aug 12, 2013
I have a problem with my access form, it said "You cannot add or change a record because a related record is required in table". I have attached the access file.
View 14 Replies
View Related
Jan 24, 2007
Can someone help me out by looking at my database I've been messing with it for 3 days and I can't figure it out.
I have 4 tables each are in a 1 to 1 relationship. From there I have a query that is for every field between the 4 tables. I then have a Tab Control form with 4 tabs and it uses the qryAll I have setup. when I enter on the form I can enter info on all 4 tabs but if I try to go to a different record I get the message "You can't add or change a record because a related record is required in tblScouts". I have tried so many different options. I had even posted yesterday trying to do the same thing but my tab controls had subforms on them. The_Doc_Man was helping me with that issue but I couldn't get that to go either.
Would anyone be willing to look at it for me and explain to me what I was doing wrong?
Rick
View 5 Replies
View Related
Aug 26, 2014
I have a split form that's like a list of pending tasks. The data source is a linked SharePoint 2013 list where users submit requests. The user takes the information from each record and performs an action. When it is done, the user presses a button and the task status changes from "Pending" to "Processed". The form record source is based on a query that finds only records with a status of "Pending" so when the user changes the status of the task, it is removed from the list. It works fine except when there is only one task left in the work list. If the user processes the last task, the form refreshes and it goes to a new empty record and I get an error message that says I must enter a value into one of the required fields. I tried making the field non-required but it just creates an empty record in the table.
View 3 Replies
View Related
Sep 3, 2013
I have two sub forms on a main form.Subform 1 displays information which comes from a query, filtered using two combo boxes on the same form.The active record on the data sheet has two field values output to hidden text boxes on the form.
These text boxes then provide the values used for the query displayed on the 2nd sub form.When I highlight a new record in subform 1, the text box values change, but the values in sub form 2 do no.Which would be the best method to use to get this to requery?
View 10 Replies
View Related
Jul 7, 2005
Ok, so I was wondering if there was a way that only certain text boxes show up in a form based on the record.
For example, in my products form there is hardwood cost, trim cost, fireplace cost, but only some products apply to each of those. How do I do it so that if the record refers to a fireplace all you see is the fireplace cost textbox and not the other two?
Is it better to have seperate tables? But they are all products so I thought they all go in one table.
Also, I would like a box named retail on the form which is basically the cost x2, I know how to build the expression but I would like it if it would multiply the hardwood cost by 2, if there is no hardwood cost then it chooses the trime cost, and so forth for the fireplace cost. Also, sometimes there is a retail price that does not need any calculations at all..how do I incorporate this?
Is any of this possible?
thanks very much in advance for any help.
View 9 Replies
View Related
Sep 22, 2005
There are two main methods of navigating around the top-level form on the database I am currently working on.
One is by a drop down menu listing GP practice names - so you can scroll down to "Dr Watson" for example and then all the subforms display the correct info.
The second way is by clicking on "Previous Surgery" or "Next Surgery". This is obviously a lot easier for data entry than having to use the drop down menu each and every time!
However there is a problem, in that when one uses the second method - the highlighted value in the combo box does not change! You can literally navigate through hundreds of surgeries, and though the address changes in the subforms etc "Dr Watson" is still proudly displayed in the combo box.
So we have a situation where it would be pretty easy for someone entering data to think they were on the correct record when in fact they were not, leading to errors in input.
Is there a way to force the combo box to "refresh" to the same value as the current record, effectively displaying the title?
Thanks
Gazz
View 2 Replies
View Related
