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 can post here. If you'd like to become a student, all you have to do is enroll in one of my courses, which you can do for as little as $1. 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.

NEW: Access Developer Students, make sure you subscribe to the Developer Student Forum.

Click Here to Post a comment and start a new discussion

Please read the FORUM RULES before posting.

Create Default Settings Upload Images Link
Brent Rinehart
3 hours ago

I know Richard has mentioned this before and maybe I should know by now how to do this, but it's not clicking how I should do it. I want to have a table that would store default settings for various forms. such as default path locations, default user choices specific to a user on their front end copy of their database, and more as they come down the road. Would I link to the table or do something like a DLookup to the table to get the value? I would think I would have to go this route because the table would hold settings for multiple forms. Read More...

Scott Axton
99 minutes ago

Brent you just described exactly how to do it.
Create your table.  It only needs one record.  I call mine dbSettingT.
Add a field in for each setting you want to store.  What ever you want - FilePath, TaxRate, etc. Pretty much any thing that would be "global" to everyone.  The advantage is this is persistent from session to session.

For settings for an individual you could make a "UserSettingT" which would have the user preferences.
You could allow them to choose things like form colors, default printers, etc.
This table would have a record for each user.
Of course this would require that you have individual user logons and a ton more in your code to allow for user settings.

In both cases your DLookup would find the values needed at the time that they are needed.
The other alternative would be to set global variables in a global module when you start up your db.

Pointers to specifics?  Pretty much all of this web site.  The   Lego Pieces   are scattered around.

Richard Rost
97 minutes ago

There are so many different ways you could implement this. A simple DLookup from a table would work. If you want to have it unique per user, then add username as a field. If each form has different settings, then just add FormName as a field. It's just really name/value pairs.

Show 1 More Reply...

Relationship Delete Upload Images Link
Richard Lanoue
6 hours ago

I am having difficulty deleting a relationship field in Access. I created a field and wish to delete it but it says I have to delete the relationship. I cant fint it in Database tools. What do i do? Read More...

Kevin Robertson
5 hours ago

Database Tools > Relationships
If you don't see the Relationship you are looking for click the All Relationships button.

Payment Screen Upload Images Link
Marily Cruz
22 hours ago

Hi Richard, I work for a government agency where we contract a variety of vendors to perform a variety of tasks at any of the housing units we own. We use a 7 digit code to identify labor and materials and type of vender. For example 4437 labor & 757 electrical. Each housing building has its own 8 digit code that looks like 20-178-111. We must show all those numbers when we prepare a payment request to send to finance. How can I make a payment screen that would automatically enter all those codes and at the same time deduct the payment from the contract's balance? Any suggestions??

Scott Axton
21 hours ago

I highly recommend that you look at, and consider ordering, the Access Work Order Seminar

Your question sounds like a PERFECT fit for this. You can watch the first two lessons first for free, before deciding.
Plus as a Learning Connection and Gold member - don't for get to use the link(s) Richard sent you to take advantage of the substantial discount your eligible for.

When I first went through this, I was way over my head but Richard explains things so well I was able to get it running for one of my business partners with out too many issues. It was certainly a game changer for me.
---

Regarding the labor and materials etc I would create a "product table" to store those as items. Then just add them like any other order detail for an itemized list for your invoice (order)

Scott Axton
21 hours ago

Wow sorry for the typos!

forget not for get.
you're eligible - not your

Show 1 More Reply...

at delivery amount date Upload Images Link
Bruno Segers
28 hours ago

Hi Richard, I would like to add the delivered amount and date to my delivery status in my orders, which in turn will replenish the stock correctly. If everything is delivered at once, it will still be possible to work out. But if the goods arrive in 2 or 3 times, this is a other question for me. How do I best work this out? Read More...

Richard Rost
105 minutes ago

I haven't covered partial shipments yet. It's on the list. Coming soon. Hang in there.

Replicate Inventory Upload Images Link
Walter Hamilton
30 hours ago

I'm building an inventory management system and need to be able to replicate systems. We have several systems that are made up of several boxes which each contain numerous component items. I have three tables set up: Read More...

Adam Schwanz
27 hours ago

So you want to be able to choose an EndItem to replicate, and have it also replicate the associated Containers and Items?

Access Developer 19 Nested Recordsets

Walter Hamilton
26 hours ago

Adam,

Thanks. I'll check it out now. I really appreciate it.

Show 1 More Reply...

OpenForm Nested Subforms Upload Images Link
Elaine Heltman
31 hours ago

I want to create double-click event using docmd.openform to open a form with nested subforms. How do I construct docmd.openform with 2 conditions: LesseeID from the main form (MatterDetail_F2), and the LeaseID from the subform (TestLease_SubF). Read More...

Adam Schwanz
28 hours ago

Are the forms not linked like a parent/child? You should be able to just open the main form and have the child (subform) fill in automatically. Otherwise if they are not linked, an image could help see what you're trying to do.

Elaine Heltman
26 hours ago

Yes, they're linked. Here's the whole process: Via a search form (thank you Search Seminar), double click on a "Well" result and the WellDetail form opens, showing the Well details (whose Record Source is a query that includes LesseeID and LeaseID) and also showing the user the related Lessee and the related Lease. Lessees and Leases have a 1 to M relationship, as do Leases and Wells. From this WellDetail form the user can doubleclick on the Lease and another form opens with more detail - easy. But, when the user doubleclicks on the Lessee, I want a different form to open (the nested form) that shows the Lessee (in the main form) and opens to the Lease (subform) as was shown in the the WellDetail form, not just the first Lease in the subform, which are in numerical order. I'll attach 2 snips that I hope will clarify. I appreciate your help.

Show 6 More Replies...

Elaine Heltman
26 hours ago

Scott Axton
22 hours ago

OK here we go......

First your Docmd.OpenForm is way outta whack (Well, Duh Scott, she just said it didn't work) because of incorrect concatenation.  I know, I know - bear with me.

The reason you get the type mismatch is because you are trying to put text into an id field.
If you just had:  DoCmd.OpenForm "MatterDetail_F2", , , "LesseeID=" & LesseeID
That would work.
Now yours has:   DoCmd.OpenForm "MatterDetail_F2", , , "LesseeID=" & LesseeID AND ...
Access is trying to convert the where condition to  Lesseeid = LesseeID AND  instead of substituting the value of the variable - LesseeID = 9 for instance.  LesseeID AND is text and Access tries but says wait I can't do that. Text into a number field doesn't work.  So... type mismatch error.
Get it?

So to get that to work the proper concatenation you would have to do:
DoCmd.OpenForm "MatterDetail_F2", , , "LesseeID=" & LesseeID & " And LeaseID=" & LeaseID

Note the Space before the And   - " And

Scott Axton
22 hours ago

Regarding your forms.  While some what helpful - a capture of your form in design view is more helpful to us for seeing what is happening behind the scenes.

Lessee and lease are awful close.  Double check spelling.  I misread those at first.

If you click on the subform once and go to properties - make sure the relationship is correct between parent form and the subform.
See the example capture from the Blank template below.

Scott Axton
22 hours ago

Richard Rost
5 hours ago

Concatenation... I tell ya... those quotes and ANDs will get ya.

Dlookup Checkbox syntax Upload Images Link
Teeka Leone
2 days ago

I haven't found an example ANYWHERE of how to get the value of a checkbox using Dlookup so wondering if it's not supported in Access? The control on the form is a checkbox which matches the datatype on the table I'm looking it up from. On the properties of that checkbox on my form I have: =DLookUp("SSOchk","EnvironmentsT","EnvID =" & [Forms]![DeploymentsF]![EnvID]) I have the code in the default value property of the control because this is a bound field. I have the control name in the control source property as I need it to get the value from the environments table via DLookup, but then write that value to the deploymentsT. If there's a better way of doing this, I'm all ears!

Teeka Leone
2 days ago

Never mind. I think my testing was faulty. DLookup doesn't seem to work when the field is bound. The code only works when the form control is unbound and the DLookup Code is in the control source property. Correct?

Richard Rost
109 minutes ago

Nope... actually it's the other way around. DLookup looks up a value FROM A TABLE OR QUERY, so the field NEEDS to be bound. You can't DLookup an UNBOUND field on a form. But you wouldn't need to. You'd just say =Forms!FormName!Field

Control button with long text Upload Images Link
James Beland
2 days ago

I have Windows 10 Pro. Using Access 2013. I have a field named "LastName" in EmployeeT. I tried to build a list to access from a button on a form. I've done this a lot but now with 13, I'm having problems. I did notice a few problems using short text Vs. long text for this. Now I can't even see my query list when building a button.

Richard Rost
111 minutes ago

James, I need a lot more information to help you. Please explain in more detail.

Search combo boxes Upload Images Link
William Dowler
2 days ago

I have several combo boxes on a form. For simplicity sake let's say one is: 1 Bill, 2 Sue, 3 Thomas with the id (1,2 or 3) being the bound field that is stored in the table. I want to use the find/find next functionality to show records with a particular value in order as I press the Find Next button (say all with the value "Bill") so that the user can navigate through the table, via the form for all the records with "Bill" in a particular field. The search doesn't find records with "Bill" it will only find the records with "1" the bound field which is information the user will not have. How can I set this up to find the text value instead of the ID value? Many thanks.......

Adam Schwanz
2 days ago

Make An Expression in the query like ComboFix: DLOOKUP("RealName","TableName","ID=" & ID) then when they search on that field it will find it.

Adam Schwanz
2 days ago

Or bring that second table into a query and bring in the actual name. Either way.

Show 6 More Replies...

William Dowler
2 days ago

Adam,
What query do you refer to? Thanks for the response by the way.

Adam Schwanz
2 days ago

I was saying you should make a query with the table you're using currently and base the search on that. Or just change the recordset (assuming you're using a recordset for rs.findnext)

But you know this may be even easier, you could just base your search on the second column. Whatever you're using now to search with just change it from ComboBoxName to ComboBoxName.Column(1) assuming the actual string value you want to search (Bill, Sue, etc) is in the second column (it starts at 0). Lots of options here.

If you can't get any of them work/make sense then can you link your current code you're using for the search/find next and I can give you more help.

William Dowler
37 hours ago

Adam,
I am just using the FIND option on the Home menu.
Is there a good vba option that would replace that?

Adam Schwanz
33 hours ago

OH, there's a ton of different VBA searches/filters. You're not going to be able to do anything special with that built-in find tool.

I'd really suggest the Access Search Seminar

This might help you too for a non vba option Filter Records

Richard Rost
2 hours ago

The find dialog box (CTRL-F) will definitely search inside the other columns of a combo box as long as you specify the right field and search type. For example "current document" to search all fields, and "any part of field" to search the whole string.

Richard Rost
2 hours ago

Buttons Dont Work Upload Images Link
Agatha Miller
2 days ago

I am following the genealogy seminar and I'm on Lesson two at 48 minutes into the lesson. I was able to add the command buttons for mother and father but I can't get the command buttons to work in the child sub-form to add mother or father. I have re-watched the video several times and retraced my steps but the buttons don't work. My buttons don't even open the PersonAddF form the way it does in the video.

Kevin Robertson
2 days ago

Saying your command buttons don't work isn't enough. We need more information.

Forum Posting Rules
Context

Agatha Miller
2 days ago

I don't know what else to tell you. I copied the button from the Add mother button just like Richard shows in the video, then I enter all the coding Richard shows and after saving my work, I close my PersonF and reopen it and try to add a mother in the child sub-form but nothing happens. there is no error message or anything. It just doesn't work.

Show 3 More Replies...

Show Older Comments...
View in Table Format