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 RepliesIs it possible to requery a subform 'in the background' before repainting / refreshing the screen?
I have a subform which has multiple controls, some of which have conditional formatting applied to them.
The problem is, whenever I open the tab that contains the subform, there is a momentary 'flicker' as the CF calculates and highlights the appropriate controls. I have a requery command in the change event of the tab control (rather than recalculate the entire form).
Code:
Private Sub tabAllFunctions_Change()
Select Case Me.tabAllFunctions.Value
Case 0 ' Page 0
Me.sfmSubForm0.Requery
Case 1 ' Page 1
Me.sfmSubForm1.Requery
Case 2 ' Page 2
Me.sfmSubForm2.Requery
End Select
End Sub
It would be neater if the subform waited until all of the necessary requerying / recalculating were done first before showing the final result. Admittedly this would introduce a short delay (we are talking a fraction of a second here) but it would make the output appear more stable rather than seeing controls flick between colours while the recalculation takes place. For me, this would be preferable.
How I can 'pause' the screen update while the Requery takes place to remove the momentary flicker?
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 RelatedHi - 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.
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.
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.
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
Im trying to sum a value within subform able before I even start I get ####Error as I have put in the control source the rule to create the sum
Code:
=Sum([TempTable].[hours])
I then have a function when another value is added it requires the text box which holds the sum value.
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
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
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
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.
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
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?
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).
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
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?
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?
Hi,
I'm trying to requery a subform within a tab after I select from a combo box in the main form. Can anyone help?
Or just to simulate the "refresh" button click within a macro?
thanks
Jeff
This seems pretty simple but I would like to select a combo box and after the user selects the combo box (Which Is a City) I want to use the underlining query of the form to run because I am using the <>False Criteria for a command of;
IIf([Forms]![frm_Deals]![CheckCity]=True,[City] Is Not Null Or [City] Is Null,[City]=[Forms]![frm_Deals]![SelectCity].[Value])
I may have other combo boxs with other information so that is why I want to run the underlying query. I have used the After Update command of;
Me.RecordsetClone.FindFirst "[XXXX] = '" & Me!FindXXXX & "'"
But I want to requery the query under the form.
I have a sales call tracking program.a list of available numbers are displayed to multiple users running the a user clicks on a record to get a call screen displayed with client info they then call the client and try for a sale.
the problem exists that two users can click on the same list record and open up the same call screen - they then both try to call the customer manually on the phone. one agent will get through the other will get a busy signal and record the call as a Busy Call. When the user with the customer is done he tries to record a Sale or No Sale and is met with an error message saying the record has been modified and they cannot save changes!!
I need a way to get access to immediately lock the record when it is clicked so only one person can open the call details screen? What is the best way to lock the records?
I have a split database with several attachment fields that work fine.
When I try to create a new attachment field in the BE, upon opening the FE, I get the following error: "The search key was not found in any record."
I have tried several ways: first create the attachment entry in the BE, then I can't open the FE. Create it first in the FE, leave it unbound and then create it in the BE - same error.
How to create messagebox to alert when I open form that have a new record added , and if it can count No of new record ? could it be possible?
View 3 Replies View RelatedI want to return the number of record with the SQL search in MS Access. I am using the following code, the expected result will be a value. But somehow, it does not give any result.
StrSQL = "SELECT COUNT(*) FROM table1 WHERE [Condition]='A' "
How do I return the count value with the search?
I would like to get a third party to edit a database and record those edits by changing the colour of the font. I know this can be done in layout mode, but it changes the font in all records, I only wish to change the font colour in one record, how I can do this, without changing all the records in the database.
View 3 Replies View RelatedUsing Access and Outlook 2010. I have a database that tracks Contracts for our University. One of the fields it tracks is Expiration Date.
Is there a way to have Access prompt Outlook to send an email to my boss when the contract is a month or within a month from expiring?I'm assuming when the database is opened it would have to run a query for expiration dates expiring within one month of today's date (which i know how to do).
I just don't know if it's possible to generate an email or some kind of message with those records/contract numbers displayed? I should also note my boss is the only one who uses this database so even if the email notice isn't possible, is there a way to show a message of what contracts are expiring soon when he opens the DB?