Strings in [Form!]

[Jbozward]

Hi all.

This is more than likely to be real simple, but I can't get it to work....

I need to add a string into a Form! line.
Here is what doesn't work

    Dim rs As Object
    Dim frm, fld As String
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ID] = " & Str(Me![Combo16])
    Me.Bookmark = rs.Bookmark
    frm = Forms![Form - Quote]![Frame155]
    If frm = 1 Then fld = "Case"
    If frm = 2 Then fld = "Memory"
    Forms![Form - Quote]![***fld String here***] = Me![Description]
    Forms![Form - Quote]![***fld String here***]  = Me![Cost]

Can anyone help?

 

[Mile-O]

Dim rs As Object

Be more specific:

Dim rs As DAO.Recordset, or Dim rs As ADODB.Recordset (depends on your version and/or preference)

Dim frm, fld As String

This dimensions a variant and a string. To dimension two strings (i.e. what you want) you do this:

Dim frm As String, fld As String


Set rs = Me.Recordset.Clone

Set rs = Me.RecordsetClone


rs.FindFirst "[ID] = " & Str(Me![Combo16])

If ID is a numerical data type then why are you wanting to convert it to a string?

rs.FindFirst "ID = " & Me.Combo16


frm = Forms![Form - Quote]![Frame155]

What a horrible name for a form. Can't your frame have a more meaningful name too?

If frm = 1 Then fld = "Case"
If frm = 2 Then fld = "Memory"

fld = IIf(frm = 1, "Case", "Memory")


Forms![Form - Quote]![***fld String here***] = Me![Description]
Forms![Form - Quote]![***fld String here***] = Me![Cost]

I prefer to use early binding in my code as opposed to the late binding you have employed.

So, the final part is:

Forms("Form - Quote").Controls(fld) = Me.Description
Forms("Form - Quote").Controls(fld) = Me.Cost


Now, where you are going to have problems is that it would appear your controls have the same name as fields in your form's underlying recordsource. Change the controls to give them a prefix which will help differentiate between fields and controls. So, txtCost is better than Cost, cboWhatever is better than Whatever, etc.

After doing this:

Dim rs As DAO.Recordset
Dim frm As String, fld As String
Set rs = Me.RecordsetClone
rs.FindFirst "ID = " & Me.Combo16
fld = IIf(frm = 1, "txtCase", "txtMemory")
Forms("Form - Quote").Controls(fld) = Me.txtDescription
Forms("Form - Quote").Controls(fld) = Me.txtCost

 

[Jbozward]

You are a God.....

Cheers!

 

[Mile-O]

You are a God.....

Hmm, not quite!