Access Developer Forum

This is a Forum for students in my Access Developer courses. Yes, you can still post questions in the comments section of the individual courses, but I will be using this Forum to post general notifications, ideas for future lessons, etc.

I recommend you subscribe to this Forum if you're interested in getting these kinds of updates. I'm not going to send out individual emails for everything.

Click Here to Post a comment and start a new discussion

Progress Bar SQL Server Upload Images Link
Despina Karayanni
11 days ago

Hello Richard. I have used the code of the progress bar (the last edition with the seconds and records measurement) in the customerF form of the database that is online -ACCESS SQL Server (using recordset and a path through query). However, it is dramatically slow. Should I make any modifications in the code? Thank you very much.

Richard Rost
9 days ago

Things like that probably will run pretty slow over the wire. It all depends on how many records your local DB has to process.

RSGoToRecord in recordsets Upload Images Link
Despina Karayanni
18 days ago

Hello Richard, As I am new in using recordsets the ACCESS SQL, which has made my database much faster, I confront a problem due to ignorance. It's fine with the commands rs.movefirst, rs.moveprevious, etc. However if I want to specify to go to a specific record, i.e., SELECT * FROM "CUSTOMERT" where customerID = "customerID". How could I specify afterwards to go to a specific record? Read More...

Adam Schwanz
18 days ago

If you have a unique identifier like a primary key, you can do rs.movefirst and then rs.findfirst to go to a specific record

Despina Karayanni
18 days ago

Thank you for your prompt reply. What I need is when I am in form customerlistF to press a command and direct to the specific customer file of the customerF form, which is a recordset. And as such, the customerID field in the recordset is unbound. So, the customerID is not defined.

Show 5 More Replies...

Richard Rost
17 days ago

So your customer form is unbound and you want to dynamically load the fields in the form based on a recordset value? Just pass the ID to the form and open the recordset to just that ID. Or am I not understanding your question...?

Despina Karayanni
16 days ago

Hello, Thank you for your answer. Yes, this is my problem. I'm trying to do so, using a combobox, unsuccessfully. Where could I find a proper code?

Richard Rost
16 days ago

When you create your recordset to load the values, just use the combo box as your criteria

openrecordset("SELECT * FROM CustomerT WHERE CustomerID=" & ComboValue)

Then proceed to load the rest of the values.

Despina Karayanni
16 days ago

Thank you very much. I was confused with all that complicated SQL code that I tried to apply. It was so simple, actually.

Richard Rost
16 days ago

It usually is after you get the hang of it. Just keep learning. I've been doing this for almost 30 years. :)

Manipulating Recordsets Upload Images Link
Glenn Taylor
43 days ago

A complex query produces a Recordset which is very useful in a report. But to refine the data to serve another purpose requires the removal of one or more records from this Recordset based on refined criteria. Using the rst.Delete command (VBA) to remove an unwanted record causes a "read-only" error (3027). I suspect the only way around this might be to write the records from the Recordset you want to keep to a temp table and then send the resultant temp table records to the report. Can anyone think of another workaround where the creation of a temp table is unnecessary?

Alex Hedley
43 days ago

What is your criteria for removal?
Why can't that just be added to a new query which is based from your original?

Glenn Taylor
43 days ago

The Recordset has 5 fields and if three of these fields are the same the second and subsequent records are thrown away. The query is a UNION query, quite complex. I really need to evaluate each record against it's adjacent (sorted) record for duplicates in these three fields so VBA seems to be a logical choice. I have the VBA working fine except it throws the error when I do find a record which needs to be discarded and make an attempt to delete it. I'm about ready to create the temp table to just get through this issue and move on. It would just be elegant to just run through the Recordset and toss those which aren't wanted and then feed the report with the modified Recordset. I guess that would make it too easy :-)

Show 7 More Replies...

Scott Axton
42 days ago

Glenn could you add a field, called "DelFlag" for instance, and then update that field to true with your record set?
Then feed the report with the data where the DelFlag = False?

Are you permanently deleting the record from your db?
I've found this helps in that case also. First mark the records for deletion. Then delete the records that are marked. I've had issues deleting one at a time "on the fly" before and this method solved my problems.

Scott Axton
42 days ago

Even that solution may not be of value to you. Due to the complexity of your query.
See the video: Not Updateable

Glenn Taylor
42 days ago

In deciding whether to set this additional field to True or False, I might as well just perform the delete operation. I can't have dissimilar fields in the UNION so the "manipulation" of the results can best be addressed in VBA code, which is pretty simple with a couple of if's. The complexity of this is the result of tracking miles traveled to businesses or clients and combining that with distances traveled to FedEx and USPS, for instance. The business travel doesn't require shipping so it's in a "tblTransaction" table (for lack of a better word) and the shipping is in a tblShipping table linked to the "tblTransaction" table, but it's conditional in that the shipping is done from our office to a shipping station and not a client or agency shipping to us. We have to keep track of all routes that documents take throughout the process. Thanks for your contributions to this dilemma.

Glenn Taylor
42 days ago

To answer your direct question, Scott, no we are not deleting any records from any database tables, simply reporting of the contents in a way that can be used for other reports (in this case, business travel for tax reporting purposes).

Glenn Taylor
42 days ago

Another option that came to mind (even though I wouldn't know how to do this) is to skip records with have the same info in the three fields from being output in the report by filtering within the detailed section of the report itself (seems like a jury rigged implementation). I might experiment with something simple to validate this method. It would certainly educate me on how records are processed within this report event!!

Richard Rost
41 days ago

Feed your Union Query into an Aggregate Query to remove the duplicates (Group By).

Glenn Taylor
34 days ago

I ended up using a table and as I processed the records via VBA, saved off the one's I wanted to the table. I then fed the report from the table's record source. Using an aggregate query would be problematic since there is additional processing if records are "deemed" to be duplicates. By that I mean they aren't really duplicates in the purest sense, only certain fields match each other and even that some of those fields might have "FxE" or "FxG", but are considered "duplicates" since I only take one trip to FedEx whether I'm shipping a package via FxE or FxG (Ground). So the processing is a little more complex than just looking for pure duplicates. The solution works very well. I can deal with the fact that I can't delete the table within the VBA or when exiting the output form but I do delete the table when I start a new DB session.

Adding appts to Google calendar Upload Images Link
Rajashree Natarajan
2 months ago

Hi Richard,

My office uses Google Calendar to make appts for my patients. . Is there a way to make an appointment directly into the calendar using the info from access. This is so we can have their contact, email, alternate contact in Google in case we have to reschedule in an emergency. Read More...

Adam Schwanz
2 months ago

From my understanding, to work with external programs like this you need an API. In this case, google calendar API.

Have you thought of using SQL server instead to work remotely?

Richard Rost
41 days ago

Like Adam said, I know that Google does have an API for using their calendar, Gmail, contacts, etc. I've personally never done anything with it, although I am curious and will probably experiment with it... one day...

Show 3 More Replies...

Need a simple VBA code Upload Images Link
Rajashree Natarajan
2 months ago

SO FRUSTRATED that I am just not getting it right. I have two field HADelivery date and 90dayFU date. I am so proud that I figured out VBA code to populate 90 days from delivery date and automatically fill the 90dayFU field Read More...

Alex Hedley
2 months ago

AfterUpdate?

Adam Schwanz
2 months ago

Yea just use the HAdelivery field after update event

If HAdelivery="" or IsNull(HAdelivery) Then
  'Field is blank, change the 90dayFU
  90dayFU = ""
Else
  'Field isn't blank, update the 90dayFU
  90dayFU = HAdelivery + 90
End If

And VBA can be hard, but you just have to keep getting exposed to it, keep practicing. If you move along to the developer lessons Richard does a great job teaching it, if you don't give up anyone can be taught :).

Show 2 More Replies...

Syntax for DoCmdRunSQL Upload Images Link
John Lucas III
2 months ago

I thought that SQL statements had to end in a ;"
In Developer 5 there are several DoCmd.RunSQL statements that do not end with a ;"
DoCmd.RunSQL "UPDATE CommissionT SET IsSelected=FALSE " & _ Read More...

Alex Hedley
2 months ago

Normally ; is used to terminate a line so you could have multiple commands. If you only have 1 then it isn't needed.

Richard Rost
2 months ago

In Access you generally only use single-command SQL statements and like Alex said, the semicolon isn't required.

Show 3 More Replies...

Comment Block Upload Images Link
Scott Axton
2 months ago

Hey guys does any one know if there is a key combination to do block comments in VBA?

I have used
/*
stuff
more stuff
*/

in another language but have not found the equivalent here. Read More...

Adam Schwanz
2 months ago

Found this online to make a shortcut for it. Done in the Code screen.

    Right-click on the toolbar and select Customize...
    Select the Commands tab.
    Under Categories click on Edit, then select Comment Block in the Commands listbox.
    Drag the Comment Block entry onto the Menu Bar (yep! the menu bar)
    Note: You should now see a new icon on the menu bar.
    Make sure that the new icon is highlighted (it will have a black square around it) then
    click Modify Selection button on the Customize dialog box.
    An interesting menu will popup.
    Under name, add an ampersand (&) to the beginning of the entry.
    So now instead of "Comment Block" it should read &Comment Block.
    Press Enter to save the change.
    Click on Modify Selection again and select Image and Text.
    Dismiss the Customize dialog box.
    Highlight any block of code and press Alt-C. Voila.
    Do the same thing for the Uncomment Block or
    any other commands that you find yourself using often.

Scott Axton
2 months ago

Smart alek - I swear I did a search and didn't find anything.
Thanks for that.

Show 5 More Replies...

VBA Command Listing Upload Images Link
Mark Pierce
2 months ago

Does anyone know of a list of VBA commands that have a definition of what they do that I can use for a reference?

Alex Hedley
2 months ago

I started filling up the Glossary with functions etc from each video, there's still lots to add but it's getting there.

Mark Pierce
2 months ago

you are the best Alex!

Show 2 More Replies...

Possible IIf function Upload Images Link
John Muir
2 months ago

In a form based on a query, i want a date field, [DocumentsDiary], to either populate if currently null or change if an existing date is in the field by + 7days, based on the selection of "POST-SETTLEMENT DOCUMENTS" from a field called [CasePhase]. Read More...

Adam Schwanz
2 months ago

Sorry I have a hard time following along what you're trying to do with all the field names that mean nothing to me.

So in general, not sure what you want to do if documentsdairy is null, so I just threw the current date in

IIF(IsNull(DocumentsDiary),Date(),DocumentsDiary+7)

For the second part, you just want to have an afterupdate event that sets the date.

If Date() - DocumentsDiary <= 7 Then ' something to catch so can't spam the date up if you change the combo box on accident
'do nothing
Else
DocumentsDiary = DocumentsDiary + 7
End if

John Muir
2 months ago

Sorry my question wasn't clear.

For the [casephase] field, I have a combo box where I can select the phase such as Case Intake. When I change that field to "Post Settlement Documents" to populate the [CasePhase] field from the Combo Box, I want the [DocumentsDiary] date field to be seven days in the future or seven days from today.

Something like if the CasePhase is currently "Case Intake" and today i change to "Post-Settlement Documents" i want the DocumentsDiary date field to show 8/23/2021.

Here is the combo box row source for [CasePhase]: "POST-SETTLEMENT DOCUMENTS";"INTAKE AND PREPARATION";"NEGOTIATIONS";"NEGOTIATIONS - SUSPENDED";"AWAITING POLICY ISSUANCE";"CLOSE CASE"

Does my question make sense?

Thanks,

John

When the

Show 2 More Replies...

ListBox sets SubForm filter Upload Images Link
Adrian Spickler
2 months ago

I'm putting together a basic setup for a Food Pantry. Client_Info_Table and Event_Log_Table are much like RR's CustomerT and ContactT with the field Client_ID linking the 2 tables. I have a Search/Listbox form for finding the client. A subform is supposed to show the Event_Log for each client as you click on them in the listbox. At the moment, the subform only seems to work for the first client in the list (Client_ID=1). Clicking on any other client only shows a blank new record for Client_ID=1. There are no errors. I went through the troubleshooting except for reinstall Access and reinstall Windows. The Event_Log_Query has [Forms]![Client_Search_Form]![SearchList] as the Client_ID criteria. SearchList is the name of the listbox. The OnClick event runs the following: Read More...

Private Sub SearchList_Click()
    [Event_Log_Query_subform].Requery
End Sub

Any direction would be appreciated.
Thanks,
Adrian

Alex Hedley
2 months ago

If you MsgBox [Forms]![Client_Search_Form]![SearchList] in your onclick event what shows?

Alex Hedley
2 months ago

Report Listbox might have some tips

Show 8 More Replies...

Adrian Spickler
2 months ago

Thanks Alex. I'll look at Report ListBox. The MsgBox command you gave does return the Client_ID correctly for each client I click.

Scott Axton
2 months ago

Adrian in your form that has the list and the SubForm of the Events. From Design Mode click on the subform and go to it's properties. Make sure that the link fields are correct. See image below.

Scott Axton
2 months ago

Adrian Spickler
2 months ago

Thanks for the feedback Scott. I do have the Link Fields set with both as Client_ID. I think I'm going to give up on the subform idea and make a "Client Check-In" button that opens up the Event_Log in a new window. This seems to be what RR does in his videos. It seams to work fine for my purposes. (I do prefer the look and flow of the subform though.)

Scott Axton
2 months ago

The code you have in the On Click is more than likely your issue.
[Event_Log_Query_subform].Requery tells Access "go out an reload the info for this subform.
It will always just show the first record after it has done that.
Go watch this video it may help you working with list boxes: Favorite Customers

Scott Axton
2 months ago

See screen shot below. Is this what you are trying to do?
Pick the person then the records on the right belonging to that person update?

Go watch the video just posted today... Relational Combo
There isn't much difference between a list box and a combo box.

Scott Axton
2 months ago

Show Older Comments...
View in Table Format