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

Inputmask Upload Images Link
Abraham Breuer
28 days ago

Is there any way how i can change an input-mask with VBA?
i tried to make a logon and mask the users password, but i want to be able to have a on click event that unmask the password format to the original text Read More...

Abraham Breuer
28 days ago

Please ignore spelling i have tried it with inputMask

Kevin Robertson
28 days ago

Try
TextboxName.InputMask = "" to turn it off
TextboxName.Inputmask="Password" to turn it off

ComboBox Rowsource Upload Images Link
Abraham Breuer
2 months ago

Hi!

I have set up a form with 2 Comboboxes 1- CustomerCombo 2- SalesRepCombo,

then used the after update event:

Private Sub CustomerCombo_AfterUpdate()

SalesRepCombo.RowSource = "SELECT SalesRepID, SalesRepFullName, FROM SalesRepCustomerQ" & _ Read More...

Abraham Breuer
2 months ago

Show 3 More Replies...

Add Records One Time Upload Images Link
Abraham Breuer
3 months ago

in dev18 lesson 4, you learn how to edit new records to an unbound form using recordsets,
my question is if I want to add let's say 4 records at ONE TIME to my table with just one
Click on the add button, is this the same method or more complex?

Scott Axton
3 months ago

Abraham -
Check out this video and the extended cut for Add Records Automatically.
https://599cd.com/blog/display-article.asp?ID=1445

Richard doesn't use recordset in the videos but it is pretty straight forward and you should be able to loop through adding records with recordsets.

Richard Rost
3 months ago

Yep. A simple append query will work.

Many to Many Upload Images Link
Edwards Christopher
3 months 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 months 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 months 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 months 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 months 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
3 months 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
3 months 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
3 months 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
3 months ago

Richard Rost
3 months 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
4 months 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
4 months 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
4 months 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
4 months 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
4 months 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
4 months ago

Just found the link in the footer under Info ;)

Richard Rost
4 months 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
4 months 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
4 months ago

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

Abraham Breuer
4 months ago

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

Developer 27 Finished Upload Images Link
Richard Rost
5 months ago

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

VBA in VSCode Upload Images Link
Abraham Breuer
5 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
5 months ago

Richard Rost
5 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
5 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
5 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
5 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
5 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
5 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
5 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.

Show Older Comments...
View in Table Format