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!

Open Form by Combo Link
Nancy Proctor

23 hours 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

23 hours 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

22 hours 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 4 More Replies...

Richard Rost

22 hours 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

22 hours 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

22 hours 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

22 hours 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.

Repeat Customers Link
Michelle Seidelin

37 hours 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

36 hours 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

32 hours ago

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

Show 8 More Replies...

Michelle Seidelin

27 hours 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

26 hours 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

26 hours 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

24 hours 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

24 hours ago

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

Michelle Seidelin

16 hours 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 hours ago

Just use the COUNT or DCOUNT function.

Michelle Seidelin

2 hours 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

Highest of 3 Link
Joe Hood

2 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

2 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

2 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

3 days ago

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

Alex Hedley

3 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

2 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.

Customer Number Link
Michelle Seidelin

4 days ago

Hello Richard

I am wondering which lesson to watch the situation is when I started this database I use the auto number as a way of of tracking how many customers I have now I would like to change this is as the auto number is only for access as you have said in future lessons I'm wondering how do I set up a column to put on the form to show customer five which is an auto number at the current time is customer five Read More...

Alex Hedley

3 days ago

Michelle, you shouldnt use an autonumber as a Customer #, if you delete a record then add another your count wont match. autonumbers are internal for access to track relationships etc

There are many options you can use:
- Custom Sequential Counter Number
- Create Customer Codes
- AutoNumbers Good or Bad?
- Automatic Record Numbering

Michelle Seidelin

3 days ago

Hello Alex which one would you recommend for a single form tracking customers if you need VBA coding can you give me a suggestion on how to do that I'm not up to that yet

Michelle

Show 1 More Reply...

Enter Number Get Name Link
Leif Borgenlov

4 days ago

Hi. I want to create a table with the following: Number, Name, Date, Final Number, Song and Artist. Number and Name should be in the form of a dropdown list where I enter the number and get the associated name. I have tried in different ways but do not get it. From date to artist must be entered in advance. Hope you can help me with that.Best regards Leif

Richard Rost

4 days ago

Generally you only need to store one value in your table if Number and Name are linked. For example, if customer 555 was always "Joe Smith" then you just need to store 555 in your second table. This is the basis of a relationship between two tables (customers and, say, orders). I cover this in Access Expert 1 and 2.

The only time you would need to store BOTH values in your table would be if you could change one of them without the other. For example, if you want to enter a product ID and still be able to edit it's description. For example product 678 is "Cotton Sweatshirt" but you want to be able to change the description to "Cotton Sweatshirt, Large, Blue." But even in that case I would still recommend a 2nd options field. You do this with a DLookup and AfterUpdate event.

Join First, Middle, Last Names Link
Emad Al-Baghli

7 days ago

[First Name] & " " & [Middle Name] & " " & [Last Name]

I have list of people, some of them has only First Name and Last Name, If I did not put Middle Name MS access show me blank he did not joined the names together, how can I let MS access accept F M and L names or F and L name?

Alex Hedley

7 days ago

Cant remember which video its in but instead of & use the plus symbol and if the field is empty it wont add the space.

Richard Rost

7 days ago

Just do this:

FullName: FirstName & (" " + MiddleName) & " " & LastName

If MiddleName is NULL then you won't get the first SPACE character.

Show 1 More Reply...

Import Data Link
Richard Lanoue

7 days ago

I have an old database I made with a tone of data. Now that I've learned more things. I'd like to create a new database and have that old data imported to my new one. Didn't you have a lesson on how to do that? I'd appreciate the help thanx

Alex Hedley

7 days ago

Did you have a search on the site for import?

Are the table structures the same or are you wanting to split stuff up?

Query Criteria Limit Link
Abdul Nwiran

9 days ago

Please advise how many field i can filter by quiry
as i try build query i have issue is limited field i can filter by use Or,
Thanks

Richard Rost

8 days ago

Hi Abdul. I assume you mean how many criteria can you have in a query with the OR rows. I'm not sure exactly what the upper limit is, but I've never reached it, and I've made some pretty complex queries. I think the maximum length of the SQL statement behind the query is 64k characters. That's a lot. In any case, if your query is too complex or long, you can always break it up into multiple queries to simplify it.

Time Card Signature Link
Sharon Esdaille

9 days ago

Employees need to sign timecards. Once signed no edits should be made. I have a signature textbox. Once signed I disabled the signature box and locked form fields. When I close and reopen the timecard, the signature is gone, signature box is re-enabled and form fields are now editable. What am I missing? Read More...

Sharon Esdaille

9 days ago

I think I figured it out. I was using an unbound text box. Once I created a field on my table to hold the signature value, now it stays.

Sharon Esdaille

9 days ago

Okay. I didn't fully figure it out. The value stays now that I'm no longer using an unbound text box, but when I close and reopen the form, the fields that were disabled after update, are now enabled. I'm missing something else.

Show 2 More Replies...

Show Older Comments...
View in Table Format