Can you cause a field in a form to do a lookup and post?

[Hey Lucy]

I've asked about this before but am still confused as to what to do.

I am creating a customers and orders database for a client.

This client has customers that are companies and customers that are individuals.

Now, my method may be completely incorrect, but here's what I did:

I created the Customers table with the following fields:
Customer ID (auto-number field)
Company (Name of Company if Customer is a Company)
Contact (LN, FN of Company Contact if is Company)
Customer (LN, FN if customer an individual)

So, if the Customer is a company, the company name and contact full name is entered in their respective fields.
If the Customer is an individual then the customer full name is entered in the respective field and Company and Contact would be left blank.

Next, I created an Orders table with the following fields: (AND here is where my problem lies.....)
OrderID (auto-nuimber field)
CustomerID (looked up from Customers table)
Company (combo box query)
Customer (combo box query)

So, when I create an Order form from the Order table, I am going to get a drop down list for the Customer ID, a drop down list for the Company, and a drop down list for the Customer.

This, of course, creates quite a problem. The entry person will not know the Customer's ID number, so they will not be able to make an entry in that field.

The entry person can, however, enter the Company or the Customer based on their selection in the drop down lists.

But...with the Customer ID field in Orders linked to the Customer ID in Customers, if there is not a Customer ID entered when the new order is entered, then it will not save that order in the Orders table.

I thought that maybe if there way a way to choose a Company or a Customer from a drop down, then have the Customer ID automatically fill in to the Customer ID field (locked for entry) based on the Company or Customer selected, then it would retain the order in the Orders table.

Maybe I'm going about designing the tables needed the wrong way.

In any case, I could really use some help on this. Do I need separate tables for Companies and Customers? I still need them merged together for queries I will have to do on sales and orders, so that wouldn't make a lot of sense to me.

I'm attaching a pic of the Order form. As you can see, I entered a Company, but now I need it to somehow autofill that Company's ID# into the ID field, so that it will be retained in the Order table.

I seriously believe I'm going about this all wrong but don't know any other way.

Any help is appreciated!!!! (I'm screaming HELP!! you just can't hear me! LOL):banghead::banghead::banghead:

Thanks thanks thanks to anybody who can point me in the right direction!!!

And, as a side note, I do NOT know VB, so everything I do has to be done within Access itself. I am using Access 2010.
Thanks!

 

[Heatshiver]

I'm not exactly sure if I got this right. But moreorless it sounds like you want people to pick from a list and then populate the ID automatically..?

If so, then this can be accomplished. What I would do is have the list box have the ID included and be sure that the ID field on the form is NOT enabled, but is NOT locked. This way you can pass values to it, however, no one can type into it directly.

Then use the list box After Update function to state that the ID field on the form will equal the (ID) value of the column of that list box.

I had to stop and do a bit of research as there's ways to grab column information, but what I had to think about was what to use when you are on a selected value. I believe you can use ListBox.Column(Column#), just replace "Column#" with the column number you have ID on in your list (remember columns start from 0, so 0 = column 1).

I believe from there you can just throw in the code to equal each other. I just tried this on a database I'm working on and it worked fine.

EDIT: I wasn't sure at the time, but ItemData will do the same thing as Column.

 

[Hey Lucy]

Thank you so much for your response!!!!! I am going to try this method as soon as I am able to get back to work on the database and see if this gives me what I want. You are correct in assuming that I want entry person to choose name from drop down list and then automatically populate the Customer ID of that company/customer.

Do you know if this, then, will save to the orders table?

 

[RainLover]

Have you ever created a Combo Box using the wizard.

Give it a Try. Include the ID as the first field and when prompted Hide the ID.

The Control Source should be Your Foregin Key.

Done correctly this will do every thing you want.

You could also create a Test Databse using one of the Templates provided in Access. I am sure you would find within a Combo that does what you want.

BTW. You are heading down the right track, you just need to understand a little bit more about Combos.

 

[Heatshiver]

If the ID on the form is bound, it should save as Access does this automatically.

But if you're worried, which is reasonable, just add DoCmd.Save after the code in the After Update code you put in. To go the extra mile you could use:

If Me.Dirty = True Then
DoCmd.Save
End If

Or just use the save command in the Form's OnDirty event.

If you don't find your order updated, you can try Me.Requery as the last part of your After Update function. Hope all goes well!

 

[RainLover]

If the ID on the form is bound, it should save as Access does this automatically.

But if you're worried, which is reasonable, just add DoCmd.Save after the code in the After Update code you put in. To go the extra mile you could use:

If Me.Dirty = True Then
DoCmd.Save
End If

Or just use the save command in the Form's OnDirty event.

If you don't find your order updated, you can try Me.Requery as the last part of your After Update function. Hope all goes well!

I think if you look at the Help files "DoCmd.Save" will save the form not the record.

Instead of this

If Me.Dirty = True Then
DoCmd.Save 
End If

Use this.

Me.Dirty = False.

 

[Heatshiver]

You're right as long as the form is the active object. I'm guessing you'd have to write something like this to save the table:

DoCmd.Save acTable, "tblName"

However, I completely agree with the Dirty, an easier way to go. I was initially thinking along these lines:

If Me.Dirty = True Then
Me.Dirty = False 
End If

 

[RainLover]

You're right as long as the form is the active object. I'm guessing you'd have to write something like this to save the table:

DoCmd.Save acTable, "tblName"

However, I completely agree with the Dirty, an easier way to go. I was initially thinking along these lines:

If Me.Dirty = True Then
Me.Dirty = False 
End If

Most people do. But it is not necessary to write all that code. It takes time and it is one more thing to go wrong.

How can it not be the active Form.

Why are you talking about saving a Table. This is not possible.
I will let you work that one out yourself.

Remember the Golden Rule. KISS.

 

[nanscombe]

Not that it is required, but when you create a command button that saves a record it uses the code DoCmd.RunCommand acCmdSave, with a bit of checking to see whether the data has changed or there are errors, rather than DoCmd.Save ... .

 

[Heatshiver]

@RainLover it's a good idea to use the entire code if you want to see whether or not it is already being saved as you can throw in a MsgBox, or whatever you want, then determine if the code is truly necessary or not.

I was initially thinking that the datasheet somehow could be involved since I wasn't positive on the needs of this and had skimmed the question while multitasking.

I was mentioning tables as when you save a record your saving it to a table. As I understand it, the DoCmd.Save code I presented would have something to do with saving and tables according to here. If not, not sure what acTable would save...

And please direct your attitude elsewhere. I don't appreciate it. If you didn't mean to do it then just read what you wrote and think if you'd appreciate that condescending tone aimed at yourself.

Sorry Op, didn't mean to take over your thread with this, but it seemed that some things needed to be said.

 

[nanscombe]

Since it seems difficult to find examples, I think you'll find that DoCmd.Save saves display and / or formatting changes to objects, including tables, rather than content, ie column widths etc not changes to data.

To save data on a form you would use, as I mentioned previously, DoCmd.RunCommand acCmdSave.


Programmatically Change Field Value and Save the Record

 

[RainLover]

Since it seems difficult to find examples, I think you'll find that DoCmd.Save saves display and / or formatting changes to objects, including tables, rather than content, ie column widths etc not changes to data.

To save data on a form you would use, as I mentioned previously, DoCmd.RunCommand acCmdSave.


Programmatically Change Field Value and Save the Record

Nigel

I believe that DoCmd.Save does the same as DoCmd.RunCommand acCmdSave. It does not save the data it saves changes in the form design.

I just ran a test before I posted this, just in case. It appears that what I said is correct. It does not save data.

 

[nanscombe]

Quite right. It can be embarrassing when you only half remember code. It's acCmdSaveRecord not acCmdSave.

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click


    [B]DoCmd.RunCommand acCmdSaveRecord[/B]

Exit_Command6_Click:
    Exit Sub

Err_Command6_Click:
    MsgBox Err.Description
    Resume Exit_Command6_Click
    
End Sub

 

[RainLover]

That's right.

There is something about the DoCmd.RunCommand set of commands that I can't remember properly.

I think they have all been replaced and are only there now for compatibility.
Perhaps I am confused with something else. I must check it out one day. But if you know can you please enlighten me.

 

[Mihail]

Hello guys ! Until you will end the war about how to save a record I'll try to answer to the question from the first post
In my opinion is no need to treat an individual customer different from a company.
So you have no need for two fields.
Maybe you wish to add a new field, named... IsCompany (Yes/No) if you ever need to show only companies or only individuals.
This approach will eliminate your actual troubles.

 

[RainLover]

@RainLover it's a good idea to use the entire code if you want to see whether or not it is already being saved as you can throw in a MsgBox, or whatever you want, then determine if the code is truly necessary or not.

I was initially thinking that the datasheet somehow could be involved since I wasn't positive on the needs of this and had skimmed the question while multitasking.

I was mentioning tables as when you save a record your saving it to a table. As I understand it, the DoCmd.Save code I presented would have something to do with saving and tables according to here. If not, not sure what acTable would save...

And please direct your attitude elsewhere. I don't appreciate it. If you didn't mean to do it then just read what you wrote and think if you'd appreciate that condescending tone aimed at yourself.

Sorry Op, didn't mean to take over your thread with this, but it seemed that some things needed to be said.

Heathshiver,

I am sorry that I made you feel that way, it was not my intention. However it would be more of an injustice to the OP if I did not point out where you are wrong.

Neither DoCmd.Save or DoCmd.RunCommand acCmdSave will save a record. If you created a test Database this can easily be tested by showing the record selector on the form and watching the pencil. This is basic stuff which I am sure you know about. It is always a good idea to test, if you can before posting.

As far as the Table is concerned, it cannot be in design view and opened at the same time. It must be closed. If it is closed then it must be saved as Access will not allow it to close without saving. Again basic stuff. So when I say it is impossible to save then this is the reason. If you were referring to an unrelated Table then that would be a different story, but we are not.

Me.Dirty when run checks first to see if the record requires saving before actually saving the record. Your check is therefore redundant.

What I feel as incorrect on your part is your attempt to justify your code by using red herrings. Things like the message box. It has nothing to do with the saving of a record nor has the OP mentioned that he wanted to use it.

I do not see the purpose or understand why you posted your link. You may wish to enlighten me.

This leaves me with one last thing and that is “KISS”. Perhaps where you come from you read things differently to us here in Australia. The term “KISS” is a very important tool which is taught in many different disciplines. If you don’t understand its use then ask questions.

You have not pointed out which part or parts you feel are condescending so I tried to explain a few things that you may have found issue with.

If you would like a stronger apology you will be disappointed. I cannot take responsibility for the way you react to being corrected. On the other hand if you find that I am wrong please let me know as I have no problem learning from others.

 

[Hey Lucy]

Have you ever created a Combo Box using the wizard.

Give it a Try. Include the ID as the first field and when prompted Hide the ID.

The Control Source should be Your Foregin Key.

Done correctly this will do every thing you want.

You could also create a Test Databse using one of the Templates provided in Access. I am sure you would find within a Combo that does what you want.

BTW. You are heading down the right track, you just need to understand a little bit more about Combos.

Thanks RainLover! This is exactly what I did and it works perfectly. I don't know why my brain couldn't remember to do it that way, as I have done it before. Duh!!!

 

[Hey Lucy]

I just want to thank everyone for all the responses! Got the brain train working for me and now I'm moving right along towards the station called Finished!

 

[RainLover]

Good to hear.

 

[highandwild]

I've just come across this post and the answer provided by Mihail is correct. You can make the RowSource query of the Combobox or listbox to display the company name if the record represents a company customer and the contact name if the customer is an individual customer. Order the list alphabetically by company name / surname or in whatever way makes it easier for the user.