Cant Get my Buttons to Open a Related Form

[ssanfu]

Form....................... PK Value .................................................. ........Action
MainMenu ....-->................................................. ....................... click on button "All Customers"
AllCustomers --> (AllCustomersQ.CustomerDatabaseID = 10) --> .........click on button "Edit"
Lead ...........--> (tblCustomerDatabase.CustomerDatabaseID = 10) --> click on button "2.1 Identity"

Error in code!!!

                    Docmd.OpenForm "Form2", acNormal, , "[CustomerDatabaseID]=" [&CustomerDatabaseID], , acNormal

Should be

                    DoCmd.OpenForm "Form2", acNormal, , "[CustomerDatabaseID]=" & [CustomerDatabaseID], , acNormal

****** EDIT: the above error could be caused by the Convert Macro to Visual Basic wizard....... *******


Form2 --> (tblCustomerDatabase.CustomerDatabaseID = 10) --> ......... click on button "Intro Letter"

The code that runs when the button "Intro Letter" is clicked is

Private Sub Letterbtn_Click()
    DoCmd.OpenForm "CompanyLetter", acNormal, , , acFormEdit, , Me!CustomerDatabaseID
End Sub

Note that Me!CustomerDatabaseID (which is 10) is in the "OpenArgs" parameter


CompanyLetter -->
has code in the Form Load event

Private Sub Form_Load()
    If Me.NewRecord Then Me!CustomerDatabaseID = Me.OpenArgs
    RunCommand acCmdSaveRecord
End Sub

Form "CompanyLetter" is bound to "tblCompanyDDIdentity". BUT there is only one record in the table........ tblCompanyDDIdentity.CustomerDatabaseID = 9
And there is nothing in the code to set the current record to tblCompanyDDIdentity.CustomerDatabaseID = 10! (if it exists)
So CustomerDatabaseID = 10 was passed to the form "CompanyLetter" using "OpenArgs" parameter, - no code tries to set the current record to the "OpenArgs" parameter value.

Because there is only one record (#9), that is the record displayed. No filter is set.


So in the "CompanyLetter" Load event, there needs to be a check to see if there is a CustomerDatabaseID FK value in the table that matches the CustomerDatabaseID PK value.
If not, then add a new record else move to that record.




In the form "AllCustomers", the code for the button "Command103" (Show All) is wrong

Private Sub Command103_Click()
    Me.Filter = FilterUK
    Me.Filter = ""
    Me.Requery
End Sub

I would try

Private Sub Command103_Click()
    Me.Filter = ""
    Me.FilterOn = False
End Sub

to remove the filter and show all records.

[mike60smart]

I would agree with June that this calls for a Main Form and SubForm layout.

Also it would appear that you do not want to rectify your problems with table structures not being normalised.
You also do not appear to have removed Spaces in Field names and Lookup Fields from Tables?

I would recommend that you stop and not think about Forms at this stage.

Rectify the problems highlighted and then come back with further questions.

[Steven19]

Database3 (4).zip

Well I had it working with the above advice received with thanks, but a colleague has tampered with something this morning and it is not working anymore. I don't think its the code, as I had it working yesterday, I think they have touched the property boxes as they made them all popup and modal, but something is clearly an issue.

I test through Main Menu, All Customers, Edit (Second Test), 2.1 Identity, Infor Letter should show record 10, but they don't none of them, they all show 9.

Can you tell what they have broke?

[Steven19]

I would agree with June that this calls for a Main Form and SubForm layout.

Also it would appear that you do not want to rectify your problems with table structures not being normalised.
You also do not appear to have removed Spaces in Field names and Lookup Fields from Tables?

I would recommend that you stop and not think about Forms at this stage.

Rectify the problems highlighted and then come back with further questions.

Hi Mike

I have started taking out the spaces and I have also started removing any look-ups. So what I am doing with them is removing the look up, starting from scratch with the table relationships and using the combo box in the forms save the value in the appropriate value in the tables. I've already had to change some query code and lookup for country lookup and renamed it. I've also taken out a query from ssanfu and amended some of his points in my database too, such as the Show all button etc etc that was the main one I used a lookup from the table. I have also removed a few table relationships and re-adjusted the relationships so I do not have unnecessary data. However the main front end has to work so I can get proper development time allocated because at the moment I am doing it here and there in windows of opportunity, so want to reassure you I am not ignoring your vast experience, just that there is a reason to the madness.

[mike60smart]

Hi Steven

OK Great.

Did you also see my comments regarding the tblCompanyDDIdentity ?

Also both table DirectorsIdentity and tblCustomerDatabase needs work on them to normalise fields.

[Steven19]

Hi Steven

OK Great.

Did you also see my comments regarding the tblCompanyDDIdentity ?

Also both table DirectorsIdentity and tblCustomerDatabase needs work on them to normalise fields.

Hi Mike

I have a renamed version taht I am working on. Taking your suggestions forward I am seeing the benefits.