Solved: AUTO OR MANUAL POPULATE

[GaryB]

Hi,

I need some help, please. I have an estimating form that has a drop down menu for account numbers. When I select an account, it automatically fills in all the info for name, address, phone, etc... That part works fine, but, what I also want to do is to be able to manually enter the name phone etc... for someone who isn't a current customer for what may be a one time estimate. Because the form is tied to a query and accesses a linked table, I seem to be locked out of manually entering information. If anyone has some insight on this, I would be most appreciative.

Thanks

GaryB

[XLGibbs]

Most queries are generally opened as non updateable recordsets.

You will likely have to build an Update statement into the event to where if you change a record, the appropriate record will update in the table..

something like:

[VBA]

Update [TableName]
Set [Fieldname] = TextBox1.Text Where [UniqueIdField] = ComboBox1.Text

[GaryB]

hmmm,

Would this cause the manually entered data to become a new record? If so, that probably wouldn't work since the customer table is linked to another database that makes it uneditable. I may not be able to do what I want to do. The other data base is an estimating program we purchased and I don't have any access to the progaming in it. It was built using Access 97. I have attached a working sample of one of the forms I am using.

Thanks

Gary

[XLGibbs]

No the update statement updates and existing record, which is what the WHERE clause in the set statement identifies...

I may have misunderstood your post though, I thought you wanted to update the record by typing in the phone number manually?

You can try and set the query properties the form is linked to as updateable...

[GaryB]

What I am trying to do is create an either/or type of situation where the record uses and established account or I can manually enter customer information. Right now I can only use existing accounts.

Gary

[XLGibbs]

Hmmm, sounds like almost need two forms that are identical. One with a feed from the query for existing, and a one linked to a hard table in the database for where you can add these records for the one time deals. Certainly made more difficult by the linked tables that you can't update...

You can have both of these identical forms load, and toggle their visbility with a button click...each form would work independendtly.

[GaryB]

that is certainly one way of going about it. What really bugs me is the other estimating program, the one we bought, does this. I can either use and existing account or just enter what I want. It's like once it dumps the info on the form it severs the link to the table that holds the accounts. It's driving me nuts.

Gary

[nullZero]

You can do this with code. Let's say you have "new customer" in your combo. Then when that value is selected you can run some code that sets your form record source to your table instead of your query. That will work for a single table, but if you need to update multiple tables then bind the form to nothing... you'll probably have to loop through all your controls and unbind them too. Once that's done you can use docm.runsql() to do your inserts into your tables.

hth,
nZ

[GaryB]

Hi NZ,

The query has two tables in it ( see attachement in earlier response) one for customers and one for the info for the form. Since my coding abilities are extremely limited, I'm not totally clear on how to bind to form to nothing. From my limited understanding for the fields to appear in the form at all it has to be bound to, at minimum, a table or am I totally off base on this. Ok, I admit it, I'm confused.

Thanks

Gary

[nullZero]

Hi Gary,

I downloaded your sample and I'll take a look at it today. When I'm done I'll post back with a sample of what I'm talking about. I did take a peek at your form and was very excited to see it's a print estimate form... I used to be in print estimating and print buying.:)

nZ

[GaryB]

It's an estimating form for our Indigo Digital Press. We are a full service printing company in Northern California, the S.F. Bay Area, and have the usual conventional presses as well as the digital press. When we got the digital press 4 years ago, I had to create an estimating program for it. What I uploaded is a small part of a much bigger data base that also tracks every job in the plant etc....

Gary

[nullZero]

Hey Gary,

I took a look at the form, and re-read your previous posts and it got me thinking... is there any reason why you couldn't have a customer 'x' that exists solely for one time only quotes? Seems like that would solve your problem in the simplest manner.

About your form... because of all the calcs your doing I'm not even sure that my original idea will work. You have a lot of business logic built into the form and it would take me a little while to figure out how to do what I was proposing. Probably more time than it's worth.

If you do still want to continue with your original idea I'd be happy to help. Just PM me your email so I can put together all my questions in an email.

hth,
nZ

[GaryB]

HI nZ,

It is a complicated form. I do have the x customer already set up, but, when the next one comes along then you lose the record of who the previous quote went to. I was thinking about this last night and I think the easiest solution to this would be to create a 2nd set text fields for name, phone, etc.. that is tied into the form table and then either use the Account for automated or use the 2nd set for manual. not the prettiest way to do it, but, workable. What do you think? I don't want you to spend a lot of time on this, that wouldn't be fair.

Thanks

Gary

[GaryB]

This is going to sound kind of goofy, but it works for me.

I created another set of text fields with custname, phone etc?

Made the fill and the outline transparent and placed them exactly on top of the original fields that are controlled by the account box. Then I changed the tab order so that account was 0 and the 2nd custname field was 1 ,so, if there is an account it automatically fills the fields and if I use the x customer account, which has no name or address info in it, it automatically goes to the 2nd custname field and I can manually fill it all in. Since the 2nd set of fileds is transparent when I use the automated account it shows right throught.



Thanks you all who helped me with this.



Gary

[XLGibbs]

That makes sense gary, not goofy at all. I have a number of similar routines in VB forms that do the same thing...basically I toggle them enabled for new records, otherwise, they are invisible and disabled...

I took a look after NZ stepped in but it seemed things were well under control.

If you can, and if applicable, just mark this puppy solved for us...

PS. Null Zero, nice to see you jumping in here..Mr.Excel not given you enough action?

[nullZero]

PS. Null Zero, nice to see you jumping in here..Mr.Excel not given you enough action?

Thanks XLGibbs ~ Yeah I'm hooked? I've been posting here, Mr. Excel, and UtterAccess. It's getting to the point that I'm constantly looking over my shoulder at work to make sure nobody sees what I'm doing. They would say that I'm not being productive but I actually feel like these little mental exercises help me to be a better coder and therefore more productive.