Autofilling Checkboxes in a Form using another table

[tdw]

I have a customers table that has a couple of checkbox fields.
I also have an orders table with those same checkbox fields (though with a slightly different name).
This is not duplicitous as I only have those fields in the customers table to define a customer's preferences. Those default preferences may not apply to every order from them.

What I am having trouble with is getting the true/false info to pass on to the orders table via the form. The form's data source is the orders table. I have checkboxes on the form assigned to their fields on that orders table. What I want is when the user enters the customer, I want ("on update event") the checkboxes to autofill according the checkbox field of the customers table where the customer name matches what they just entered.

Am I missing something simple?

Thanks

[NeoPa]

...
This is not duplicitous as I only have those fields in the customers table to define a customer's preferences. Those default preferences may not apply to every order from them.
...

I suspect you mean duplication ("duplicitou s" is "deceiving" or "mendacious ").

Not particularly relevant but I thought I'd throw it in as a bonus ;)

PS. I understand your meaning and why this is a perfectly valid way of managing your data.

...
What I am having trouble with is getting the true/false info to pass on to the orders table via the form. The form's data source is the orders table. I have checkboxes on the form assigned to their fields on that orders table. What I want is when the user enters the customer, I want ("on update event") the checkboxes to autofill according the checkbox field of the customers table where the customer name matches what they just entered.

Am I missing something simple?

Thanks

Simple? Possibly not.

What you need to do is to grab the default values from the Customer record when, and ONLY when, a new order is added. Once the item has been set for an order, or afterwards changed on that order, it is not appropriate to overwrite that info with the defaults.

I would consider one of two approaches :

1. If the current form shows only order records from the one customer, then you can change the default values of the relevant controls either when the form is initialised, or when the filter is changed if you do change filter values after it's opened.
2. Otherwise (orders are shown that pertain to more than one customer), use the BeforeInsert event to trigger setting the values as they should be.

Hope this helps.

[tdw]

I suspect you mean duplication ("duplicitou s" is "deceiving" or "mendacious ").

Not particularly relevant but I thought I'd throw it in as a bonus ;)

PS. I understand your meaning and why this is a perfectly valid way of managing your data.

Yes you are right. :-)
Actually both could apply because sometimes I think Access has a deviant mind of its own.

Simple? Possibly not.

What you need to do is to grab the default values from the Customer record when, and ONLY when, a new order is added. Once the item has been set for an order, or afterwards changed on that order, it is not appropriate to overwrite that info with the defaults.

I would consider one of two approaches :

1. If the current form shows only order records from the one customer, then you can change the default values of the relevant controls either when the form is initialised, or when the filter is changed if you do change filter values after it's opened.
2. Otherwise (orders are shown that pertain to more than one customer), use the BeforeInsert event to trigger setting the values as they should be.

Hope this helps.

I have seperate forms for different uses. I have one form for entering new orders, and another form for viewing existing orders (does not allow editing). I also have a form for editing existing orders.

The only form that I want to lookup the customer's default preferences is the Add New Orders form. Basically, I don't want to have to click the two checkboxes every time I enter an order, I want them filled automatically based on the customer's default preference. But I want to be able to uncheck the boxes if, for some reason, they don't apply to a particular order.

An example of one of the check boxes: we have an option for title companies to have us put their logo on our survey plats. Not all title companies want that. It is also possible that they won't want it on a particular order, but will on most. So the "Logo?" check box should default to "yes" but not be stuck that way. The check box should NOT default to "yes" for certain title companies if they almost never want the logo.

The "Ordered By" field is a combo box on the Add New Orders form, which is populated by the Customers table (for convenience, as most of our orders come from regular clients). So when the user types or selects a company name that IS in the Customers table, then I want it to lookup the checkbox fields in the Customers table and automatically fill the checkboxes in my form (i.e. in the Orders table). I would like it to happen on the On Update event for the Ordered By field, and nowhere else (that way, like you said, it won't later reset any changes I make).

I am having trouble figuring out the procedure to make this happen.

P.S. There is really no filter applied on the form, other than it being a data entry only form, so it does not load existing orders.

[NeoPa]

I hear you, but other than the fact you have a dedicated form for adding orders I don't see anything I haven't already said. At the same time it doesn't specifically confirm what I said as it uses different wording.

I would say that the On Update event is probably not appropriate, as this will not display the change to the user before they submit the form.

I'm still not sure whether or not this could be done by setting the default values when the form is initialised as you only say there is no filter applied. You don't comment on whether or not the data can be guaranteed to be specifically for a particular customer.

If this approach is appropriate, it certainly is the best for your needs.

[tdw]

I hear you, but other than the fact you have a dedicated form for adding orders I don't see anything I haven't already said. At the same time it doesn't specifically confirm what I said as it uses different wording.

I would say that the On Update event is probably not appropriate, as this will not display the change to the user before they submit the form.

I'm still not sure whether or not this could be done by setting the default values when the form is initialised as you only say there is no filter applied. You don't comment on whether or not the data can be guaranteed to be specifically for a particular customer.

If this approach is appropriate, it certainly is the best for your needs.

I apologize for leaving out some of those answers. The form is used to enter several orders at once, from various customers. The user fills in each field for one order, then clicks on a button to basically "submit" the order to the database (I wasn't very specific about this, the Add New Orders form's control source is actually a New Orders table. This way if the user wants to begin entry of an order, but not have it finalized until gathering all the information requested by the form, they can still go back to a partially entered "new order" and submit it later.) When they click on the Submit button, it copies the record to the Open Orders Table, deletes the record from the New Orders Table, creates a job folder on our server, and creates a rich text format file with the order's detail in that job folder on the server, then finally prints the order (all this in one click). Then it refreshes the form so the user can enter the next order without all those "#DELETED" messages in the fields.

Due to the layout of the form, and the tab order, the Ordered By field would be entered before they get to the checkbox fields. So basically, if the On Update event checks those boxes for the user, the user doesn't have to tab over to or click on the checkboxes except for the rare occasion that it needs to be changed to something other than the default customer preference.

The problem with doing it when the form initializes, unless I am misunderstandin g the nature of this event, is that the form will be used, while open, to enter several orders in a row, not all from the same company.

Did that help? I hope I didn't leave out anything else.

[NeoPa]

I'll go in order as far as possible.

1. I assume you have the "#DELETED" problem solved alread, but if not you should know that a .Refresh will not do that for you. You need to use .Requery.
2. OnUpdate events occur for most of the controls of your form, as well as the form itself. If you have a control where you enter the customer account code, this is probably the best one to use for this as when that is changed is when you'd want the values to be reset to the customer default values.
3. Doing it when the form initialises is indeed not appropriate in your case. This was only suggested in absense of the information, now supplied, which makes the usage clear.
4. Yes, that helped. With that understanding the rationale that governs exactly what you're trying to achieve is now clear.

Let me know if this resolves your issue or whether you need any help implementing this logic.

[tdw]

I'll go in order as far as possible.

1. I assume you have the "#DELETED" problem solved alread, but if not you should know that a .Refresh will not do that for you. You need to use .Requery.
2. OnUpdate events occur for most of the controls of your form, as well as the form itself. If you have a control where you enter the customer account code, this is probably the best one to use for this as when that is changed is when you'd want the values to be reset to the customer default values.
3. Doing it when the form initialises is indeed not appropriate in your case. This was only suggested in absense of the information, now supplied, which makes the usage clear.
4. Yes, that helped. With that understanding the rationale that governs exactly what you're trying to achieve is now clear.

Let me know if this resolves your issue or whether you need any help implementing this logic.

Reading back over this thread I can see that you're postings are much clearer than mine. Yesterday I was trying to juggle a ton of work at the same time as posting my questions. Not that today will be any different! :-)

You're right, it's Requery, not Refresh. We don't use customer account codes as we actually treat each individual order as it's own account. We just happen to have some loyal clients.

What I'm having difficulty with (and maybe it's just because I'm so flustered with an overload of work) is how to (i.e. VB code, a query, whatever works) get the checkboxes to automatically fill. I thought about using a String or other Variable with a DLookup, but am not sure what the right kind of variable would be, nor what it should look for in a Yes/No (or True/False) field. "True" and "False, "Yes" and "No", "0" and "-1" ??? In brainstorming I come up with pieces of ideas but haven't been able to put them all together into something that works. Can I use a default value for the form that is an SQL statement that looks up the other table....etc etc etc etc....

By the way, I am posting this while half asleep...haven' t had my second cup of coffee yet. So if I just confused it further, just say "WAKE UP!" and I will repost the above with better wording later.

[NeoPa]

... We don't use customer account codes as we actually treat each individual order as it's own account. We just happen to have some loyal clients.
...

After reading through and enjoying your last post I'm so tempted just to shout "WAKE UP!", but I decided that the only important thing that's not clear is, if you have no account codes, how do you determine whether the customer is a revisit or not. What check can you use to determine this (If you simply compare the name string then I'm afraid I will have to shoot you)?

This is fundamental to understanding which course to take.

[tdw]

After reading through and enjoying your last post I'm so tempted just to shout "WAKE UP!", but I decided that the only important thing that's not clear is, if you have no account codes, how do you determine whether the customer is a revisit or not. What check can you use to determine this (If you simply compare the name string then I'm afraid I will have to shoot you)?

This is fundamental to understanding which course to take.

:::strapping on a bullet-proof vest::::
The Ordered By field is a combo box populated by the Customers table. The Customers table is really only there for two reasons:
1. fast entry of common customers into the field using the combo box to pop up the name as user types (I also use a "Subdivisio ns" table for the same reason for a combo box in the Subdivision field) and
2. So that the client company's name gets entered the same way each time for report purposes. Some title companies will have multiple offices, so for example "MBH Settlement Group, L.C. (Lake Ridge)" and "MBH Settlement Group, L.C. (Old Town Alexandria)" are the same company, different offices.

I took this database over, I didn't create it. I've done a heck of a lot of steroid injection into it though, it now has at least three times as many uses as before.
The Customers table used to be erased and repopulated everytime the database was opened. It would use data from the Ordered By field in the Open Orders Table (any Closed Orders are in their own table, so the Customers table would not be populated by any Closed Order data). The field on the form also did not used to be a combo box, but would autofill after the user had typed the first five letters, thus forcing the user to have the company name entered exactly the same way every time for report purposes. That was a pain in the butt because if the user had a valid reason for changing what popped up, it had to be done with the mouse and not the backspace key (because as soon as you hit the backspace key it would just fill it back in again!). So I made it a combo box. I also changed the Customers Table to keep all of it's information, rather than only reflecting currently Open Orders. My form now prompts the user to add the name to the Customers Table if they type something new (and also asks them to check their spelling if they think the customer should already be in the table).The user should not add the client if they are likely a one-time customer, such as an individual home owner (we are a land surveying company).

So.... what I have so far on the checkbox thing, is that if the user enters a client name that exists in the Customers table then it.... does nothing yet, but I want it to check the boxes where appropriate. There is no reason to have a one-time user in the list because, for one thing, their address is likely the property address, they won't have a logo (if they are a home owner) so obviously the logo check box will be unchecked, and the other check box ("Laminate Plat?") will always be yes for one-time customers. It's just the regular clients that may express preferences for using their logo in plats, and for laminating the plats.

:::ducking the bullets::::

P.S. we use a Job Number as our primary identifier for any project. Ordered by is to tell us who to send plats and bill to. Our filing system is primarily focused on Job Number, location of the property etc. because if we ever go back to that property, when owned by someone else, we really just care about the property information for doing the project. In other words, we track the project using the database, not so much the client, that's what we use Quickbooks for.

[tdw]

One more little bit of info:

We also know who our repeat clients are because we are a small company and quickly become intimitely familiar with our repeat customers. We get an average of 20-30 orders in a day, many times multiple orders from a single title company.

[NeoPa]

OK. No shots required ;) It would just have been easier to do and easier to explain if the records were linked in a reliable manner.

Essentially then, from your description of what you have, I would go back to point #2 of post #6 and use that approach. Obviously you will need to interpret exactly what is required and which control to use for yourself as it's done in such a non-standard way. Any more specific advice I give may be inappropriate for your situation as I'm mainly thinking of standard ways of doing things (and design etc).

Although I can't be more specific, I don't think this should be too much of a problem for you as you have full access to all the requirements (and the db of course).

[tdw]

OK. No shots required ;) It would just have been easier to do and easier to explain if the records were linked in a reliable manner.

Essentially then, from your description of what you have, I would go back to point #2 of post #6 and use that approach. Obviously you will need to interpret exactly what is required and which control to use for yourself as it's done in such a non-standard way. Any more specific advice I give may be inappropriate for your situation as I'm mainly thinking of standard ways of doing things (and design etc).

Although I can't be more specific, I don't think this should be too much of a problem for you as you have full access to all the requirements (and the db of course).

I agree that point #2 on post #6 is the way to go in this case. I just can't seem to get it to work. Do I use VBA coding (if so, what would it be) only, do I use VBA to launch a query (if so, what kind?) etc. What I've tried so far fails to affect the checkboxes.

[NeoPa]

If you can explain what you've tried (bearing in mind I'm working disconnected from your database) I will see if I can help. I don't have enogh info to give any more specific advice at the moment.

[tdw]

If you can explain what you've tried (bearing in mind I'm working dosconnected from your database) I will see if I can help. I don't have enogh info to give any more specific advice at the moment.

If you see what I tried, then you really might decide to shoot me!!
Actually, I scoured the help files for things that seemed to apply, and this is what I thought would do it:

    '   Autofill Logo and Laminate checkboxes
        
        If DLookup("[uselogo]", "cust", "[cusname] =" _
                    & Me.ORDER) = True Then
            Me.LOGO = True
        End If
        If DLookup("[laminateplat]", "cust", "[cusname] =" _
                    & Me.ORDER) = True Then
            Me.LAMINATE = True
        End If
    End If

cust = the customers table
uselogo = true/false field on cust table
cusname = customer name field on cust table
Me.ORDER = the "ORDER" field (for Ordered By) on the Add New Orders Form, it is this field that has the After Update event containing the above code.
laminateplat = another true/false field on the cust table
Me.LOGO = the checkbox on the form, with control source being a true/false field in the SC_OPEN table (the Open Orders Table).
Me.LAMINATE = the other checkbox on the form, control source being a true/false field on the SC_OPEN table.

The above code does not give me any errors. It just doesn't work.

I also thought about using strings with queries instead of dlookup (dlookup has always boggled my mind a little, I haven't quite grasped where it's appropriate and where it's not), but I don't know what KIND of string would be right, considering it's a true/false value, and not a text value.

[NeoPa]

That's starting to make some sense.

What would be helpful though, for me to understand where this code fits in (or tries to), is a list of relevant controls on your form and the names and types of the relevant fields in the relevant tables.

I generally use the following format as it makes it MUCH easier to work with when shown clearly.

Table Name=[[U]Cust[/U]]
[I]Field; Type; IndexInfo[/I]
CusName; ???; PK
UseLogo; ???
LaminatePlat; ???
...; String
...; Number
...; Date/Time