Passing parameters between forms.

cath_hopes

Registered User.
Local time
Today, 14:35
Joined
Oct 17, 2007
Messages
52
I've been going round in circles for a while with this one and I'm sure there's an easy solution - I just can't see it!!

Anyway, I have two forms both based on the same table: Property which has a primary key "Property ID". Form1 asks the user to select a property using the a combo button. The code behind this button is:

DoCmd.Openform "Property Form", , , , , , "[Property ID];[House Name/ Number];[Address Line 1];[Date record created]"

This opens form2 (Property form) and form2 should then show all the editable record details for the selected property but instead each field show #Name? instead. The 4 parameters are passed to the second form just not having any influence...

What do I need to do to correct this?
Hope someone can help!
 
Check the names of your forms. They should not be the same.
Instead of using two forms why don't you use one form. In the second form header create the combo box and on the After Update Event add this code

Dim Cancel As Integer
DoCmd.ApplyFilter , "PropertyID = Forms!frmProperty!cboPropertyID"
If IsNull([PropertyID]) Then
MsgBox "No Records Matching this PropertyID.Select another ID please", vbOKOnly, "No Data"
Cancel = True
Me.cboPropertyID.SetFocus
End If

Assuming you name your combo cboPropertyID otherwise use the name of your combo.
The combo should have its recordsource based on your table and the row source based on the PropertyID

Cheers
 
Hi there Coyote,

One form is a simpler solution with the combo at the top - I have tried this and it now works. I can now move onto my next Access task.

Just one more question: my combo has 4 columns (one hidden) and is based on a query that sorts the data within the columns. Once a user has selected a 'record' from the combo, the combo will only show the first field (column). This isn't that important, just looks odd and I was wondering how to display all non-hidden columns after_click. (The drop down will still show them).

Thanks loads for your help!
Catherine
 
Well it worked for a short while then I added a new record and then it stopped working... aargh!
The single form idea did work (and without your Docmd.ApplyFilter code) but only briefly.
I have since tried using your code but there's a problem with the following line:
DoCmd.ApplyFilter , "[Property ID] = [Forms]![PropertyForm]![Combo25]!Column(0)"

It produces a runtime error 3270. I guess it doesn't like the column(0) bit, but that is where [Property ID] is stored. So I also tried:

DoCmd.ApplyFilter , "[Property ID] = [Forms]![PropertyForm]![Combo25]"

This does not error, but it doesn't pick up any record either....
By the way, Combo25 has 4 column/ fields - the first being Property ID.

So then I thought about using the following bit of code within the Sub Combo25_AfterUpdate():

Dim Counter As Integer
Counter = 0
While [Property ID] <> [Combo25].Column(0)
Counter = Counter + 1
DoCmd.GoToRecord acActiveDataObject, Property, acGoTo, Counter
Wend

If the Counter value is correct then the DoCmd.GotoRecord statement should work, however the While loop doesn't seem to be working nor am I confident that [Property ID] is being recognised....

What do you think of all this...?
 
I've refined my While.. Wend and DoCmd.GotoRecord idea and it works albeit clumsily:

Its a real sledgehammer solution to the problem, but it does mean I can move on to my next VBA job, however at some point I will definitely want to improve on this. The following code retrieves the correct record & displays it but not before it screen flashes through every record that precedes it in the database table! Ultimately there will be about a thousand records so its not ideal but will do for now. Here is my code which sits in the Combo After Update procedure:

Dim counter As Integer
Dim CNumID As Integer
Dim PNumID As Integer
counter = 1
CNumID = Combo25.Column(0)
PNumID = 0
While CNumID <> PNumID
DoCmd.GoToRecord acActiveDataObject, Property, acGoTo, counter
If [Property ID] = Null Then
PNumID = 0
CNumID = 0
'Send user a message stating that property record does not exist...
MsgBox "Property record does not exist."
Else
PNumID = [Property ID]
counter = counter + 1
End If
Wend

Ho hum!
Thanks for reading.
 

Users who are viewing this thread

Back
Top Bottom