Best way to open a second form

[Runawaygeek]

Hi,

I have a first form, "Customer Details"

This links to 2 tables, Customer Info and Contacts.

on this form is a button that opens a second form

Technical Information

The table for this form has the ID of the Customer details form in colum CUST_ID.

I thought i would do a VBA button something like:

If ID = CUST_ID THEN 
DoCmd.OpenForm "Affiliate SOP", acNormal, , ("ID=" & ID)
ELSE
DoCmd.OpenForm "Affiliate SOP", acNewRec
END IF

Is this the best approach, if so, can you please help me with the IF ID = CUST ID bit as I am sure this is formatted wrong.

or, is there a better approach??

Thanks,
Ben

 

[CJ_London]

the principle is correct. re your question on CustID, please clarify where this and ID comes from. Note your examples have one with a space and one with an underline - which is it? Spaces in field names are a bad idea - VBA will change a space to an underscore for example so your naming becomes inconsistent and may not work

 

[Runawaygeek]

the principle is correct. re your question on CustID, please clarify where this and ID comes from. Note your examples have one with a space and one with an underline - which is it? Spaces in field names are a bad idea - VBA will change a space to an underscore for example so your naming becomes inconsistent and may not work

Hi CJ,

In my actual VBA/ACCESS i never have Space, everything is "_".
I am just rubbish in forums!

ok, so ID is the Autonumber ID on the Customer_Table, so Customer A = ID 1
on the Technical Information Table, under column CUST_ID would be a 1 for Customer A, so that it relates back, right?

Having read some more, i think the code would need to be a little different, in that i would need an Append Query to run if there was no matching ID.

If ID = CUST_ID THEN 
DoCmd.OpenForm "Affiliate SOP", acNormal, , ("ID=" & ID)
ELSE
DoCmd.OpenQuery "Append New Rec", asViewNormal, asAdd
DoCmd.OpenForm "Affiliate SOP", acNormal, , ("ID=" & ID)
END IF

This would then, take New Customer record, see its missing on the Tech Table, and append the data, so it then has a Record to open.

Where i am lost, is with the WHERE Clause in the initial IF statement, i dont know how to format it, as i have only ever used IF IsNull(me.Combo_Box_1) etc..

Thanks,

 

[CJ_London]

I was expecting a response along the lines of (as a guess)

Cust_ID is the name of a combo control on a form with a rowsource of

SELECT ID, CustName FROM tblCustomers

ID is a control on the same form ......no idea where it comes from

And where is your code located - i.e. what is the event that triggers it? the combo afterupdate event? a button?

Re the append, if you are opening a form as acnewrec, it will append automatically when you close the form, go to a new record, click a button to save or however you are handling it, so don't agree with your new code

 

[Runawaygeek]

How would it Auto append to the Technical Information table?
Via the relationship?

So how the database works, When you open, there is a ComboBox that is populated by the Customer Table.

You choose a customer and press open (a button) that opens the Customer Details form, based on the IDs behind the combo box matching the ID on the Customer table. This works fine.
Or you press a button to Add new Customer, this opens the same form but as a New Rec. again, fine.

You can then populate your new customer and all the contact details in this form. at the bottom is a button that says "Open Technical Details"

Right now, this button looks like:

Private Sub BTNTD_Click()
DoCmd.OpenForm "Technical Record", acNormal, , ("ID=" & ID)

End Sub

This works, but ONLY, if a record on the Technical Details Table already exists for the matching Customer ID.
If I create a New Rec, then press the button, it opens a new blank and locked out form, as there is no Key match.

What i want, is, that when you press the button, it appends the Customer data from the customer table, to the Technical table in the right columns, then opens the second form, pre populated with the Customer details that link, allowing you to then populate the Technical details for that customer.
does this make any sense??

Thanks,

 

[CJ_London]

Not really answered my question, so change names to your names.

So much easier if you used subforms - no coding required

I'm confused by your naming ID normally refers to an autonumber - in your case it seems to refer to the customer number.

Can a customer have more than one technical detail record?

If the user has created a new customer record, why do you think it is not updated?

 

[Runawaygeek]

Not really answered my question, so change names to your names.

So much easier if you used subforms - no coding required

I'm confused by your naming ID normally refers to an autonumber - in your case it seems to refer to the customer number.

Can a customer have more than one technical detail record?

If the user has created a new customer record, why do you think it is not updated?

The ID on the Table "Customer Record" is an autonumber, its the PK.

The FK to this number is Cust_number on the other table "Technical Details"

No, there would only be 1 Tech record for each Cust Rec. so 1 to 1.

I am sorry i am confusing, I struggle with the way Access does things..

 

[Runawaygeek]

The ID on the Table "Customer Record" is an autonumber, its the PK.

The FK to this number is Cust_number on the other table "Technical Details"

No, there would only be 1 Tech record for each Cust Rec. so 1 to 1.

I am sorry i am confusing, I struggle with the way Access does things..

Ill read into Subforms.. Have not used them before.

 

[CJ_London]

if it is a 1 to 1 relationship, why not just combine the two tables? then when the new customer is added, just requery your customer combo box and the user then selects the new customer

 

[Runawaygeek]

My only possible issue with that, is that not all Customers have a Tech report, some have other things depending on their Type.

If i combined the other 6 tables that hang off the Cust one, i would have about 250 columns on that one table and a lot of blank cells..

My whole database hangs off the Customer table, all other tables link in via the ID to Cust ID, with a very small exception..

This is why i wanted a primary form, then for the user to select which Sub form they required. Some Customers could be on all 6 tables, some could only be on 1 table...

This is how i would have structured it in Oracle, but someone else would have made a gui for me ontop. In this case however, there is no budget. .

 

[CJ_London]

fair comment.

Your naming convention is vague so I'm guessing at what is called what but since you have separate forms, suggest the following:

In a separate module create a global variable called say gblCustID

 Public gblCustID as long

in the customerform form before insert event put

 gblCustID=ID

in the customerform form after insert event put

currentdb.execute("INSERT INTO tblTech (CustID) VALUES (" & gblCustID & ")")
forms!firstform!customercombo.requery
forms!firstform!customercombo=gblCustID

note change firstform!customercombo to whatever you have called your initial form and combo

in your button to open the technical form you then just need

DoCmd.OpenForm "Technical Record", acNormal, , ("CustID=" & customercombo)

 

[Runawaygeek]

fair comment.

Your naming convention is vague so I'm guessing at what is called what but since you have separate forms, suggest the following:

In a separate module create a global variable called say gblCustID

 Public gblCustID as long

in the customerform form before insert event put

 gblCustID=ID

in the customerform form after insert event put

note change firstform!customercombo to whatever you have called your initial form and combo

in your button to open the technical form you then just need

DoCmd.OpenForm "Technical Record", acNormal, , ("CustID=" & customercombo)

Can i check where i am creating this Module, i thought here:



But access says it cant see it?

 

[CJ_London]

because you have given the module the same name as the variable - change the name of the module to say modVars

 

[Runawaygeek]

because you have given the module the same name as the variable - change the name of the module to say modVars

Ok, I see what you have said but i must be missing something,
When its in there and i try to type in a new Rec. it says invalid use of null, as the ID number is still null until i finish entering the Customer name?

(thank you for your help!)


UPDATE: can I add an ONLOAD that forces the new record number to be created when i load the form?

 

[CJ_London]

which event did you use and on which form?

 

[Runawaygeek]

which event did you use and on which form?

You said to have the Before and After Events on the Customer Form, so i did.

Before Insert | glbCustID=ID
After Insert | currentdb.execute("INSERT INTO tblTech (CustID) VALUES (" & gblCustID & ")")
forms![Customer_Form]![CustBox].requery
forms![Customer_Form]![CustBox]=gblCustID

(Its not a combobox, just a box to type into. The Combobox is on the form before that allows the record to be opened. The issue lies in the opening of a new record.

I might build a fake data one and send it, then you can see my structure better maybe?

 

[CJ_London]

so I guessed correctly that you have a table called tblTech which has a field called CustID