Add a New Customer Button to a Subform not working...

[accessnewtome]

Thank you thank you. Please take your time. As I said, I have very limited time but will work on every chance I get.

[RuralGuy]

I'll have to be honest and say I don't use Macros but I do everything with VBA. Are you creating this for on-line or something? If not, macros are not necessary and frankly I find them limiting.

[RuralGuy]

Let's start with the ContractorsCustomers form. Are you aware that Access *always* save changes to a forms Record Source unless you take extraordinary steps?

[RuralGuy]

I could probably work with macros if you want but I'll be learning at the same time so it will be a bit slower.

[RuralGuy]

BTW, just get to it when you can. I have plenty to keep me busy and will just wait for you to have time to respond.

[accessnewtome]

Thanks for your understanding and again...thank you for your help!

"I'll have to be honest and say I don't use Macros but I do everything with VBA. Are you creating this for on-line or something? If not, macros are not necessary and frankly I find them limiting."

I would prefer to learn more VBA. I just did the macros because they seemed easy until things got more in-depth. I program AMX control systems and that programming is based off the C+ platform. So not completely new to programming...just don't know VBA.

"Let's start with the ContractorsCustomers form. Are you aware that Access *always* save changes to a forms Record Source unless you take extraordinary steps?"

Yes I am aware. I am used to programming touch panels where there is a button for everything. To keep with my trained brain patterns, I was trying to use buttons for everything. Plus I will be using this on a Microsoft Surface tablet so bigger target the better.

"I could probably work with macros if you want but I'll be learning at the same time so it will be a bit slower."

I would prefer to do VBA. I just have to learn it. But if you want to learn macros I'm in. But I agree they seem limiting and probably more work in the long run.

[RuralGuy]

In the ContractorsCustomers you have a "Save and Exit" button. The Close you have selected *only* saves changes to the form when you select YES. As stated earlier, the changes to the data are *always* saved by Access when you close a form. You do know that you can convert all of your macros of each form to VBA with a selection from one of the menus, right?

[accessnewtome]

Yes, for the "Save and Exit" button, I had it set to "Prompt" but it would never prompt. So I had set it to "Yes".

I did read somewhere in my journeys that you could convert the macros to VBA. Never tried it until now. I converted the "Save and Exit" button to VBA. When VB opens, I can see the programming behind it now...along with some programming that I tried from somewhere that didn't work and I deleted the button but not the code (button49 I believe). So I deleted that. So now I just have:

'------------------------------------------------------------
' btnContractorCustomersSaveNewCustomer_Click
'
'------------------------------------------------------------
Private Sub btnContractorCustomersSaveNewCustomer_Click()
On Error GoTo btnContractorCustomersSaveNewCustomer_Click_Err


DoCmd.Close acForm, "ContractorsCustomers"




btnContractorCustomersSaveNewCustomer_Click_Exit:
Exit Sub


btnContractorCustomersSaveNewCustomer_Click_Err:
MsgBox Error$
Resume btnContractorCustomersSaveNewCustomer_Click_Exit


End Sub

This is where I'm assuming I need some sort of IF statement to see if there's a "ContractorID" associated with the open form...

Unless we're going a totally different direction with design?

[RuralGuy]

If I use the DoCmd.Close command, here's how I use it:

DoCmd.Close acForm, Me.Name, acSaveNo

I'll post again in a few minutes.

[RuralGuy]

The top of *all* of your Class (code) modules should begin with:

Option Compare Database
Option Explicit

[RuralGuy]

It also now looks like the "tblContractorsCustomers" should be a query with a Join rather than a table but I'm not positive yet.

[ssanfu]

Just a couple of comments:

With regards to the form close command. The syntax is:
expression.Close(ObjectType, ObjectName, Save)

Help says: "A AcCloseSave constant that specifies whether or not to save changes to the object. The default value is acSavePrompt."
Notice the phrase "save changes to the object"; this means saving changes to the form object, not saving the data entered into the form.

To close a form, I use: "DoCmd.Close acForm, Me.Name"

---------------------------------------------------

I know nothing about your requirements, but looking at your dB, I think you need to change your relationships and the structure of a couple of tables. I would expect the structures/relationships to look like this:

Note: I did remove all spaces from object names and any special characters. I added the suffix "_PK" for primary key fields and "_FK" for foreign key fields.
I didn't see any date fields, so I added one to the junction table "jnctContractorsCustomers".

But then again, I could be wrong.....

[accessnewtome]

Thanks for the details...I will play around first chance and report back.

[accessnewtome]

OK, had a couple minutes.

I tried re-doing my tables ssnafu to your suggestion...I get the same results.

So stuck at:

When I add a customer to my contractor; I click the button to add new customer.

When the form opens, the contractors name and ID is filled in appropriately.

When I close the form either by my save button or by the close window x, it saves the record to tblContractorsCustomers; but it does not have the ContractorID or Contractor Name associated with it.

Also, when I go into my Contractors Table; the entry that saves into the tblContractorsCustomers does not show under the contractor.

Danggummit!

[RuralGuy]

You may wish to attach your db again so we can look at it.