Creating link between 2 forms with code? 1

[InkyRich]

Hello,
Can anybody tell me how I can create a link between two forms using VB without embedding one as the sub-form into the other. I want to have two seperate forms but with the second updating when I change the record in the first. I have created the 'one-to-many' relationship in the Relationships form but in order for it to work I need to paste the second form into the first. Is there any way over this?

Looking forward to hearing from you.

InkyRich

A fool and his money are soon parted - so you might as well send it to me!

 

[Remou]

You will have to write code to update the second form. You could use an append query, a recordset, or update each control in an event.

 

[InkyRich]

Thanks Remou,
Any suggestions for the VBA I need?
Inky

A fool and his money are soon parted - so you might as well send it to me!

 

[Remou]

You need to decide what method will suit best and this will depend on your application. The rules for relational database design (

http://www.r937.com/relational.html)

dictate that data should not be duplicated, so it is probable that not all fields in the second form will need to be updated. Why not sketch what you see happening, and I (or someone) will comment?

 

[InkyRich]

Ok.
Everything is controlled by 'todays' date, which at the moment is entered manually. Under the date is a day planner of a list of events to be carried out at particular times in the day.
On double-clicking an event this will open up the lower form which lists various checks that have to be made at that event time.
These forms are too big to be pasted into one DateFRM so I need to be able to reference one form to the other in order that when the date changes to the next day a new set of blank forms is presented.
As time goes on the 'days' results are saved to the tables which can then be accessed to see the history and print reports.
I hope you understand as I dont think I have explained myself too well.
Inky

A fool and his money are soon parted - so you might as well send it to me!

 

[Remou]

There are a few possibilities here.

1. Have you considered a tab control?
2. Do you know that you can use a Where statement with DoCmd.OpenForm?
3. You could use the after update event of the date control to filter the list for the lower form, or better, to set the recordsource for the lowere form.

 

[InkyRich]

I did not want to go the tab route but I like the sound of both of suggestions 2 and 3.
Do you have any code?
Inky

A fool and his money are soon parted - so you might as well send it to me!

 

[Remou]

You will find the syntax for OpenForm in help, as for the third option, sketch out your idea of the code and I (or someone) will comment. You will find the After Update event in the properties for the control.

 

[InkyRich]

Thanks Remou I will give it a try
Inky

A fool and his money are soon parted - so you might as well send it to me!

 

[MajP]

faq702-5860

 

[InkyRich]

Hello again Remou,
Sorry to be a pain but can you help me with the DoCmd code you suggested. I have tried various ways to get the form : 1 method is using the tempdate variable as a date:

    Dim stDocName As String
    stDocName = "testerdateform"
    
    Dim tempdate As Date
    tempdate = 11 / 3 / 2008
    
    DoCmd.OpenForm stDocName, , , "txtDate = tempdate"

and then I have tried using the tempdate variable as a string:

    Dim stDocName As String
    stDocName = "testerdateform"
    
    Dim tempdate As String
    tempdate = "11/03/2008"
    
    DoCmd.OpenForm stDocName, , , "txtDate = 'tempdate'"

Can you see anything wrong with this and why it will not filter out the date 11/03/2008?

Inky

A fool and his money are soon parted - so you might as well send it to me!

 

[Remou]

You need to use an unambigous date and date delimiters and you also need the name of a field, not a control, so:

Dim tempdate As Date 'I do not think your date is a String
    tempdate = #2008/03/11# 'yyyy/mm/dd
    
    DoCmd.OpenForm stDocName, , , "TheDateField = #" & tempdate & "#"

Or

DoCmd.OpenForm stDocName, , , "TheDateField = #2008/03/11#"

I hope your date field is not called 'date', it is a reserved word and to be avoided along with 'name' and other reserved words:

http://support.microsoft.com/kb/286335

 

[InkyRich]

Hi Remou,
Thanks for the code. I have got it to filter ok. I had to tweek the code slightly as the date format in England is different to that of the States in that we have the format dd/mm/yyyy, but that was all.
Thanks again.
Will probably be in touch again knowing my luck!!
Regards
Inky

A fool and his money are soon parted - so you might as well send it to me!

 

[Remou]

The format I suggested was for use in any part of the world. The UK date is ambigous as far as US-designed Access is concerned. You may find problems when the date is 3/11 as you show. Access will treat this as 11/3 (mm/dd) in many cases.

 

[InkyRich]

Hello again Remou,
That is a very good point, I will take that into account.
Inky

A fool and his money are soon parted - so you might as well send it to me!