Microsoft Access Forum

Welcome to the Microsoft Access Forum. Access is by far our most popular topic here at Computer Learning Zone. This forum is for everyone to read but only students (paid customers) can post here. If you'd like to become a student, all you have to do is enroll in one of my courses. After you create a logon, you will have full access to the Forums.

Subscribe to this forum and receive an email update whenever new posts are added. You will also be notified by email if any conversations you are involved in receieve new comments. Yes, even non-students can subscribe to update notifications.

Having problems with your Access database? Before posting your question, check to see if it's already been answered. You can use the Search feature to look for keywords. There's also the Access Troubleshooting Checklist which covers solutions to many common problems. If neither of those things help, make sure you post exactly what you're doing, what the specific error message is, what you've tried, and any other details we might need to help you. Don't just say, "I get an error." Tell us what the error is and what you're doing. We're not mind readers. I wish. LOL

Click Here to Post a comment and start a new discussion

Set Focus Link
Michelle Seidelin
2 days ago

Hello Richard

Just can't remember which lesson or tech help video you showed how to set focus to a certain field e.g. first name now that I have the DSUM working to get the next number instead of having the auto number is my customer number which you course which was caught way back at the beginning on level I now that I am trying to make it better I need to know which lesson to give the focus to the first name field is there is another field before that I don't want to set the tab to this one as I need to be able to use the first field when I need to Read More...

Richard Rost
25 hours ago

Use either Control.SetFocus or DoCmd.GoToControl

SetFocus / GoToControl

Michelle Seidelin
14 hours ago

Hello Richard I notice the set focus shortcut you have given me is to develop the 13 I am only up to develop a six as far as purchasing and only up to expert 12 as far as building can you suggest an easier way of setting the focus I tried to fiddle around with the tab order and the tab order seem to change okay I wanted to start on first name but when you go to a new record using a new record button which is the way it set up at the moment on the customer table/customer form there is no sign of the flashing cursor until I push the tab key any suggestions thanks for all your help

Michelle

Show 1 More Reply...

Record Source Disappears Link
Abraham Breuer
2 days ago

Hi Richard!
I am having problems when selecting my record source for my form, I select a source but it does not appear to work it just stays blank
any Idea what's going on???

Richard Rost
2 days ago

Sounds like this falls under "weird stuff" to me. Run down the Troubleshooter.

Abraham Breuer
2 days ago

the solution was: Restart Access. Close and restart your database!!!!
thank you very much

Show 1 More Reply...

Customer Numbers Existing Link
Michelle Seidelin
2 days ago

Hello Richard and Alex

Is there a way of inserting customer numbers automatically from the description you given at this website https://599cd.com/tips/access/incrementing-your-own-counter/
Read More...

Richard Rost
2 days ago

I don't think I covered how to do this for existing customers. It would be a little more challenging, probably beyond a TechHelp video. A simple DMAX won't cut it for existing records. Probably best done with a Recordset loop. I'll add it to a Developer class soon.

Michelle Seidelin
2 days ago

Hello Richard

Thank you I fought a bad later on and be easy to do it by just putting the numbers in even though there is 400 numbers it shouldn't take too long to type 1 to 400 in is it how many customers I have my business is nothing like yours is this the small home business

Show 2 More Replies...

Separate Username from Email Link
Emad Al-Baghli
2 days ago

I have a lot of E-mails I want to extract "user name", for example first column "emadb@gmail.com" and the other column will be only "emadb"
Thanks

Richard Rost
2 days ago

One of my Gold Members just asked me this question a few days ago. It will be a TechHelp video shortly. It's basically some string functions: InStr, Left, Right, etc. Hang in there.

Richard Rost
60 minutes ago

Here you go: Separate Username from Email Address

Access Backup Itself Link
Nancy Troutman
3 days ago

I really like how Access runs after splitting the database. But I hate the weekly chore of opening each of the backend databases to create a back-up. Online instructions only back-up the front-end. Is there any way to tell Access to back-up itself automatically, lets say every Friday at 10pm or something like that? With the date as part of the back-up name.

Richard Rost
2 days ago

Yeah, you can have Access backup the back-end files. I actually have a routine that I wrote for my own database that I am planning on releasing as a template very soon. I wanted to let it run in my office for a few months to make sure there were no glitches. It's been running fine for about 6 months now, so I'll release it soon. Essentially as long as none of the back-end tables are IN USE, it works fine. Otherwise the file is skipped. So you need to make sure everyone is out of the database.

Alex Hedley
2 days ago

You could also create a quick script that runs on a Windows Task Schedule:

You would need to amend the following to copy not move but the initial principles are there
Rename and archive a File

Gantt Chart Planner Anyone Link
Richard Rost
3 days ago

Anyone interested in a Gantt Chart Planner for Access? Good for scheduling and project management.

Open Form by Combo Link
Nancy Proctor
4 days ago

Hi Richard,

I'm trying to figure out how I can open a specific form based on a selection I make from a combo box.

I'm thinking I can somehow build a macro using the IIf function somehow, but I haven't been able to figure it out and it's probably better to write it in code builder using VBA. Do you have any prerecorded lessons that I can purchase that explain how to do this? Read More...

Richard Rost
4 days ago

How is the form that's opened determined? What are you selecting in the combo box? If it's just that you want a list of forms in the combo box, then that's easy. Otherwise, yes, I would do what you suggest: make an IF THEN block (or a SELECT CASE statement) in VBA and open the appropriate form. Tell me exactly how this form is chosen and I'll help you out. I haven't done anything like this in any of my classes, but it would make a cool TechHelp video.

Nancy Proctor
4 days ago

In my table [TBL_Patients], I have a combo box field named [Cover_RX]. For context, Cover Rx is a medication discount program. The combo box selections for that field is either "Yes" or "No". Basically, I document if each patient enrolled into the discount program or not. On the after update event, if "Yes" is selected, I'd like the popup form [FRM_Cover_RX_Yes] to open. That form gets it data from the table [TBL_Cover_RX_Yes]. There are several fields in that table allowing me to document information that a pharmacy would need so the patient could get his/her discount on meds. If "No" is selected, the popup form [FRM_Cover_RX_No] would open with a field allowing me to document why the patient does not have this discount.
I use this in the "where condition =" line in my macro to ensure that info is documented on the right patient: ="[Patient ID]=" & [Patient ID]

Show 5 More Replies...

Richard Rost
4 days ago

Yeah this would be a simple AfterUpdate event:

If ComboValue="Yes" then
   DoCmd.OpenForm "Form1"
else
   DoCmd.OpenForm "Form2"
end if

Assuming your combo box is storing the text value "Yes" and not an actual Yes/No field, which case you'd change it to True/False. Is this data stored in the same table as the patient info, or is it a different table? I ask because you might be better off just showing/hiding relevant fields instead of opening two entirely different forms.

Nancy Proctor
4 days ago

my combo box is actually storing the words "Yes" and "No". I prefer that over a check box (True/False). The Yes/No is in the same table as the patient info.

The Cover Rx details is in it's own table

The reason for no Cover Rx is in it's own table.

So, 3 tables total. I've always been under the impression that it's not a good idea to have a table with a bunch of empty fields so that's why I created 2 different tables for the cover rx details describing the yes/no. not sure if that's the best approach.

In my relationships, the patient table and the cover rx yes table are connected via a one to many and the patient table and the cover rx no table are connected via a one to many.

Richard Rost
4 days ago

The way you're doing it is fine. If it's just ONE empty field, or a couple, that's not a big deal. For example, let's say you have an OrderType which is either Online or Shipped. If someone selects Shipped you could just hide (Visible=FALSE) the Address fields. Sure, you'd have a blank couple of fields for each customer, but that's not too big of a deal. If your "No Cover Rx" for example is just ONE field, there's no reason to have a separate table for it. Just my opinion though. If what you have works, keep it.

Richard Rost
4 days ago

And of course, I've got TONS of Developer lessons available to teach you programming. If you just want a quick intro, start here: Intro to VBA. It's free.

Nancy Proctor
3 days ago

I wasn't sure how to incorporate the where condition "="[Patient ID]=" & [Patient ID]" into the code that you wrote, so I created a macro that mirrored what you wrote so I could add the above where condition. yes...it's time I take I start learning VBA.

Repeat Customers Link
Michelle Seidelin
4 days ago

Hello Richard

In my customer T I have a field called repeat customer which is a checkbox yes no field what is the best way of showing this in a form I was thinking it would be similar to how we set up groups would this idea work or is there a better way of doing this this would be a form on its own no sub form required what I am thinking it would be the customer I and the field called repeat customer is I said which is our yes no field Michelle

Alex Hedley
4 days ago

Which form are you wanting to show this in?
A CustomerListF or a single CustomerF?
Who determines they are a repeat customer?
If its just a checkbox, like you say, why cant you just have that on your form?

Youve mentioned sub form - what data are you wanting to show here? A sub form is for showing related records, for example customer contacts. A customer calls and you add notes about that.
Being a repeat customer is a single thing.

Richard Rost
4 days ago

Yeah, a simple checkbox would do. Why invent the steam engine when a hammer fixes the problem? :)

Show 11 More Replies...

Michelle Seidelin
4 days ago

Hello Alex and Richard

After writing that message yesterday I realise after I closed the computer down I didn't make it clear that I was using a query and there was a checkbox on the form and I was planning to feed the new form to show the return customers with this query on us continuous form thank you all let you know how I get on

Michelle

Michelle Seidelin
4 days ago

Thank you Alex and Richard I discovered a few minutes after reading your message what I was doing wrong the problem was I was trying to use another form rather than building a new one is sooner I did what you suggested and of course turning into a to newest form which was my plan ever rings working great with that thanks again our students wouldn't know where to turn if it wasn't for your support

Alex Hedley
4 days ago

So you'd like to take Criteria from a form to use as a value in your Query which feeds a continuous form?

See this tip: Get a Value from an Open Form Field

Michelle Seidelin
4 days ago

Thank you for your help I have figured out what I was doing wrong now just can't remember how to put account of how many customers I have as far as return customers I know it's something to do the count function but this can't remember the coding and not sure where to find it on the website what lesson do you look at and for some reason it won't let me set vertical only or should I say horizontally only to show all my cup repeat customers I work this out I'm sure but if you've got any suggestions as I've got two forms I like to get account of how many people there are in different forms remember this is not our son function it is not our numbers on trying to figure out

Alex Hedley
4 days ago

Aggregate Queries could be used.
Or do you want a CROSSTAB If you want a grid with amounts per thing.

Michelle Seidelin
4 days ago

Hello Richard and Alex

What I want to figure out can't remember was luckily where I heard about it but what I want to do is put a total at the bottom of the page showing how many customers are return customers this is not something that SUM I know we looked at count somewhere but can't remember where and how to do this when you are not trying to get account of people's names/lines rather than a total e.g. 20 20 = 40

Richard Rost
3 days ago

Just use the COUNT or DCOUNT function.

Michelle Seidelin
3 days ago

Hello Richard

I used count on both of the different forms one of them works because it had a field called full name but the other one did not I am guessing is because the field I was trying to count was customer ID putting in = (count customerID) just showed a blank form I think that is because that field is in a form that shows groups and of course it only showing customerID as I could copy that but it didn't recognise the count function it came out blank do you have any other suggestions on what to do with this form Michelle of New Zealand

Michelle Seidelin
3 days ago

Hello Richard

It doesn't matter how many times I type in a code looking at your video about Dcount what I'm trying to do is put a new field into my database to show the number of customers and get rid of the field on the form which is actually showing it at the moment from customer ID the auto number I have tried and tried getting to the stage where I think I will just leave it as it is unless I can get someone's help it's are difficult typing one-handed and making sure you have the commas in the right places I know this is simple for you is just getting to the stage where I don't know where to go next

Alex Hedley
2 days ago

DCOUNT
Syntax: =DCOUNT(Field, Domain, Criteria)
Example: =DCount("*", "CustomerT", "CreditLimit=50")

For you something like:
=DCount("*", "CustomerT", "RepeatCustomer=TRUE")
This assumes your Field for "Repeat Customer" is named "RepeatCustomer" and it's a checkbox so you can say True or False

Michelle Seidelin
2 days ago

Thanks everyone I have it working now still need to find a way of putting a total at the bottom of our subform which will change when categories change this was in level 6 I think when we created the groups e.g. hardware, software thanks again for all your help now just working on another thing

Highest of 3 Link
Joe Hood
5 days ago

Hello Richard,
I have 3 Number fields on a form. I would like to add a text box that defaults to the highest of those 3 fields. Have not been able to figure a way to do that. Any suggestions?
Thanks, Read More...

Richard Rost
5 days ago

How about an IIF function:

=IIF(Field1>Field2 AND Field1>Field3,Field1,IIF(Field2>Field1 AND Field2>Field3,Field2,Field3))

Should do the trick. :)

Joe Hood
5 days ago

Thank you Sir! Worked Perfectly, your help is very much Appreciated.

Joe

Show 1 More Reply...

MS Word Mail Merge Link
John Orcutt
6 days ago

using MS Word mail merge instead of Reports in Access
Do you ever cover that in your classes?

Alex Hedley
6 days ago

There's an old tip
There's also an old course Word 201 which goes into much more detail, it may look a little different as it's using old versions of the apps.

You can always use the search to see where it's mentioned on the site: Mail Merge

Richard Rost
5 days ago

Yeah, I covered that EONS ago. I'm preparing to re-record all of my old Word lessons. Hang in there another week or two.

Show Older Comments...
View in Table Format