Passing data to another form

[rixx]

I saw this topic in another thread, but I haven't been able to apply any of those tips.

I am trying to create a drill down from a text box and created a double click event to open a form and populate it with data keyed off of the value of the text box. I tried to do the Do.Cmd formname and in the wherecondition, I try to pass it the value of the text box from the first form, but it only opens the form and doesn't populate it with information from the current row. What am I missing? Do I need to create a recordset, populate the recordset then open the form. The form controls are bound to the tables. Do I need to run a query, populate a recordset, then make the assignment to the controls, or can I use queries that are predefined.

Any help would be most appreciated.

 

[jsaliers]

The easiest way is to create and populate a recordset from the first form, and then move this to the second form. I have included some sample code. There is some stuff missing, but it is pretty clear.

Dim dbs as DAO.Database
Dim rs1 As DAO.Recordset

'Open Form2 and switch to it
Form2TextBox.SetFocus
Form2TextBox.Text = vbNullString

On Error GoTo Error_Catch

'Switch to Form1

Set dbs = CurrentDB
Set rs1 = dbs.OpenRecordset("Select FieldWanted FROM Table1 WHERE Condition)

'Switch to Form2

Form2TextBox = rs1("FieldWanted&quot

ExitProcedure:

Exit Sub

Error_Catch:

MsgBox (Err.Description)
Resume ExitProcedure

End Sub

Hope this helps you.

Jon

 

[rixx]

Thanks, Jon. I'll try it out.

Rick

 

[rixx]

Jon et. al.,

I tried the following:

Dim dbs As DAO.Database
Dim rs1 As DAO.Recordset

Set dbs = CurrentDb
Set rs1 = dbs.OpenRecordSet("SELECT * From MyTable Where [FieldName] = '[Literal Value]'&quot

But when I run this piece of code, I get a message saying "Compile Error: User-defined type not defined." If I run the following in the Immediate Window:

Set dbs = CurrentDb
Set rs1 = dbs.OpenRecordSet("SELECT * From MyTable Where [FieldName] = '[Literal Value]'&quot

I am able to print the values from the recordset. Am I missing something? Where do I have to Dim the DAO.Database object? Why does it think that DAO.Databse should be a User-defined type? The debugger highlights that line. I'm using Access 2002 if that makes a difference.

Any help would be greatly appreciated.

Rick

 

[jsaliers]

I am not sure I understand why you are using [FieldName] = '[Literal Value]' as your condition, but I don't see why it would not work. Do you have a reference to the proper DAO objects? (Tools>References)

The reason for the confusion over the condition is that I thought you would be using field values from the last record. If that is the case, maybe this will work:

'Populates the recordset
Set rs1 = dbs.OpenRecordSet("SELECT * From MyTable&quot

'Moves to the last record on the recordset
rs1.MoveLast

TxtBoxOnForm2 = rs1("FieldWanted&quot

Hope this helps you.

Jon

 

[rixx]

Jon,

I think I now have a proper reference to the DAO objects because I'm no longer getting that error. However, I do get a Type Mismatch and the debugger points to the statement:

Set dbs = CurrentDb

In the immediate window, I try to print CurrentDb and get the Type Mismatch error. What does it expect? I thought of putting the fully qualified path to the database but the Type Mismatch error is telling me something else.

Thank again,

Rick

 

[jsaliers]

I had a similar problem myself, and it had to do with how I wrote the conditions in the following statement.

Set rs1 = dbs.OpenRecordSet("SELECT * From MyTable Where [FieldName] = '[Literal Value]'&quot

My problem was fixed when I changed it to the following:

Set rs1 = dbs.OpenRecordset("SELECT * FROM MyTable WHERE [FieldName] = '" & [Literal Value] & "'&quot

Maybe this will fix the error. Let me know if it works.

Hope this helps you.

Jon

 

[rixx]

Jon,

I get the Type Mismatch error even before it gets to that point. The debuggers points to this statement:

Set dbs = CurrentDb

For some reason, it won't allow me to set dbs as the currentdb. I added the ampersands:

Set rs1 = dbs.OpenRecordset("SELECT * FROM MyTable WHERE [FieldName] = '" & [Literal Value] & "'&quot

but it doesn't even get to that point.

Thanks again,

Rick

 

[jsaliers]

When you declared your variables, make sure you declared the recordset and database variables correctly.

Dim dbs as DAO.Database
Dim rs1 as DAO.Recordset

Hope this helps you.

Jon