Urgent - Making linked forms, like the wizard does but in design view!

S

Sharky II

Registered User.
Local time
Today, 19:34
Joined
Aug 21, 2003
Messages
354
Making linked forms, like the wizard does but in design view!

Hi guys

I've made a form, and i want to add a couple of buttons so that they open (related) forms, just like the form wizard does when you choose fields from two different tables. I want it to be just like a subform, but to be opened only when the user clicks the 'show info' button.

If i just add a button and tell it to open the other forms, it does open them, but it's not related - ie, the system is for a travel agency and the 'main' form is the customer form, and i USED to have the trip information for as a subform of the customer table - this way when you see a customer, you also see his/her RELATED trip information.

So you can see that if the button just 'opens' the trip form like it does at the moment, well, this isn't very helpful.

I also want another button to open another form, which is little more than a text box (memo) for additional comments, but needs to be related all teh same.

If anyone can help me, i'd really appreciate it, as i'm so close to finishing, all i have to do is link this stuff up!!!

I don't understand why the wizard doesn't let you do it if you choose fields from more than two tables - that's a bit crap!

Cheers guys
 
Last edited:
Sharky II,

If the other forms are subforms, you can use the Master-Child
links on the property sheet of the subform to specify the
control sources for the parent and child. The button if you
chose to use it could just select whether the subform is
visible or not.

If the other forms are popup forms you can:

Code: 
DoCmd.OpenForm "YourPopUp",,,"[TheKeyField] = '" & Me.TheFormsKeyField & "'"

That assumes that your key is a string, not a number. Remove
the singlo-quotes if it is numeric.

and you can set the default value for your key field to:

Forms![YourMainForm]![TheFormsKeyField]

Wayne
 
hi there wayne!

sorry but i'm totally new to access, sorry if i'm a bit slow!

I am pretty stupid! All i had to do (i think) is to create the button, then use the Command button wizard and select 'Open the form and find specific data to display' - i then match the two Customer ID's and all is well...

...UNTIL i click final and i get an error saying 'Subscript out of range'

Do you know what this means and how i can stop it happening please!?

Cheers again
 
Last edited:
Taken from one of those links:
I have a form with various subforms, command buttons, and combo boxes. The VBA code for this form has suddenly disappeared. When I look at the properties of one of the command buttons, it still says [Event Procedure] after On Click, but when I click "..." to view the code, nothing happens. I can't add any new code, and I can't even save the form.
Click to expand...

Exactly same thing here - i tried copying the code but it won't even open the code builder.

I CANNOT remake the form as it's just too much work to do before tomorrow (i've done a lot of faffing about with the form, took me ages), is there any way i can copy and paste stuff into a new form and keep the fields functional?

I'll have a play with it now, but i'm doubtful!

Thanks for your help Wayne!
 
Sharky,

No! Read the last post!!

Start Access, open a new, blank database.

Do File --> Get External Information --> Import

and import ALL of your old database into the new, blank one.

That fixes a lot of problems, with Access, get used to it.

Wayne
 
yeah mate i tried it (sorry forgot to say - oops!)

didn't work - gonna remake the form and just set up all the buttons before i start farting around with formatting etc

thanks though mate!
 
before you go man!!!!!

at the moment the button, despite doing what i said (ie i linked customerID from customer table to customerID on trip table in teh wizard) teh button only opens the trip table, and does not open the relevant record (ie it doesn't match the trip record to teh correct customer - the customer ID is not automatically set!). You can do this by a drop down but this isn't how it should be. At the moment the code is:


Code: 
Private Sub Trip_Info_Click()
On Error GoTo Err_Trip_Info_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Trip5"
    
    stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Trip_Info_Click:
    Exit Sub

Err_Trip_Info_Click:
    MsgBox Err.Description
    Resume Exit_Trip_Info_Click
    
End Sub

can you tell me what i need to change to make to work? the tables are called 'customer' and 'trip' respectively. i have only been using access for two days!

Plus, how do you reset the autocounter to 0 again???

Cheers!
 
Last edited:
Sharky,


Code: 
Private Sub Trip_Info_Click()
On Error GoTo Err_Trip_Info_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Trip" ' <-- This is your form Name!
    
    ' CustomerID is the name of your key field in the child table,
    ' it is also the name of your field on your main form.
    '
    stLinkCriteria = "[CustomerID] = " & Me.[CustomerID]          ' Number
    stLinkCriteria = "[CustomerID] = '" & Me.[CustomerID] & "'"   ' String
    stLinkCriteria = "[CustomerID] = #" & Me.[CustomerID] & "#"   ' Date

    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Trip_Info_Click:
    Exit Sub

Err_Trip_Info_Click:
    MsgBox Err.Description
    Resume Exit_Trip_Info_Click
    
End Sub

That should work.

Wayne

If it doesn't you can:

Tools --> Database Utilities --> Compact
ZIP your Database
Post it here.

The "autocounter" is an AutoNumber. You should not care At ALL
what it is. It is just a unique identifier for your records (to be
used for linking with other tables, etc.). On normal forms, you
won't display it, users won't care what it is. It will reset if you do
the compact as described above, but it really doesn't matter.
 
mate i've done that but the file is 303k zipped and it won't allow me to attach!


have you got msn or aol? i really need a hand with this - ANY help would be cool
 
Sharky,

What happened, didn't you read the mail on your user CP?

Wayne
 
Edd,

The subordinate tables have to be subforms. That is the way
that Access works.

Your form looks very nice though!

The only way that we can do anything, and it will take a while,
is to convert your main form to a "bound" main form and the
rest of the items (all of the checkboxes) are "unbound".

We can put the buttons for the subforms on, they will bring
up "bound" popup forms. We will then have to move their
information back to your unbound checkboxes on the main
form.

You have how much time?

Wayne
 
Mate it's 6:30am - i have to go in to the office before about 3pm (sleep is for girls, and is not required) - it'll take as long as it takes!

By the way, the trip info is seperate to the checkboxes - the checkboxes are just supposed to be a quick reminded to the user of which trips the customer has ever showed interest in.

What's the next step?

Thanks for this man
 
Edd,

Let me take this from the top:

You have a bunch of customers, they're on the main form.

They take a bunch of trips, that is not on the main form, you
want a button to display the Trip table. It works as a subform,
we should make it work as a popup. We fill out a lot of
info, but don't display it on the main form.

Where do they select the Discovery, Expedition and Lifestyle
checkboxes. The tables don't relate to any other tables,
not customers or trips!

Wayne
 
Discovery, Lifestyle, Expdition and Presige are all types/genres of trips Inside each of these talbes are destinations set as FIELDS (I tried doing it so that they were all records and not fields but then they were all drop downs and the client must be able to tick more than one thing... anyway). They want a load of checkboxes (on the same screen as teh customer info - the main screen) to tick when the customer calls up just to indicate what trips the customer was interested in... if you check out the form entitled 'Customer1' you can see how i've done that, but there are no buttons on that page. Check out 'finalform' to see what the (rough, without titles etc) layout is sort of like.

You're right - making the checkboxes on teh same page as the customer details gives us a problem because when they are there, all the records do not show up that are available in the customer table. But...

Aren't they related to customerID?
 
Edd,

I divorced the checkboxes for the moment. The main screen
"Customer1" is just the customer info.

I put a command button to view/edit the trip info, we can
pretty up that form.

We can do the same thing for the phone log.

The Discovery, Expedition and Lifestyle
checkboxes. The tables don't relate to any other tables,
not customers or trips!

Are they just a thing to show what the customer might
be interested in? If so, and we want to cheat a little
we could just add them to the main table and we could be
done sooner than you think.

I'm cutting this down to size so that i can post it here.

Removing logo, etc.

Will check back before posting it.

Wayne
 
WayneRyan said:
Edd,

I divorced the checkboxes for the moment. The main screen
"Customer1" is just the customer info.

I put a command button to view/edit the trip info, we can
pretty up that form.

We can do the same thing for the phone log.

The Discovery, Expedition and Lifestyle
checkboxes. The tables don't relate to any other tables,
not customers or trips!

Are they just a thing to show what the customer might
be interested in? If so, and we want to cheat a little
we could just add them to the main table and we could be
done sooner than you think.

I'm cutting this down to size so that i can post it here.

Removing logo, etc.

Will check back before posting it.

Wayne
Click to expand...

You got it exactly right - the checkboxes are simply so the people can see who is interested in what - a silly touch but it's what they wanted! Sticking it all into the main customer table sounds like a definate plan, saves a lot of hassle, and i'm not getting paid for this!

Thanks SO SO much for your help, i am clearly in your debt mate.

Edd
 
You must log in or register to reply here.

Similar threads

W
After reassign link to new form in the navigation subform of the navigation button, error message "The form is not exist"
Replies
8
Views
287
wendywu
W
C
Solved The Expression ... you entered as the event property setting produced the following error: Requested type library or wizard is not a vba project
Replies
0
Views
288
CarltonJ
C
P
Solved Imported DB to windows 11 does not show objects or top panel
2
Replies
23
Views
920
Gasman
Gasman
B
Pop up forms not "stacking" in the right order
Replies
12
Views
614
CJ_London
C
T
Access When emailing brings up Outlook 2016 wizard to add email account
Replies
10
Views
766
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom