Find and Select Continuous Form record using VBA

I have a continuous form, and I would like to programatically find and select one of the records with VBA if it meets certain conditions. How would I do this? Basically trying to control the record selector arrow with VBA.


Post your answer or comment

comments powered by Disqus
Hi,

Is there some way I can select a record using VBA, as if I clicked on the record selector at the left side of a form? (the record selector is marked black)

TIA

Hi! Anyone here knows how to filter a table record using VBA?

For example, I have a list of customers. I want to filter them to those living in the East only. How do i do it using VBA code?

Please reply.. Really need help here. Thanks!

I want to find duplicate records of one field in a table and delete all but one of the records using VBA.

I was thinking of using: CurrentDb.Execute "DELETE *
but I'm not sure how to put what I want after that.

I did use the Query Wizard and was able to get the duplicate SQL needed:

SELECT First(tblUserID.[UserID]) AS [UserID Field], Count(tblUserID.[UserID]) AS NumberOfDups
FROM tblUserID
GROUP BY tblUserID.[UserID]
HAVING (((Count(tblUserID.[UserID]))>1));

Any help would be much appreciated! Thanks.

Ms Access 2010 accdb
The age old issue of dealing with a continuous form when there may be no records.

This is a SubForm. I have nearly resolved the issue by adding an unbound text box control on the form footer and code to the On Current event of the form.

	Code:
	If Not (Me.Recordset.BOF And Me.Recordset.EOF) Then
        Me!txtRefinanceAmountSum.ControlSource = "=Sum([RefinanceAmount])"      'records exit
    Else
        Me.txtRefinanceAmountSum = 0                                            'no records so use Zero
    End If

If I enter the main form and select a date that has no records in the subform, the correct result appears - $0.00.
If I enter a date where records exist, the correct value result ie the =Sum(... works.

However... If I then select a date with no records, I get nothing ie, the $0.00 does not rsult.

What could be happening here ? Could the fact that there were records be tricking the If Then statement to assume records when there are none ?

With my form open, I want a button that when clicked will create a new record using VBA and save it in my table. Then I want it to GoTo the record (or last record in the table) in the current form.

The following code does all of the above, EXCEPT it goes to the last record of the form, rather than the last record of the table.

Here's my code.

Dim dbTransportationPlanning As DAO.Database
Dim rstCurrent_Shipments As DAO.Recordset
Dim Shipment_num As String
Set dbTransportationPlanning = CurrentDb
Set rstCurrent_Shipments = dbTransportationPlanning.OpenRecordset("Current_Sh ipments")
rstCurrent_Shipments.AddNew
rstCurrent_Shipments("Product").Value = "ONE"
rstCurrent_Shipments("TNProject").Value = "NEW"
rstCurrent_Shipments("Shipment_Num").Value = "NEW"
rstCurrent_Shipments("Departure_Date").Value = "1/1/1900"
rstCurrent_Shipments("Shipper").Value = "NEW."
rstCurrent_Shipments("Consignee").Value = "NEW"
DoCmd.SetWarnings False
DoCmd.GoToRecord , , acLast
DoCmd.OpenQuery ("Sharepoint_Append")
DoCmd.SetWarnings True

Hi All,

I am new to MS Access VBA, but not to VBA or to MS Access.

Thanks in advance for whatever help you can give me.

I am trying to verify whether or not duplicate records exist in a table (General Flying & Ownership Expenses) using VBA. What is the best way to proceed? I designed a query that counts duplicate records (see below). A duplicate record would be two records having the same "Type" and the same "Year"


SELECT

[General Flying & Ownership Expenses].Year,
[General Flying & Ownership Expenses].Type,
Count([General Flying & Ownership Expenses].Type)

AS CountOfType

FROM [General Flying & Ownership Expenses]

GROUP BY

[General Flying & Ownership Expenses].Year,
[General Flying & Ownership Expenses].Type

HAVING

(((Count([General Flying & Ownership Expenses].Type))>1));



What I want is if the results of this query for CountOfType are greater than 1 (i.e. there are duplicates), a message box will pop up indicating the table where the error is coming from and for the Sub to halt (see pseudo-code below).



Pseudo-Code

Sub Duplicate_Tester

Duplicate Test Process
if (results from Duplicate Test Process) > 1 then
msgbox(“Duplicate records in General Flying and Ownership Expenses”)
End
end if

End Sub



How to do this? Should I be using a query at all? I tried using the MS Access help and found it baffling.

Thank you!

- Kevin

Hi Guys,

Background:

I have a continuous subform within a form. The continuous form is used to display search results. Each search result (record) has a checkbox at the end to (hopefully) allow the user to select it to enter that record into a listbox which will allow the compilation of a report.

Problem:

The checkboxes are linked to a table with a corresponding "Yes/No" type field. The checkboxes will display checked/unchecked if this is the value in the table.

However, I cannot check or uncheck these boxes. It's as if they are locked, but I have checked and they are not. I've also tried tying an event to the cb_Click() event - but cannot get the event to fire.

Please help!!

Many thanks in advance.

I have this selection form whereby users will select a specific record and open another form based on that record. However, that form has a header that contents fields that i would always "hide" . So it results in this big empty space in the header. How do i set the form size of my HEADER using vba?(both width and height) so that i can hide this empty space.

Hi

I am relatively new to using VBA in Access an would appreciated a little help!

I am trying to add a new record via VBA. Actually a series of records. I want the new records to be partially auto-completed by the information on the existing form and subform. The goal is to take the number of weeks in a school year and set up the record when the child is enrolled in the program on the History Report. I want it to loop until the number of weeks in the program equals the number of records generated. My Problem is the number of weeks in a program is stored in another table. I keep hitting a wall trying to pull that information. Below is my latest attempt. I added a subform with the data I needed. Now I am trying to call the data from the subform to fill new records. There are several fields not auto-completed that will be throughout the program.

Any help would be appreciated! I hope this makes sense my brain is fried. Thanks!

Private Sub SetPaymentHistory_Click()


Dim Week
Dim Counter
Dim WeekID


WeekID = 10
Counter = 0


Do Until WeekID = Counter
Set rs = CurrentDb.OpenRecordset("Select * From PaymentHistory")
Week = Counter + 1

With rs
.AddNew
![ChildID] = [Forms]![SessionEnrollmentForm]![ChildID]
![SessionID] = [Forms]![SessionEnrollmentForm]![SessionID]
![ProgramID] = [Forms]![SessionEnrollmentForm]![Program Type]
![WeekID] = [subForms]![SessionDefine-subform]![WeekID] this was my idea but I keep getting an error message here.
![Cost of Program] = [Forms]![SessionEnrollmentForm]![Payment Amount]
![Sliding Scale] = [Forms]![SessionEnrollmentForm]![Sliding Scale]
![Sliding Amount] = [Forms]![SessionEnrollmentForm]![Sliding Amount]
![CCDF] = [Forms]![SessionEnrollmentForm]![CCDF]
![CCDF amount] = [Forms]![SessionEnrollmentForm]![CCDF amount]
.Update
.Close
End With

Counter = Week

Loop

Set rs = Nothing

DoCmd.OpenForm "PaymentHistoryForm", acFormDS, , "ChildID=" & Me.ChildID

End Sub

What is the recommended method for deleting records using a VBA module? Currently I am using an SQL method that specifies the table name and the upper time stamp date from which values should be deleted.

My database is continually being updated with new files from this same VBA module. I would like to delete old records based on a rolling window (say 30 days). Would this be the best way to go? It seems a bit slow...but then again my database contains 1 million + records.

Thanks.

not sure how to get my database to do what i need.

i have a form that uses cascading combos to filter records before they are entered on a form. When the last combo box has had its value selected i need something like an AfterUpdate event to either trigger vba.

i need a simple calculation performed and depending on the results of that calculation either display a message and clear the current record or save the current record.

using vba i had thought along these lines:

Dim bkValue As String
Dim facStock As String
Dim totStock As String


bkValue = "Select Booked_Value from tblBooked"
facStock = "Select Factory_Stock from tblTool"
totStock = facStock + bkValue

if totStock

Hi all,
I have this database to keep track of our company's licenses. It works fine. We have about 250 records, each record contains licensee's info and also has an ole field linked to a folder that gets updated directly from the form.

All the linked files are pdf files.

Whenever we have a new license or one that needs to get updated we do (right click ----Insert Object ---etc).

The problem I am having is..

If the user is updating a license or replacing an expired one with a new one, the user needs to access the folder, move the expired License to a new folder and name the new license so it gets linked to the database.

Is there a way to do this process using vba like a button to update or replace the existing pdf file.

the linked files are under a folder like this:

R:operLicensesFolder(usualy a state name)Filename.pdf

Thanks in advance

Hi, basically what i want to do is append a record to a table using vba. I would like to take two values from my form (productID, supplierID) and insert them into a table (supplierProductsTBL) under the same headings. I've constructed an sql statement but am getting the following error:

runtime error '3346'

number of query values and destination fields are not the same'

I think this happens because the table also contains an autonumber field (supplierProductID) and i'm not referencing this in my sql statement (below):

mySQL = "INSERT INTO SupplierProductsTBL ( SupplierID, ProductID ) VALUES ('" & SupplierID & "'), ('" & ProductID & "')"

i wuld just get rid of the autonumber field but i need this.

Any help on this would be appreciated, cheers mark.

Hi all,

I have a database with a continuous form based on a query. The problem I have is that the database includes images and therefore the continuous form is loading very slowly.

What I would like to do is limit the view of results so that I only see 5 records at a time and have a button to see the next 5 which will allow me to click through all the records.

Does anyone know how this can be done? I am really struggling with it at the moment and have searched for information but cannot find any. If someone could help I would be really grateful.

Thanks.

Hi

I came across an interesting scenario that one of my colleagues had encountered. She had a document in which she needed to remove a number (thousands) of excess spaces in a large table that spanned many pages. The only solution we could come up with was to replace all the spaces with nothing. This, however, meant going back through one of the columns in the table and re-entering those spaces that were actually required. There were only a small number of these to do so nothing too terrible in this instance. It got me thinking though! Is there a way in which the in-build find and replace facility can be used within a selection of text? I have tried selecting some text and using find and replace, but it works its way through the whole document, ignoring the selection. Any solutions would be much appreciated.

If the form I am using already has a record using a specific number, I want the user to be aware before they enter all the info. I found this code and it works except when it redirects me to the existing record, it doesn't. It redirects me to record #1. The field booking number is not a PK or AutoNumber field. It is a number field and as you can imagine, each is very specific to each person. Using that number as the PK field just won't work for this application. What I would like to accomplish is to be redirected to an existing booking number if it exists, otherwise the user will just fill in the form as needed. Here's what I have"

CODE:
[Private Sub Booking_Number_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
SID = Me.BookingNumber.Value
stLinkCriteria = [BookingNumber] = " & " '" & SID & "'"
'Check tblClient table for duplicate BookingNumber
If DCount("*", "tblClientInfo", "[BookingNumber]=" & Me.BookingNumber) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Booking Number " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", _
vbInformation, "Duplicate Information"
'Go to record of original Booking Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing

End Sub]

Any help will be greatly appreciated

I have a button in my continuous form that shows up next to each record. I want this button to delete the record next to it with the Recordset.Delete code. Sometimes it works, and other times it says a current record is not selected. What can I added to the code to make sure that it selects the record it is next to? Thanks.

It's actually a Main form with 2 nested subforms. Selecting the datasheet record subform3 selects the corresponding record in the Form of subform2. After clicking on the datasheet to select the record, the focus automatically goes to the first record. I can click on the same datasheet record to regain the focus. I've been searching, but this activity is not very common. I just want to click on the datasheet record and have it stay focused on that record. The code I have used for the on_click of subform3 is:

Code: Private Sub Form_Click() Dim rs As Object Set rs = Forms!frmSPK.frmSPKSF.Form.Recordset.Clone rs.FindFirst "ID=" & Me.ID Forms!frmSPK.frmSPKSF.Form.Bookmark = rs.Bookmark End Sub

Hi all,

I have inherited a database with a table with plenty of duplicate records. In some cases the a record may appear more than once.

I have kindly been given the following code but somehow I cannot get it to work. I am getting a runcode error.

In my example database I have three fields a, b and c
and I have duplicate records:-


Please note I am using A2003....I have attached word doc with code for anyone's perusal.

Just amended my post to include my sample database. I am also providing, again, some background to my problem:-

I've inherited this database with a table which has accidentally been populated with records that already exist.

I have set up a sample database that more or less mimics the real one. I have omitted PKs and other database rules because that is what I have. I "simply" require a coherent piece of VBA code that will save me the trouble of sorting the records and then deleting the duplicate records that I see.

Obviously I would be working on a copy to start of with so that I would first understand the code before I embark on the real thing.

I completely understand the concerns that maybe the database is structured improperly or whether additional fields have to be included, but my main concern is deleting the duplicates.

The code I am supplying has been kindly offered to me but is not working. I believe its almost there, and I am sure it just requires some tweaking.

Basically what I need is code which will sort the table and then compare the entire contents of one record with the one below it. If it is the same, ie. all the fields are the same, then one of the duplicates should be deleted.

Thanks and any advice on the code grately appreciated.

Hello all. I am a newbie to the world of Access and vba. I have been teaching myself a couple of things over the past few days.

I have a table that was created within a form using vba codes in Public Function. The sql that pulls data from 3 tables is also under the Public Function vba code.

Since this is not pulling from any table or report, I cannot use 'DoCmd.PrintOut "Table or Report Name", acViewPreview' to print the table within the form. I am anticipating also having an issue with some of the columns not showing because of the number of columns in the form table.

Unfortunately I can not post the code due to the nature of the information but can provide further clarification if needed. Any help is greatly appreciated. Thanks in advance

I need to find a value such as ABC123 in a cell and select the cell.
if no match is found then I want to do something else
ABC123 is in fact a variable but I know how to find a variable

A recorded macro gives the following to find the cell and select it but does not show me the code for if no match then...


	Code:
	 Cells.Find(What:="ABC123", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate


have tried a search on the internet but not found what I'm looking for
smiler44

Hope somebody can help with this. I checked the archives but not luck.
My continuous form allows entry of 12 records. On the thirteenth record the new row scrolls to the top and all the previous rows disappear. Every new record after that scrolls up out of view and a new blank row is at the top. I can scroll up to see the previous records but they do not scroll one at a time as data entry is performed.
Data Entry is set to No, Allow Additions is set to Yes.

Am I missing something?

Thanks for any help.
Kerry

I've only recently gotten to know about continuous forms and their use a bit. So for example, I used a maximum number of pre-defined (6) visits. I need to change that still, but during the development of the sales forms I already ran into a problem.

At first I was planning to have a certain maximum number of items to have on a form and create an invoice from that. But of course that would limit the number of items on one invoice which isn't good. So, now I have a continous form which adds a line containing productname, price, nr of that item etc. anytime needed.

In the previous design idea it would have been easy to calculate the total amount of money at the end of the list since the list was predefined. So I could just say invoiceamount = itemID1amount + itemID2amount + itemID3amout etc.
With the "new" design however that is impossible to do since the number of objects is unknown. So how do I create a piece of code which does that? In the continous form I have a SaleID which keeps track of which items belong to a certain Sale and a TotalItemsID autonumber which keeps track of the number of lines (items) which belong to that specific Sale.

So I need to count the total number of TotalItemsID's within a certain SaleID and then have a piece of code which creates a sum of that counted list of items.

I'm new at this, I've found the function Dcount() should be used to accomplish this, but the rest, as of yet, remains a mistery to me.

How to do this?

I'm slightly confused with this question as I've got two continuous forms dealing with separate parts of a database and one works and the other doesn't, so I created a test table, continuous form and entry form which does not work either.

I've got a test table with 'Acc_code', 'name' and 'postcode' as fields.

I created a continuous form with the 3 fields in which is not editable and a form with the 3 fields also on (a bit redundant but I'm just trying to see where I'm going wrong)

On the continuous form event double-click, I've got the following code:


	Code:
	DoCmd.OpenForm "testform", acNormal, , "[Acc_Code] = " & Me.[Acc_Code]

The code I got from searching through the forum for double click updating of continuous form records.

It looks right and compared to coding I already have for the other part of my database which works, it's almost exactly the same apart from the field names and the form name.

When I double-click on an entry, instead of it opening the single form, I get a prompt for: 'Enter Parameter Value' for the field value (so if the acc_code='test01' then it's asking for the parameter value for test01) with an entry box underneath.

If I leave it blank, it opens up the form with no records but if I type 'test01' into the box, I get the 'test01' single form record appearing.

I'd be grateful if I could have guidance for this, I'm sure it's me going wrong somewhere.

Thanks


Not finding an answer? Try a Google search.