Modules & VBA :: Working With Selected Records In Datasheet
Oct 24, 2013
I have a SQL view linked into a ms access application. That view is used in a subform and in datasheet view. I want to select multiple records and click on a button that will delete those selected rows. I need the button because the view has 1 table and a subquery joined in the view and will not allow me to delete from the main table in that view. So, I figured I can select the rows I want to delete and then create a button that will send a passthru command to delete those records from the main table.
In a form's VBA code, I'd like to know whether a full record has been selected, like when the user selects one or more rows before deleting with the Del key.
Is this possible?
According to [URL]......, Me.SelHeight returns the number of rows that have been selected within the selection rectangle. If I select a rectangle of cells in Datasheet view, for example, Me.SelHeight will be > 0 even though full records have not been selected. I want to know how many full records have been selected. Is this possible?
I have button on my form that uses a selected record in a listbox (non multi-select) to run code that updates a table. The code itself works great so long as there is a record selected in the listbox. If no record is selected an error is thrown. I'd prefer the end user not to ever see that error. If the end user clicks the button and no records are selected, I'd like for nothing to happen at all. I've tried several variations of checking the listbox with an if / then statement to see if records are selected and if so to exit sub, but they all throw errors when no record is selected in the listbox.
Code:
If IsNull(Me!lstHeatTreatments) Then Exit Sub End If
I'm using the following code to clear selected records from a listbox after a button is pressed:
Code:
For i = 0 To lstProducts.ListCount - 1 lstProducts.Selected(i) = False Next
The code works great except that it leaves the listbox scrolled all the way to the bottom. What is the best way to scroll back up to the top of the listbox without selecting another record?
I created a form to enter the lookup data and build the SQL string to create a recordset in VBA that is a subset of the original data. Using the Form wizard I created a form that displays ALL of the records in that original table (actually a QUERY). What I want to do is open this second form but have it reference the recordset I built in VBA.
I have database with an userform called AssignWP, combobox called WPDevBy, listbox called List352 (Multi select) and table called Justified.I am trying to update one field WPDevelopedBy of the table as combobox value based on list box multi selected records.
I am using the following code to check for duplicate tickets when importing multiple records into a datasheet view form by using the paste append function.
Code: Private Sub Ticket_Number_BeforeUpdate(Cancel As Integer) DoCmd.SetWarnings False If DLookup("Ticket_Number", "Record_Store", "Ticket_Number= '" & Me.Ticket_Number.Value & "'") > 0 Then Cancel = True MsgBox "There were import errors, please open View Import Errors above." End If End Sub
The form is used to insert multiple records into the database at a single time.
That codes works to check for duplicates. And if there are none there are no popup messages.
If there are duplicates though it gives a popup for every single Ticket_Number that is a duplicate.
I am wondering if there is a way for it to give only a single popup once it completes checking all the records to be imported for duplicates.
Im v new to VBA and been having trouble getting the following code to loop through the records in my subform:
Private Sub cmdComplete_Call_Click() Dim rs As dao.Recordset Set rs = Me.fsub_Call_Off_Quantities.Form.RecordsetClone rs.MoveFirst
[code]....
It only seems to update the first records txtQuantity_Called_Off value. I have tried all the 'looping' code variations i can find and they all seem to only update the first .
I have a simple form in datasheet view in Company Name sequence. By default it starts the list at the beginning. I would like to put a combo box in the form header that pulls down the companies to allow the user to select the starting company to list. I don't want to go directly to the selected company, I just want the list to populate starting with the selected company. I can create the combo box and I know I use the After Update event to get the selected value, but how do I restart the list. Ideally, I would still like the operator to be able to scroll up and down after the list is redisplayed starting with the selected company.
I am building a tracking database where we would be able to track information which field are null data and report them to our Administration to fill the null data. For this I have created a form name "Search" and I have a combo box control on that form which is bound to "Table = employee" and its ROW SOURCE TYPE="Field List", I would like to able to query records where the selected value in this combo box is null through out the table.
For example if I select "Telephone" from this combo box dropdown, I would like the query to show all the records where the "Telephone" is null, how to set the criteria in query to take the combo box value as "Field Name" and then compare it with the Field/Column in the table and show the null values.
In Access 2010, if you have a form with a subform in datasheet view, and you move the focus away from the subform, how can you tell which record(s) on the subform are selected?
When the subform has the focus, the selected row is shown with a blue/grey tint. But as soon as the subform loses the focus, this disappears and all rows look identical.
Its an issue as I have 2 datasheet subforms on my main form, with the 2nd showing records related to the record selected in the 1st. But as soon as the 2nd subform receives the focus, it is impossible to see which record in subform 1 was selected.
I have tried conditional formatting, but that does not work.
The situation is i have two forms, looking at the same table, but the main table shows more records, I would like to select a record in the datasheet subform and have the main form flick through to the corresponding record.
I think i am missing the obvious but cannot seem to get this one....
I am trying to get my form to return a value of the amount of working weeks in a month i.e. a 4 week month (like june - 4 mondays) or a 5 week month (like may - 5 mondays).
i can calculate the number of full weeks using the control source..
I'm using a split form with the datasheet on the left.
When I use the 'Find' in the Navigation buttons at the bottom of the form, it refuse to find text in a particular text box.
The text box is a memo field and is enabled.
Example:
I have text in the memo field which is a title of an episode - "The Outcast."
When I start typing "the" it finds this text in the memo field. But when I type the entire title it the finds the text in another field. ie "out."
I've tried the 3 different Search setting in options. When I use the Find command from the ribbon it work ok as long as I select the text box to search in first.
i have a form with two cascading combo's that work when the form is opened as a single form however when i place the form on Page 3 of a TabCtl it fails to cascade the correct values.
frmSiteDetails > TabCtl33 > Page3 > frm ConditionDetails subform with Combo's 1 & 2 Combo 1 = [ElementGroupName] SELECT ElementGroupID, ElementGroupName FROM tblElementGroup; has Column Count of 2 and Bound Column 2 Combo 2 = [Element] qrySubElements has SQL:
I have a subform that is in Datasheet view. If I open this subform on it's own, the Date Picker works for the Date field. However, if I open the main form with the subform on it, the date picker doesn't work? Nothing happens when I select a date on the calender.
Hi, this problem is probably very simple to solve but I can't seem to find an answer to it.I have created a search form that returns all records meeting the criteria back into the search form. What I want to be able to do is to display the records in a datasheet view. This is what I currently have and it works ok but how do I convert?Private Sub cmdSearch_Click() Dim LSQL As String Dim LSearchString As String Dim LTownString As String Dim LActive As Integer Set LSQL = Me.RecordsetClone If (Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True) And (Len(txtsearchTown) = 0 Or IsNull(txtsearchTown) = True) Then 'MsgBox "You must enter a search string." Else LSearchString = txtSearchString LTownString = txtsearchTown Select Case Me.Frame99.Value Case Is = 1 stActive = " AND Active = -1" Case Is = 2 stActive = " AND Active = 0" Case Is = 3 stActive = "" End Select 'Filter results based on search string LSQL = "select * from tblContacts" 'LSQL = LSQL & " where LastName LIKE '*" & LSearchString & "*' AND Town LIKE '*" & LTownString & "*'" & stActive If Form_frmMain2.RecordsetClone.RecordCount = 0 Then MsgBox "No records found" Else Form_frmMain2.RecordSource = LSQL End If 'Clear search string txtSearchString = "" txtsearchTown = "" End IfEnd Sub
I have recently learned the use of 'ysnprint' (I am a novice), but wondered if there was an a way of selecting individual adresses in my db to exclude from the full address label printing.
I am having trouble getting a combo box to go to a selected record on a form. It basically ignores the selection. I have successfully used the combo box to do searches on other forms. The only difference I'm noticing is that form I'm using has multiple records for the search criteria.
I was wondering if what I'm trying to do in Access XP and 2003 is possible. I've looked at countless templates and samples and havn't seen anything like what I want to do to be able to construct it. I've tried manipulating queries, relationships different table joins but to no avail. I'm only a beginner in VBA and know little SQL, I would be truly gratefull if someone can please shed some light on the below.
I've been creating a preventitive fleet maintenance database in access and want to make some changes that will make the database more flexible.
What I have is a main form which has the following main fields from the tblWorkOrder; WorkOrderID, FleetID, StartDate, FinishDate, Odometer, and ServiceTypeID.
Within this I have a subform in datasheet view (tblServiceItems, fields; ServiceItemID, ServiceDescription, ServiceCompleted(checkbox)) and this lists all the service items that belong to the ServiceTypeID in the main form.
I have another table called tblServiceDetails and this has the fields WorkOrderID, ServiceItemID which join the above two tables.
My problem is that I can't get the subform to list all the records that are in the table tblServiceItems, it only shows each record if you go through and select it manually. What I want it to do is to show like a listbox and allow me to go through the list and check of those service items once they have been completed. On top of this I want all those service items for that service type to be recorded against that workorder (regardless of completed or not) so then when I create a report on a WorkOrder It will list all the service items showing the checkbox's of which services have been done.
I've tried to do this with a listbox, but It doesn't show the checkbox, only yes or no. I've also tried using the tblServiceItems as the subform but this only updated the table and didn't create a record in the table tblServiceDetails matching it with the workorder.
I'm using a datasheet that can be filtered for certain fields. I have a macro that opens a form to the record that was selected (aka "current") in the datasheet. It filters this opened form to "1 of 1" which makes sense. How would I go about filtering that form for all of the records left (after filtering) in the datasheet?
I hate macros, but I'm doing client-specific work. The primary key is a field (autonumber) called "ListingNumber".
I have a form in datasheet view that has a user directory with a list of each person. On each row you can click to open another form that contains the users details using the below VBA:
But once you open to that specific record, no other records are available to navigate to.
I want to be able to stay on that detail form that was opened and go to another record.
How do I continue to pull up that specific user in my detail form, but also load the others so I can navigate to them from the detail form without having to go back out to the datasheet form?
