Add Record Pop-Up - Combo Box Requery

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 Replies


ADVERTISEMENT

Forms :: Requery Moves The Record Back To 1st Record

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

SubForm Combo Box Requery

Jun 24, 2005

I am a newbie to access and am having some difficulty with a subform. I have searched the forum, but could not find an exact match to my problem.

The following is a simplified version of my problem:

Tables: TSerialNumber, TLocation, TSubLocation(Location and SubLocation Fields)

Form: ServiceForm
SubForm: ServiceSubForm
Form and SubForm linked by SerialNumber

In the SubForm: I have 2 ComboBoxes: Location, SubLocation
Location - Row Source is the Table TLocation
SubLocation - Row Source is a Query of SubLocation relating to the Location

I need the SubLocation field to be requeried OnChange from the Location ComboBox.

If I use the subform as a form I can get this to work by using a macro to requery the SubLocation, but when I try to use it as a subform I get an ApplyFilter Error.

Thanks in advance for any help.

View 3 Replies View Related

Requery Combo Box In Subfrom

Aug 8, 2006

This is a bit of a weird one.

I have a combo box in a subform that has a hyperlink to open another form which in the on click event of the hyperlinked form saves the new information and should then update the combo box in the original subform.

i figured i would use a requery to accomplish this. Using the syntax

Forms!Student!sfrmDDS_Classes_add.form.requery

This doesn't error out, but it defenitely doesn't update the combo box within the subform.

Any suggestions

View 3 Replies View Related

Subform Combo Box Requery

Dec 28, 2006

I have a subform that contains two combo boxes, 'cboPrimary_Source' and 'cboSecondary_Source'. When you select the primary source e.g. 'Direct Mail' a number of related options appear for selection in the secondary source combo box e.g. Letter, Email, Fax. When you open the subform only, this works just fine using the requery.

However, when you open the main form 'Prospects' where the subform sits it does not work. The primary source combo can be selected, but when you select the secondary source combo an error message appears 'Enter parameter value: Forms!Subdatasheet_Subform!cboPrimary_Source'.

The code used on the Subdatasheet_Subform is as follows:

Option Compare Database

Private Sub cboPrimary_Source_AfterUpdate()
Me!CBOSecondary_Source.Requery
Me!CBOSecondary_Source.SetFocus
End Sub

Private Sub Form_AfterUpdate()
Me!CBOSecondary_Source.Requery
End Sub

Private Sub Form_Current()
Me!CBOSecondary_Source.Requery
End Sub

Any suggestions would be gratefully received!

View 8 Replies View Related

Requery Value Of Combo To Listbox

Oct 3, 2006

Hello,

I have a form with a text box, a combo and a listbox.

The textbox stores the name of the user.

The combobox gets its data from a query.

I would like that when I open my form, by default the list box will show all the records of a table named Master and then, I would use the combo to requery the list by selecting another user. All records under that criteria, will have to show up in the list box.

Any help? Thank you.

View 1 Replies View Related

Modules & VBA :: Requery Once A Combo Box Selected

Jul 15, 2013

I have a form which contains two sub forms. In the first subform I have 4 fields of Cut#, Size, Quantity and Style. Once the cut# is selected, a vba code runs and fill in the rest of fields. In second sub form I have a field of Fabric# (Combo Box) which should be restricted to the Style value on the first sub form. I have a query which contains the Fabric# and Corresponding style and I try to write a vba code to requery once Style value changes.

View 14 Replies View Related

Forms :: Combo Box Recordsource Requery

Jan 7, 2014

I have a recordclone combo box for navigating between 240 company records, so the form will go to the correct record on updating the combo. Instead of clicking on the down button and scrolling through the list, I would like to enter letters into the box and update the record source automatically as I enter them.

I'm not sure what events to use since there appears to be very subtle differences between them (eg between 'on dirty', 'on change' and 'before update').So when I type in 'T', only those companies that start with 'T' are viewed in the combo box.I have discovered the VBA 'dropdown' command which would be useful for the user.

Code:

company.dropdown
Table_Company
Company_ID: autonumber
Company_name: text

The name of the form is "form1" and the combo box name is "company".The record source for the combo box is "query_company". In the form the ID field is hidden and the company name is not hidden.The criteria I have for the combo would be something like this.

Code:

SELECT Table_Company.Company_ID, Table_Company.Company_name
FROM Table_Company
WHERE (((Table_Company.Company_name) Like [forms!]![form1]![company] & "*"));

View 3 Replies View Related

Modules & VBA :: Default Combo Box Value After Requery

Oct 2, 2013

I have this code:
If Me.OpenArgs = "frmAddChecklist" Then
Set ctlCombo = Forms!frmAddChecklist!cbDEPARTMENT
ctlCombo.Requery
End if

And this code is done after I added new value to table, and now I want that new value to be used as default one, how can this be done?

View 1 Replies View Related

Combo Box Requery In Tabbed Form Mode

Nov 17, 2011

I have an application where I have developed a tabbed user input form with several tab pages to populate several tables with data using the master/child linking philosophy. There is one subform in particular that has two combo box fields that are related. I have them both set up to display value choices based on an undelying query. I want to restrict the scond box's choices based on the choice made in the first box and have written the following code to be applied after the first combo box is updated.

Private Sub RteGroup_AfterUpdate()
Form_Routing.OpType = Null
Form_Routing.OpType.Requery
Form_Routing.OpType = Form_Routing.OpType.ItemData(0)
End Sub

This effectively limits my second combo box choices based on the selection of the first box. It works fine if I run the subform individually but if I run it as part of the master form in tabbed mode I get an "Enter paramter value" dialog box asking for the value of the first combo box. it appears the requery is not functioning under the tabbed mode.

View 9 Replies View Related

Modules & VBA :: Requery Listbox With Value From Combo Box Loses Format

Mar 19, 2015

I have a listbox on a form with several fields, 3 of which are currency fields.When I filter the listbox using afterupdate from a combobox on the same form, the listbox loses the currency format on the fields.

Code:
cboprop_AfterUpdate()
Dim ListFilter As String
ListFilter = "SELECT [qry_inv_form].[inv_no], [qry_inv_form].[prop_ref], [qry_inv_form].[inv_date2], [qry_inv_form].[inv_desc_type], [qry_inv_form].[inv_net], [qry_inv_form].[inv_vat], [qry_inv_form].[inv_total], [qry_inv_form].[year_month], [qry_inv_form].[month_text] " & _
"FROM qry_inv_form " & _
"WHERE [qry_inv_form].[prop_ref] = '" & Me.cboprop & "'"
Me.inv.RowSource = ListFilter
Me.inv.requery

I tried wrapping the fields in Format(fieldname, "Currency") but that came back with a syntax error.

View 2 Replies View Related

Forms :: Requery Not Working In A Form With Combo Boxes

Jan 30, 2015

New to access...just build a form, in which there are combo boxes....cascading of boxes was done.

Now the problem is I want afterupdate function to get activated when user changes the value of one combo....for which I have created a code in code builder... see the code... I think I am making a mistake in writing the code to requery...

View 5 Replies View Related

Forms :: Requery List Box In The Form After Combo Box Updated

Jul 11, 2015

How do I requery the list box in the form after the combobox is updated? the name of the combo box is txtFilterClassName.

View 3 Replies View Related

Requery And Return To The Same Record

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

Requery Subform - Not Same Record

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

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 1 Replies View Related

Forms :: Cascading Combo Boxes Don't Show Control Source Value Until Requery

Dec 31, 2013

I have a set of cascading combo boxes. When a value is selected in Cat 1 ID, it narrows down the selections in Cat 2 ID which narrows down the selections in Cat 3 ID.

However, the values don't show up for columns Cat 2 ID or Cat 3 ID until I enter the row. Then I have it set to requery the fields because the underlying data isn't populated. But then when I leave the row, the values go away.

Why doesn't it show the underlying value from the table? Each column is bound to a field in a table - I would assume that the recorded value would show up. Did I miss something easy?

I've attached a picture of exactly what I mean.

View 2 Replies View Related

Finding Newest Record After Requery

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

Moving To Current Record After A Requery

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

Requery Moves Focus To First Record

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

General :: MS Access Goes To Another Record After Requery

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

Requery Form And Goto Its Current Record

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

Requery Failing To Add New Record To Form List

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

Forms :: Requery Subform After Deleting A Record

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

Forms :: How To Put Cursor On Newly Created Record After Requery

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

Forms :: Return To Previous Current Record After Requery

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







Copyrights 2005-15 www.BigResource.com, All rights reserved