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

Many to Many Upload Images Link
Edwards Christopher
3 days ago

Hello,

I watched Richards Many-to-Many video. I want to add another layer to the many to many relationship where depending on the group assigned to the customer, certain trainings will be available in the sub form. I am at expert level 3 so this is already beyond me. Can someone point me to a resource on this site or provide guidance? Will I pick this up in expert 7?

Richard Rost
3 days ago

Can you be a little more specific? Do you mean if the student is, for example, in the Physics group (major) he only sees Physics classes?

Adam Schwanz
3 days ago

Yea unless your talking about something else. A many to many junction table is all that you need. One table with classes and another with the students. Now if your saying you want to filter your results down, that can be done in numerous ways. I dont know how your database is setup but if you have the customer and classes table setup. I would just add a field to the customers table that you could enter in a major. Then you could make a one to many relationship between major and the classes. You could then limit what courses are offered to him by his major.

You could also just use a filter or where clause, or you could just manually assign the classes to the student with the junction table. Access always seems to have lots of ways to get to the same end result. Just depends how your tables and form is setup now and what you want to do.

Show 8 More Replies...

Edwards Christopher
3 days ago

Close. Lets say at a workplace I have a maintenance department with multiple employees and positions within the department. The electrician and foreman need to be trained on electrical safety but the general mechanic doesnt. Employee John (electrician) and Frank (foreman) need electrical training but Joe (general mech) doesnt. I would like to see the employees assigned training based on the employees position. The department would be irrelevant. These would populate in the training sub form in the employee form.

Edwards Christopher
3 days ago

Thanks Adam for the guidance. I worked on this again today and still struggled. I have two many to many relationships and I am stuggling to make them connect. For instance, in my scenario described the maintenance person can learn another protist ion which has different assigned training. It like the person ilmahoring in physics may have a double major in math as well lol

Adam Schwanz
2 days ago

Hey Edwards, If you don't have it figured out by later today I'll come up with something. My server got fried yesterday and today has been absolute chaos so my brain just isn't working right now. When things calm down later I'll try to give you some ideas. I assume you also need to track if the training has been completed by a specific employee?

Edwards Christopher
2 days ago

I got close today by nesting my subforms. I can see the specific course per employee depending on their position. It would be great if I could keep track of when that training is completed and expired per the TechHelp video in the nested subform. I do need to track training by employee.

Adam Schwanz
2 days ago

Maybe Rick can chime in. I don't know if it was because of all the work chaos, but this question was really boggling me. I'm sure there's a better way but this is what I had working. You can't control the complete check because it's a dlookup but I assume you would do that in another form linked to controlling EmployeeXTrainingT anyways.

I'm under the assumption that the goal of this is just to get a list of classes available to showup on the subform based on job position and see if they're completed or not. This should be able to cover that. If you need more I'm going to need a beach vacation first LOL.

Made a query for the subform to filter trainings offered to employee based on job position, then made a dlookup in the checkbox record source to check if the course has been completed or not.

=Nz(DLookUp("Complete","EmployeeXTrainingT","EmployeeID=" & [Forms]![EmployeeF]![EmployeeID] & " and TrainingID=" & [TrainingID]),False)

I'll include some images too. Hopefully this helps you.

Adam Schwanz
2 days ago

Richard Rost
10 hours ago

You basically just have multiple many-to-many relationships. Each employee has a department, but separate from that they have a job title. The training could be based on job title. It's all how you want to set it up.

Vba Syntax Upload Images Link
Abraham Breuer
20 days ago

Hi Richard

I am currently going through the developer lessons, I love them its brilliant,
I would like to ask you if you can put on your tech help list to explain everything about syntax in VBA, Read More...

Adam Schwanz
20 days ago

Im just imagining this being far far beyond a tech help video. Maybe even beyond a seminar. There is so much to review, i don't even know if you could go over this being so broad. I would recommend continuing the classes and most of all practicing it yourself. I used to have problems remembering stuff but working with it everyday makes it a lot easier. You get used to it really quickly that it becomes muscle memory to just throw them in where they are needed.

A couple times of why doesnt this work? Maybe i need a & in front of the field? Yep that was it Really nails it in to remember that next time.

With the & sign your usually adding a field or text to a statement. With your usually saying this isnt a function or integer i want to say a string. But they have sooo many other uses.

Scott Axton
19 days ago

Adam Breuer -
   I  agree 100% with Adam Schwanz reply.  There just isn't any amount of watching videos or even reading hand books that are a good substitute for actually doing the work.

Unfortunately the best teacher is often fixing your own errors.  I (we) could take a look at some code and tell you right away you are missing a quote or you need an & but you won't learn from it.

That said different people learn different ways.  I suggest getting a spiral note book an actually writing down some of the tidbits that are important to you.  Don't type it in.  Writing things down helps "insert it into the brain"

For instance - using your example above:
  & is an operator used for concatenation.  Used to bring together two pieces of text or to join the text values in two field names.

Show 4 More Replies...

Scott Axton
19 days ago

Got cut off Sorry!
------------
Last but not least - There is a great resource, here that in my opinion is too hard to find, under utilized, and often overlooked. (Richard and Alex Maybe this can be made more up front on the web pages)
That is the Topic Glossary:

Access Glossary

Michelle Maughan
19 days ago

Hi Scott, thanks for the Glossary Link, very informative. I thought I had seen everything on this website but can't recall ever seeing this before.
Cheers Michelle

Michelle Maughan
19 days ago

Just found the link in the footer under Info ;)

Richard Rost
13 days ago

Yeah it would be tough to put together one big SYNTAX lesson since there are a lot of different things to remember. Maybe one of those single-page laminated cheat sheet thingies would be helpful. You've seen them at the book stores. Shortcut keys and whatnot. Well, I'm not going to laminate them. I'll make the sheet and YOU guys can print them out and laminate them yourselves. LOL. I've actually had something like this on my (overly long) to-do list for a while now.

DMax DLookUp CommissionRate Upload Images Link
Abraham Breuer
30 days ago

Hi Richard
Just finished the Dev4,
the technique you use to log the commission rates for each rep into the commisionT is by appending one order at a time,
how about when I want to append multiple orders at a time for some period of time, for example, a quarter of a year, and after calculating the orders then dlookup the commission rate? Read More...

Richard Rost
30 days ago

You'd need a loop. I'd recommend a Recordset.

Abraham Breuer
29 days ago

thank you for your reply
I will need to have patience until getting to Dev16...

Developer 27 Finished Upload Images Link
Richard Rost
2 months ago

In the can. Over 4 hours of stuff. Enjoy!

VBA in VSCode Upload Images Link
Abraham Breuer
2 months ago

Does anyone know about vsCode if it's a good place to write VBA code?
Have a look at the screenshot it looks to be a neet place for VBA...
What do you say about it???

Abraham Breuer
2 months ago

Richard Rost
2 months ago

Looks kinda neat. I haven't used it. That's more up Alex's alley. :)

Show 1 More Reply...

Create Sequential Numbering Upload Images Link
Despina Karayanni
2 months ago

Hello,
I am working on creating sequential numbering in an order database. Here is the problem I have. I enter data in the orderF form, and as soon as I close this form, the OrderT table has been appended (it shows the new entries), but when I open the OrderF again, it is blank (it doesn't keep the data). Can you advise why this may happen? Thank you in advance.

Despina Karayanni
2 months ago

In the meanwhile, I replaced the form, which functions as it should. However, I keep wondering how could this have happened!

Richard Rost
2 months ago

Very strange. Sounds like you got some errant code in there somewhere. Many times I've found solutions by just scrapping a form and starting over. :)

Show 2 More Replies...

Gregory Smith
2 months ago

You can use parts of the code below to add 1 to numbers. I have cases for serial numbered letters with numbers that look like G-0789. The code below uses DMax to find the last largest. The number part is separated from the text part so numbers can be incremented by one, then recombined. That code looks like:
DoCmd.Requery 'this helps with secondary additions while the form is still open.
DoCmd.GoToRecord , , acNewRec
Dim varResult As Variant
' in DMax(), use the FieldName and the TableName, respectively
varResult = DMax("GenOutSerialNum", "tblGenCorrOut")
' This next part separates (parses) the text from the number part,
' adds 1; then puts the parts back together with leading zeros.
Forms.frmGenCorrOut.[GenOutSerialNum] = Left(varResult, 2) & _
Format(Val(Right(varResult, 4)) + 1, "0000")

For our incoming correspondence, it looks like IC-45678
Me.[IC_series] = Left(varResult, 3) & _
Format(Val(Right(varResult, 5)) + 1, "00000")
DoCmd.RefreshRecord

Gregory Smith
2 months ago

Oh, this might help, too. To help me save, I suppose any entries on the Incoming Correspondence Form, I see, years ago, I added this little note that seems to have been working for me since 2017. A better solution is probably DoCmd.RunCommand acCmdSaveRecord. The note to myself in 2017 that just I noticed in the code to create the G-0789 style of numbering after creating a incoming correspondence record looks like:
Private Sub cmdOpenGSeries_Click()
' RefreshRecord to write to table to avoid Dirty Record
' RefreshRecord does same here as SaveRecord.
' DoCmd.RefreshRecord
' 2017-07-22 To save the IC record, replace the command above with:
If Me.Dirty = True Then Me.Dirty = False

DoCmd.OpenForm "frmGenCorrOut", acNormal

Added QR Codes to D27 Upload Images Link
Richard Rost
2 months ago

I added the QR Code video to Developer 27 as well. Probably not going to do too much more with them, but I do cover the new WebBrowser control which I haven't shown much in the Developer lessons (more in this is coming) and the URLDownloadToFile function which is completely new and lets you grab a file from a web site. Very cool stuff.

Developer 27 Started Upload Images Link
Richard Rost
2 months ago

I've posted the first 4 lessons to Access Developer 27

So far this is all FREE bonus material. It does not count towards your hour of new material for this class. That's coming shortly. Read More...

Juan C Rivera
2 months ago

This is awsome stuff, can we track who took the inventory out and will it be able to print hand recipts? Maybe even track location of Item - like Deck 5 area 51 top self... Ok I need to back off I get going and never stop.... Let me know when I gone toooo far.

V/r
Juan

Richard Rost
2 months ago

You done messed up A-A-ron. :)

Yes, if your goal is not to simply sell stuff, but you want to track where stuff is, you could add a location, EmployeeID, or whatever other information you need. That's all you. LOL

Show 1 More Reply...

ABCD Helper Tables Upload Images Link
Antony Lee
2 months ago

I'm absolutely loving the Helper Forms and Tables as it has massively streamlined two projects I have been working on for the last few years.

You previously stated that once the HelperT gets past several hundred records there could be issues. Read More...

Richard Rost
2 months ago

Nah, you're fine with 179. Heck, you're probably OK with a couple THOUSAND items in the HelperT. You start getting up to 10,000 items and noticing a slowdown, then worry about it. Only over a really slow network or online with a crappy connection would you have a problem with less than 1000 items.

Richard Rost
2 months ago

It's all relative to the speed of your computer and your network connection. If you notice someone getting slow, then you can address it then. Either upgrade their network connection or optimize the database. In fact, when I show how to move this puppy up to SQL Server, it won't be a problem because the server will only send the 8 records in the current combo box across the wire. Right now Access has to process ALL of the records (over the wire) and show you the 8 you need. A database server solves that issue.

Show 2 More Replies...

Saved Imports Upload Images Link
Vicent Mnaya
3 months ago

I can't get saved imports in RunSavedImportExport macro command, how can I solve this problem. I only get a list of saved exports but imports do not show up.
I'm using access 2016.

Richard Rost
3 months ago

Honestly, I haven't used that function in a LONG time. I'll have to review it and get back to you.

Alex Hedley
3 months ago

X22 and X24 if that's any help.

Show 1 More Reply...

Show Older Comments...
View in Table Format