Forms :: VBA In Unbound ComboBox To Bring Up Record?
Dec 2, 2013
I have a form with subforms, where the main form's source is a table called tblCharts. Within this table there is a field called ID that is autonumbered.
So I created a combo box that's row source is a query that selects the ID field from table charts and two other descriptive fields to make navigation easier. Limit List is set to yes.
The problem comes up with code that I've used multiple times, but not with an autonumber. The code typicallly looks like this when I'm searching on a text field.
Code:
Sub cboPatientSelection_AfterUpdate ()
Dim rs As DAO.Recordset
If Not IsNull(Me.cboPatientSelection) Then
If Me.Dirty Then
Me.Dirty = False
[Code] ....
This worked at first but then 3070 runtime error came up, the form started messing up. So I made this code
Code:
Sub cboPatientSelection_AfterUpdate ()
Dim rs As DAO.Recordset
If Not IsNull(Me.cboPatientSelection) Then
If Me.Dirty Then
Me.Dirty = False
[code].....
This code worked at first, so I decided to make a copy of my form called Copy Of frmCharts. After this the code stopped working on the initial frmCharts but now works on the copy.
I also received other errors besides 2070. I know what error 3070 is but I can't figure out why it's not working on this form. I closed and opened the form a few times and it happened to start working. Very frustrating when I'm trying to make the system reliable for others to use
The code only seems to do this when I enter design view then switch back to form view.
Okay I have 3 forms. I have one form which is called frmEndOfTrial on this form there is a combo box which has two values--> Accepted and Return for credit.
I have two other forms called frmAccept and frmRFC. I have dragged those two forms onto the frmEndofTrial form and set there visibility to No.
What I want to happen is that when the user clicks on the 1st or 2nd option that corresponding form opens below the combo box?
I've tried a few things here but can't get it to work. I'm pretty limited in knowledge on access. Right now on hte combo box events I have this code for the After Update event me.sfrmRFC.Visible = True me.sfrmAccept.Visible = False
I know this is not what I want and not finished cause if I click on either accept or RFC it just brings up the sfrmRFC. How can I do this???
I am trying to prevent any foolish behavior from taking place upon a form. I have textboxes and comboboxes that users need to input data into or make selections within.
I came up with a method to prevent Paste as follows:
Code: Private Sub ComboBox_KeyDown(KeyCode As Integer, Shift As Integer) Call PurgeClipBoard() End Sub
[Code].....
However if I remove the MsgBox logic then the Cut is not prevented.
Note you need to include the Declares for those api functions if you use them.
I have a form "Clientfrm" with a field, "ClientID".
I have another form, "HomePage", with an unbound combobox with rowsource "ClientID" from Clientfrm. Along with that, I have an Open Form button that pops up "Clientfrm" to display existing data if an existing ClientID is typed.
SITUATION: A nonexistant ClientID is typed into "HomePage", a blank "Clientfrm" pops up with the field "ClientID" blank on the Clientfrm. Once filled out and closed, I am back to the "Homepage" to enter another "ClientID". However, the combobox does not display the new "ClientID" until the "Homepage" is closed and reopened. How do I fix this?
I tried the Me.ClientID.Requery on the On change event of the combobox of the "Homepage", but that is not working. What should I do? SHould I instead put that code in on change event in the "ClientID" field in the "Clientfrm?"
I would like to select a record from my combobox dropdown list and have that record populate in my subform. Currently, I am only able to select the 1st record at the top of the dropdown list to appear in my subform. But I would like to select any record from the dropdown list and have it populate my subform.
I am trying to figure out how to make my database check to see if the primary key value is a duplicate of anything ive entered previously and if yes, for my database to bring up that record instead of adding a new record.
I know how to do bring up records using a separate combobox (find) but I cant seem to do it using the primary key field itself.
I have been looking for days on the net for my listbox problem. It is there and found a few, even on this forum. but when i try the solutions mentioned i am in a total loss and do not know what to change to make it work for me.
I have a form named A/B Retriever with a record source qry input AB Bins Than i have a unbound combobox with row source qry input AB Bins. This populates 8 textboxes with B through I carton boxes, stored in a bin. The user selects a Bin location from the combobox and can put a "x" in a textbox to illustrate that the box is empty. This works perfect.
Underneath the input bin and box part i have 8 listboxes that shows a query that has counted the empty boxes with the corresponding bin location. this also works. but the question from users where, If i click on a, lets say empty B-box at Bin location 12A20, they want the combobox automatically focus on the combobox with the corresponding Bin location. This is a quick way for them to delete a empty box (remove the X).
This is a stupid problem, but I just can't seem to figure it out (some days I am smarter than others).
I have a form with three unbound comboboxs that are synchornized so that what you select in the first combobox changes your options in the 2nd and 3rd boxs. My problem is that I want to record the selections made in the unbound comboboxes in the table attached to the form, but cannot seem to make it work. Help!! :confused:
I have a Continuous subform based on a query based on few linked tables.One of those tables has a Yes/No field which shows in the subform.
I want to be able to change the status of this field for each record independently, so I placed an unbound check box in the subform Detail section and a Command button on the subform Header section. However when I click one record check box, all of them change, I did not expect that. How do I move forward to get this done?
I would like to have a form with a combo box open and then when a company is clicked on i would like for my report to open showng all those listings. I have the query and the report and the form but how do i get them to all work together they are not playing nice.
I created an unbound text box on a form that automatically pulls the current logged in user by using this:
Private Sub Txtuser_Click() Me.Txtuser = Environ("Username")
The form grabbs the logged in user with no problems, however, I ultimately want this information to also end up in my table. So the form has three boxes (to keep it simple). The user will type their first name and last name manually on the form which the record source is this "table" where their name goes to the table last name =Field 1 and first name=Field 2 respectively. I want the unbound box from the form to place the logged in user in Field 3 for the current record.
I have a form with 1 unbound listbox as drop down list (entypolst), an unbound text box(entypotxt) and a command button. The list box reads items from a table. I want to change a value (text) on listbox, input a text on textbox so pressing the button add a new record in a table (Table1 fields Category,Product) showing in my form as subform (SFTable1) in datasheet view. For that reason a made the following code:
Code: Private Sub AddBtn_Click() Dim ans As Integer Dim strSQL As String Dim db As DAO.Database Dim rs As DAO.Recordset Dim ctl As Control
[code]....
I take the error msg for 0 items selected in listbox and exit the sub.
Let's say, I'm updating a record, and I have a combo-box that says transfer and another combo-box that states "where to?" - or words to those effect.
Each record has a field for credit value and a field for debit value.
When I've finished entering the current record, I want to run code that fires the event, so, copies all the data from one record, to create a new record, BUT...
1) changes the "where to" from the old record to "From" in the new record. 2) changes either the credit or debit value, to the opposite in the new record?
I would like to do this from a sub-form. I can copy a whole record, no problem, but can't seem to change field values.
I have two unbound unlinked subforms residing on a 3rd unbound main form. When I enter the current record on Subform1 I would like the matching record(s) on Subform2 to be highlighted or otherwise formatted. I can get this to work for only the first record on subform2 due to the way I have my code setup on Subform1:
Code: Private Sub Accounting_Unit_Enter() 'find where AUs match. only works for first Subform2 record If Me.[Accounting Unit] = Forms!MainForm.[Subform2]!AccountingUnit Then msgbox "Match" End If End Sub
My thought was that I needed to reference the Recordset of Subform2 and search for all AccountingUnits that match the current AccountingUnit of Subform1,
it's easy to set up a form with a combobox that will list all records and moves the user to the selected record. But I can't figure out how to just show a selection of the records in this combobox, instead of all records.
So, to use the example used in most online tutorials: a drop down menu shows you all the names in a customer database. However, I'd like to have instead a dropdown menu with an overview of all surnames in the database and that I move to the first record with the selected surname.
I have rectangle object positioned 'behind' several fields on a report....
I wish to be able to hide these fields depending upon access rights....
In code am I able to bring the rectangle to the front and thus hide the fields in question... to save writing a routine to hide each control individually...?
I've looked for how to do but everywhere people asks how to bring a form in the front of all forms.I have a form emergent and modal. The program hides the access enviroment.But, when the form is loaded, it always appears under the window that I have active (whatever the window is, an excel sheet, internet explorer, etc).Is there any vba command to bring the form (or maybe the access application) to the front as the first visible window?
I have a database with a Supply Receipt Form. The first field in the form is a combobox for the user to select a material/supply. I'd like for the form to open to a new record every time. When I use:
DoCmd.GoToRecord , , acNewRec
in the Form On Open property, I lose the ability to select the material/supply. Same thing happens when I set the Data Entry property to Yes.
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.
In the past I've used command buttons with both VBA & Macros to bring up a prompt to save or discard changes to the current record then close the current form.For some reason, on just one single form that I just created, none of the techniques seem to work. It will close the form but will automatically save changes without bringing up a prompt. I've tried creating buttons from scratch, trying out both Macros & VBA, and I've tried copying/pasting buttons from other forms--of course making the appropriate changes.
I have a form and table called pcinfo-vendor that the customer enters some date that they need. They also need to pull up some vendor information from a table called vendorinfo Vendor name, Address, City, State, Phone #, FAX # and Email. How can I bring up the data from the vendorinfo table on to my form called pcinfo-vendor? So that the customer does not have to type in - Vendor name, Address, City, State, Phone #, FAX # and Email each time for every vendor.
I'm using a very simple unbound textbox on a very simple form, with the following Expression to find me data in a query and sum the last 30 days, based on a combo-box on my form...
Only thing is, it's showing me only the data from the last 30 days...regardless of the value in the combo.
=DSum("Credit","qryIncVsExp","TransDate>=#" & Date()-30 & "#" And "AccountID_FK="""&[cboaccount].[column](1)&"")
I'm sure it's something to do with the number of "'s I have, but I'm probably more than likely ever so wrong.
I have an access form that needs filling in daily by various people.
It's to document changes to a website and I currently have a combobox box set up for the various sections to state whether they are AMENDS, REVERTS or NO CHANGE.
I have set conditional formatting to then highlight these sections but am also trying to get it to work so that if the user chooses "NO CHANGE" then the data for that field copies over from the previous record.
I have set this up in the AfterUpdate code for the combobox, but nothing is happening, not even an error....
Code: Private Sub COMBOBOX1_AfterUpdate() If Me.COMBOBOX1 = 3 Then Me.[FIELD_TO_CHANGE] = DLookup("[FIELD_TO_CHANGE]", "tb_TABLE", "[ID]=Forms![form_FORM]![ID]-1") End If End Sub
(Where 3 is the value of NO CHANGE in the combobox, and FIELD_TO_CHANGE, tb_TABLE and form_FORM being the names of the various elements)...
