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 Replies
ADVERTISEMENT
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
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
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
Mar 21, 2013
I have built several combo boxes in other DB's but haven't had this happen before. I am adapting a template DB to work for me. The form is continuous and I added a field [CategoryID], I want the [ProductID] to filter based on CategoryID choice. Pretty straight forward and it works, but the [ProductID] field will not hold the text value. It's a number field to hold the ID value, and bound column is 1(Select query is ID,ProductName,StandardCost) with ID and StandardCost column widths set to 0cm. I can choose CategoryID, the ProductID combo filters correctly, then when I proceed to the next record, the ProductID goes blank.
View 9 Replies
View Related
Jan 29, 2015
When I try to transfer (ctrl c + v) data from a table in Excel to a table in Access it loses format.
Exemple: $ 1.000,00 (Excel) become 1000 (Access).
I need to keep the format to make sure that the code works properly.
View 9 Replies
View Related
Oct 27, 2005
Hi all - first post for me, a long-time user of the site though!
I have a form (frmEnquiryFor) where so far everything works almost as I want it! I have a text box (txtCompanyName) and below it a list box (lstCustomer). What I want to happen is that as a user types into the textbox, the listbox below updates on each keystroke. At the moment, this doesn't quite happen: what happens currently is that if the user types into the listbox and then hits return, the listbox updates as it should.
The listbox's rowsource is based on the SQL statement:
"SELECT [qryCustomerLookup].[CustomerName], [qryCustomerLookup].[CustomerCode] FROM qryCustomerLookup; "
The query underlying this, qryCustomerLookup, works as it should. What it does is if the user has typed in nothing, it returns all the Customer names and customer codes. If the user types in "a", it returns all the customer names and codes of customers beginning with "a". If the user types in "ab", it returns them all from customers beginning with "ab". (In case you need it, the SQL for this query is as follows:
"SELECT tblCustomers.CustomerName, tblCustomers.CustomerCode
FROM tblCustomers
WHERE (((tblCustomers.CustomerName) Like [Forms]![frmEnquiryFor]![txtCompanyName] & "*")) OR (((([tblCustomers].[CustomerName]) Like [Forms]![frmEnquiryFor]![txtCompanyName] & "*") Is Null));"
The final bit of code is just a bit of VBA - "Me!lstCustomer.requery" - that I have tried putting in various places. I have put this code in the "after update" event of txtCompanyName, and this comes close to working. What this does is update the listbox correctly, but only after the user hits the enter key after entering a letter or two. What I want is to find a way to make the listbox update after each keypress.
I've tried putting this requery code in the keypress event, the keyup event, the onchange event, all to no avail. I think the problem may lie in the timing of the event firing, but I'm not sure.
So - any ideas?!?! :confused:
View 3 Replies
View Related
Aug 24, 2006
I have a form that has names with a subform with information. The contacts are in a list box and the subform (in datasheet view) shows the phone number/email/etc.. of the selected contact person. I am using the form as a quick look up of a persons information. Before changing it to a list box, it was previously a combo box, and everything worked fine. i was able to requery the subform and the cooresponding info for the person would come up. However, its now a list box and i am having problems with the code to make this happen. I have a different button sending the names selected from the list box to a report...and that works fine, but i am obvisouly missing something to make it work with the subfrm requery. Below is the code i got so far. Any help would greatly be appreciated. its driving me nuts!
Private Sub QuickLookup_Click()
Dim varItem As Variant
Dim strWhere As String
strWhere = "[memberID] = "
For Each varItem In Me.MemberID.ItemsSelected
strWhere = strWhere & Me.MemberID.ItemData(varItem) & " OR [memberID] = "
Next varItem
strWhere = Left(strWhere, Len(strWhere) - 17)
DoCmd.Requery "subfrmqryindividual"
View 2 Replies
View Related
Nov 13, 2005
can any1 tell me y im missing in my db?
look at Monthstock form...
plz tell me step -by-step how to do it..plz..im new
View 7 Replies
View Related
Dec 12, 2006
What line of code do I need to requery a listbox on Form1 when I close Form2?
I'm pretty sure I need to put it in the OnClose Event of Form2, but I don't know how to reference the Listbox on Form1.
Thanks
ScrmingWhisprs
View 4 Replies
View Related
Nov 9, 2006
I have a listbox with 3 columns: Date, Shop, Amount
I want to format the amount and have it right aligned.
Can anyone tell me how to do that?
View 14 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
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
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
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
Jun 15, 2006
I have a multicolumn listbox on my form. It works, but the second column is an amount, and I would like that column to be right-adjusted. Is there a way to do that?
Thanks,
David
View 2 Replies
View Related
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
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
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
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
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
Jun 26, 2013
I have a subform in a tab within a mainform. Within the tab there is a button to open a form to delete/add data to the subform. So upon closing this pop up form I want to update the subform to show what has been deleted or added, I have tried the various things to requery the subform but none of it seems to work, here is what I have tried;
Code:
Forms!frm_Capex_Submission!frm_CapexSignatures.Requery
I assumed this would work but evidently not...
I have also tried;
Code:
Forms!frm_Capex_Submission!frm_CapexSignatures.Form.Requery
and;
Code:
Forms!frm_Capex_Submission!frm_CapexSignatures.refresh
View 2 Replies
View Related
Nov 5, 2013
I want a command button to "Requery" a combobox. the combobox uses a query to determine the records listed (it lists incomplete records). after completing the record, i'd like to hit a command button that will "requery" the combobox so that the recently completed record is no longer listed.
I tried this:
me.nameofcombobox.requery
But the completed record was still listed.
View 3 Replies
View Related
Aug 15, 2005
I have a form with a number of controls with code behind - this works as required.
However, recently I decided to use a tabbed control to hold the controls and simply cut/pasted these control onto one of the pages of the tab control. The events now no longer fire as they did and I have no functionality on the form - the code still exists in the design of the form though..... any ideas?
View 1 Replies
View Related
Aug 26, 2014
I have a DCount code that traps duplicate records in the database and gives a message box. (the dcount is based on 2 criteria that are a compound index) I would like to have the message box ask if the user wants to go to the duplicate record. How would I write the code that when the user clicks yes, the form requeries and opens to the record that matches the criteria? Is there a way to add criteria to a requery?
View 7 Replies
View Related
Mar 21, 2014
I have a form frm_GlobalSettings with a combobox cmbDescription that finds a record based on the value selected. The row source type for cmbDescription is Table/query, and the row source is a select statement on the form's underlying table.
I want the user to be able to use cmbDescription to add a new record to the form's underlying table. I currently have
Code:
Private Sub cmbDescription_NotInList(NewData As String, Response As Integer)
Dim strTmp As String
'Get confirmation that this is not just a spelling error.
10 On Error GoTo cmbDescription_NotInList_Error
20 strTmp = "Add '" & NewData & "' as a new global setting?"
[code]....
but when a new value is entered this errors on line 70 with "runtime errror 2118 - you must save the current field before you run the requery action".I've tried various ways around this but can't get it to work.
View 2 Replies
View Related
