I have a form that has a combobox and a field text. If i select any value in combobox i want to update the txtBox but not with the ID of the selection. I need to get the description of the combobox selection which is essentially the Column 2.
View RepliesIt's an unusual request, I know, but it's necessary for my purpose.
I have a form, frmMain that I use to update a number of tables. On it is a number of combo boxs, comProduct, comBrand, comSize, comPrice and comQuantity. comProduct opens a new form, frmNew if the users input is not already in the table. On frmNew there are again combo boxes comProduct, comBrand and comSize. This form adds the record to the table.
So, if I input a new value - not already in the database - for product in the comProduct combo box, the form frmNew appears and I can proceed to define the product, brand and size of the new record. The form then closes and reopens frmMain and tabs across to comQuantity (skipping the fields I've already defined in frmNew).
Now if you can wrap your head around that, here is the problem: Because I've already entered the values for product, brand and size in frmNew, I want them to automatically appear in the appropriate combo boxs on the frmMain form. I've been using the following code (executed from frmNew, so Me = frmNew):
Code:[Forms]![frmMain].[comProduct] = Me.comProduct[Forms]![frmMain].[comSize] = Me.comSize[Forms]![frmMain].[comBrand] = Me.comBrand
It works, but I've since discovered it creates a new record in the table so I end up with two duplicate records (one when I submit frmNew and another when the values are automatically input into frmMain.)
Now, my question: How can I display these values in frmMain without having them create the second record? I was thinking of using a SELECT query somehow, but I don't know where to begin. I also considered using labels instead of comb boxes, but I'll need to be able to edit them if the record already exists.
I am trying to use a combobox called Manufacturer to select which table the combobox called Model gets it's rowsource from using the code below.
Code:
Private Sub Manufacturer_AfterUpdate()
If (Me.Manufacturer.Value = "Siemens") Then
Me.Model.RowSourceType = "Table/Query"
Me.Model.Recordset = "SeimensTable"
Me.Model.RowSource = "SELECT Model FROM SeimensTable"
Else
If (Me.Manufacturer.Value = "Samsung") Then
Me.Model.RowSourceType = "Table/Query"
Me.Model.Recordset = "SamsungTable"
Me.Model.RowSource = "SELECT Model FROM SamsungTable"
End If
End If
End Sub
But when I run the form and select Manufacturer. Combobox Model remains empty. tell me what I'm doing wrong?
I have a combo box in a form that uses a query to access values from a different table, and dispaly them as choices. This works great. The first problem I encountered was if the value that was needed to be entered was not in the list, then there should be an option to add it. I found a couple good examples by using the NotInList definition to achieve this. And it works, when a new value is added, a message box comes up asking to enter the info in the database and the needed form then pops up. But when I close the form, the value is not in the combobox. Now I also researched this for a while, and found out about the function ComboBox.Requery, but I don't know where to put it to work.
Here's a more code definition of what I'm trying to do/have:
Private Sub Building_NotInList(Newdata As String, Response As Integer)
If MsgBox("""" & Newdata & """ is not in the customer list. Add it?", 33) <> 1 Then
Response = DATA_ERRCONTINUE
Exit Sub
End If
DoCmd.OpenForm "addbldgform", , , , 1 'Data Entry Mode
Response = DATA_ERRCONTINUE
End Sub
I was thinking about putting the ComboBox.Requery inside the AfterUpdate of a field in the addbldgform, so that when a new entry was added it would update by writing this in the AfterUpdate:
Form!buildingPractice!BuildingID.Requery
but that gives me an error saying that the field buildingPractice can't be found, when it is really a form with the value BuildingID (the name of the combobox that needs to be updated)
Sorry about the long definition, I just wanted to be clear, hope someone can help out.
- kevygee
I have a form called NewRequisitions that had a combobox in which the users select the Supplier (name, address, shipping details). I created a button to open a subform called NewSupplier so that they can add a new supplier to the Supplier table as needed. My code works okay for the first "NewSupplier" entry - the table and combobox are both updated and the new supplier is available for selection in the dropdown list. However, when there is a 2nd record that needs a NewSupplier added the combobox list doesn't get refreshed. The record is added into the table okay but you can only view it if the NewRequisitions (main) form is closed and then reopened.
Here is the code in my subform:
Private Sub Form_Close()
If CurrentProject.AllForms(NewRequisitions).IsLoaded Then
'save the record if there have been any changes
If Me.Dirty Then Me.Dirty = False
'requery combobox on other form
Forms!NewRequisitions!ComboSupplier.ReQuery
'yield to other events - and put the new list into use
DoEvents
End If
End Sub
-----------------------------------
Any assistance would be greatly appreciated!!!
I currently have a table with two important fields (lets call them X and Y) currently I have them set to ComboBox where they reference two look up tables. I then have a form that allows for user input. Each of the two fields work with drop down lists. What I want to to is filter the Y Field based on the selection in Field X.
I wrote a query that is set to the Second combo box. WHen the user selects the drop down it runs the query and uses the value in the first to narrow the selection.
[forms]![DATA_ENTRY]![HOUSE_NUMBER]
THis works great but only for the first go through. After that it seems that I have to have code on one of the events. Basically any time the user hits the drop down I want to requery.
Does anyone have any code examples that I can reference that will show me how to repopulate that second combobox based on the selection from the first combobox? EVERY TIME I CLICK THE SECOND DROP DOWN BOX ....I would imagine that I have write the code on one of its events...
Does that make sense?
Any help would be greatly appreciated.
Thanks in Advance
Hello, I ran into another programming road block.
I currently have a combo box that is bound to a table with 2 fields and has 4 different options/records in it, with an after update procedure, that has different calculations for each record. I want to add a Dlookup command that will display
the record that is chosen and it will need to be placed in the
after update procedure of the combo box also. Is this possible?
Code that is currently in the after update procedure of the combo box.
Dim prp As Property, ctl As Control
Set prp = Me!cboRidge.Properties("ListIndex")
Set ctl = Me.txtRidgetot
If prp = 0 Then
ctl = ([txtridge]*100/30)+0.4
ElseIf prp = 1 Then
ctl = ([txtridge]*100/45)+0.4
ElseIf prp = 2 Then
ctl = ([txtridge]*100/30)+0.4
Else
ctl = ([txtridge]*100/30)+0.4
End If
Set ctl = Nothing
Set prp = Nothing
Code I need to add:
Me.DLookcbotile.Requery
Thanks-- any help will be very appreciated.
plz help me how to do it...
plz look at tables
i have "monthyear" & "partno/details and month" table
i cant open all part that have same month at table monthyear...
but i can open n view customer that bought same part at partno/details...
then i cant create form from table Monthyear..i want to create form as form "test"
plz help me...
am i need a coding?
can u show me the coding?
i'm new...plz..
ahh. cant post my DB....my db size to big...700kb after zip
cant i post screenshot? :confused:
I have a form with a Combobox that gets it's data from the table 'countries' and stores information in accounts.
Now I would need data entered in the cobobox to be checked against the table 'countries' if the data already exists...
if a.) yes - then all good
if b.) no - then new entry should be added to table and combobox (after confirmation)
The company's register of projects we select the customer from a combo box that is related to the customer table. However, the way this works now, the individual may choose another customer by mistake, and if this is not corrected at the time the project will be listed with the wrong customer.
I would like to have a feature that when you select a customer receive a message if you are sure this is the right customer and if you answer yes then the customers name is being locked to this project ID.
I have a form that has several fields that a user will enter data. When finished, the data is inserted into a table. Works great. What I want to do now is look at one of the fields entered and if it is equal to a certain value, I want to disable one of the other text boxes (fields) on the form AND pop a screen that will allow the user to enter a list of names that will update a different table.
So, if user inputs a directory name GENERIC, the txtScriptName box needs to be disabled/greyed-out and I need a form to pop so the user can put in a list of script names. If any other directory name is entered, the txtScriptName box needs to remain editable. (For directories equal to GENERIC, there will be multiple script names instead of just one)
I've been successful at getting the form to pop if GENERIC is entered but can't figure out how to grey-out the txtScriptName field. I tried using Me.txtScriptName Locked in the Before Update of the preceding field but it didn't work - could still update the txtScriptName field.
Any other sugestions?
Thanks!
Mary
I have a combobox on a form which is populated from a field to present a list for the user to choose from.
I have limit list set to 'N'
When the user types something in the combobox that is not in the field in the table then I want to prompt for a password. Effectively password protecting the update of the field from a combobox.
If the password is correct the text is written to the field in the table and if not then not.
Help ?
I have a combo box which gets its values from sql server using a query which is called "get_query_reason", which works fine. Now I want to update combo box values based on a user selection, st string. Have written the code, but does not work:
Dim qDef As QueryDef
Dim Query As String
Dim st As String
Dim rs As Recordset
st = "SOV"
Set qDef = CurrentDb.QueryDefs("get_query_reason")
[Code] ....
I have been spending all my today to fill a combobox dynamically, but have not been able yet.
I have a combobox and a pass-through query in access, which is working fine and fill the details into the combobox via data source. Now what I am planning to do is to update the combobox source as soon as value in a text box changes.
Here is the code I am using, but it is not working:
Dim rs As Recordset
Dim qDef As QueryDef
Set qDef = CurrentDb.QueryDefs("get_data")
qDef.SQL = "SELECT Initial + ' (' + Name + ')' uws FROM EM.dbo.UW" _
& " WHERE lob = '" & addSingleQuotation(Me.CMB_LOB.Value) & "'"
Me.cmbUM.RowSource = qDef.SQL
Me.cmbUM.Requery
I also used Recordset, but did not work:
Set rs = CurrentDb.OpenRecordset("get_data")
Me.cmbUM.RowSource = rs!uws
Hello,
I'm trying to make a report query work but with no succes so far. Can anyone please help me?
I've got a multiselect listbox and a txtbox (txtCursisten) where the results from the multiselect listbox appear.
I want to get the results out of a table (tblAbsentie) where I can set a start and end date AND use the people selected in the txtBox. So far I've got a NOT working query, a report based on the query and a button on my form.
The VB code behind the "show report" button is this:
Private Sub cmdAbsentiePerCursistPerPeriode_Click()
On Error GoTo Err_cmdAbsentiePerCursistPerPeriode_Click
Dim db As Database
Dim Q As QueryDef
Dim sql As String
If IsNull("Me.txtCursisten") Or Me.txtCursisten = "" Then
Exit Sub
Else
Set db = currentdb()
Set Q = db.QueryDefs("qryAbsentiePerCursistPerPeriode")
Q.sql = "SELECT tblCursist.Naam, tblAbsentie.Datum, tblAbsentie.Lesuur, tblAbsentie.AantalLesuren, tblAbsentie.Deelkwalificatie, tblAbsentie.Docent, tblAbsentie.Gemotiveerd, tblAbsentie.Reden, tblAbsentie.Status, qryCountLesuren.SumOfAantalLesuren" & _
"FROM (tblCursist INNER JOIN qryCountLesuren ON tblCursist.OVnr=qryCountLesuren.OVnr) INNER JOIN tblAbsentie ON tblCursist.OVnr=tblAbsentie.OVnr" & _
"WHERE (((tblAbsentie.Datum) Between [Voer begindatum in] And [Voer einddatum in]) AND ((tblAbsentie.OVnr) In ("Me!txtCursisten"))); "
Q.Close
DoCmd.OpenReport "rptAbsentiePerCursistPerPeriode", acPreview
End If
On Error GoTo 0
Exit Sub
Exit_cmdAbsentiePerCursistPerPeriode_Click:
Exit Sub
Err_cmdAbsentiePerCursistPerPeriode_Click:
MsgBox "Selecteer klas en cursist(en)."
Resume Exit_cmdAbsentiePerCursistPerPeriode_Click
End Sub
I get a syntax error when pressing the button. Please help...
SeBasTiaan
Hi there
Can someone give me a basic example.
I have a form that contains a textbox and a listbox. The listboxs rowsource is tblSPNZ.
The table has 2 fields 1 is and autonumber and the other is where I want the results to go.
How do I make a command button populate the table? Avoiding putting any results into the autonumber field?
Thank you for your help.
I have a continuous subform of 'static data' whose record source is a SELECT query across multiple linked tables.
Most of the fields are locked and purely there for information purposes but I need to be able to allow end users to change one particular field in each record if they need to - choosing a value from a predetermined list (i.e. one of the tables)
Usually, when I need to do this, I add a command button to the subform and use that to open a separate pop-up form specific to that record, from which the user can make whatever changes they like and then update the record. So in this scenario, a simple unbound combobox linked to that table, on that separate form, would work quite easily.
But I would prefer if users could make their changes directly from the subform without (yet another) pop-up form required each time, to make managing these records more fluid and less time-consuming.
So my thought was to use a bound textbox in the continuous subform (to take the existing value for each record), hide it and overlap it with a visible unbound combobox whose row source is the table of available options and whose default value is driven by the hidden bound textbox. And then to use the AfterUpdate event of the combobox to run the UPDATE query on the record in question.
However, while the combobox is getting the correct default value and the correct list items, I can't make a selection from it. Now I am aware there are 'issues' with using a combobox in a continuous subform but I was hoping I could circumvent them by not binding it.
I have a form, on the form a textbox. Through the expression builder I have assigned one value to the control source of that textbox from a query. The query holds three values, all three of which I would like to use in a separate textbox. When I open the form the textbox just displays #Name. I'm guessing since nothing is happening because the query isn't run by command yet. I've used code like this:
Private Sub AppointmentID_Click()
Forms![ClientsMain]![TechViewSummarySubForm].Form.Requery
Forms![ClientsMain]![TechViewSummarySubForm].Form.Refresh
End Sub
to get a query to open, run etc. But that one is located in a subform which has the query as a control source. How does that work in the case I have now? Can I use the DoCmd function to run the query and get a value from it for the specific textbox? Or can't I use a (segment of a) query as the controlsource of a textbox at all?
I'm asking this since now I calculate and store certain totals by use of a textbox with =Sum() as control source in the footer of a form, which values I then assign to some fields in a table.
But I would also like to learn the right way, using queries, maybe for a later version of my DB when my skills have improved. I've been struggling with this now for a bit, but without succes. So that's why I wonder if I can assign a query or more specific one value of a couple of values in a query to a textbox in a form which has a totally different control source itself.
Hi all,
I have a select query that pulls in two date values from a textbox on a form.
SELECT DISTINCT Table01.SRV_NAME, Table01.IMP_CUTOFF_DT
FROM Table02 INNER JOIN Table01 ON Table02.Servicer = Table01.SRV_NAME
WHERE (((Table01.IMP_CUTOFF_DT)=[forms]![frmreport].[txtsqldate].[value]))
ORDER BY Table01.SRV_NAME;
However when I run it I get the following error message:"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may...." but when I copy and paste the value of that txbox from the form into the query it works fine.
Can anyone see where the problem lies? I can't work it out.
Thanks in advance,
Mitch....
Here's a bit of a pickle I've been trying to wrap my noodle around.
Anyone have an idea what route to pursue (unbound form, or afterupdate, etc?) to force a validation rule on the number of characters in a text box based on the value of combobox in the same form.
Example:
If cboBox1 = 840, then len(txtBox2) = 6
But if cboBox1 = 820, then len(txtBox2) = 3
And so forth?
Thanks,
~Chad
friends, a small question.
I have placed a txtbox on my form and via a cmdbutton a can copy data to another text box. Is there a way I can replace data? Will appreciate any help.
I am currently using the column history to log the history of inputs into a memo field.
But i now need to swap how my database runs and now require a combo box to have the same history function, as this is for a status updates and i require users to only input certain status's.
I no that I cant use the columnhistory command with a combo box.
When selecting a status from the combo box it automatically update the memo field (which will be hidden on the form) so the column history function works.
Hello,
In a form I have "txtBoxA". I would like store the number of records from table "A" based on a specific field into "txtBoxA".
Ex:
Table 'A' fields: QuoteID, Date, GenerateOrder
What would be the exact syntax for this??
The following code is not working
Me.txtBoxA.Value = DoCmd.RunSQL ("SELECT COUNT(GenerateOrder) FROM A")
Thanks
Having had a pretty good look around, I think I must have missed something...
I just want to try and make a Forms single Address textbox = multiple table fields values:
Add1
Add2
Add3
PostCode
Cheers,
Pailin
I have a table tblDateGroups, with two field, [Group] (like This Week, Next Week, Last Month, etc) and [Code] that holds the code for criteria for each group in a query. I know the code is fine (like next week = Between Date()-Weekday(Date())+8 and Date()-Weekday(Date())+14 ) because I tested before putting in the table.
On my form I have a combo box that list all the [Group] and i hidden textbox that looks up the code based on the cbx after update...all that is good.
However, when i try to requery the report the criteria is not working. I am simply using the date criteria to read the hidden text box with the code string.
Forms!frmMainHome!subaWelcome.Form!txtCodeForQuery .
the error code is "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."
I have a login form with a combobox which derives its list of values from an EmployeeRecord table (i.e., last name, first name, securityID, job grade and record number): only the last name and first name appear in the list. (I swiped some VB code for a multiuser login form and routine from several previous posts.)
After it performs the security validation/authorization, it then opens a navigation control form based upon the user's job grade. It runs after the click button event. To this I would like to update another table, LoginEvent, with the last name, first name and record number from the combobox selection, and also add the time and date of the click.