Setting recordsource for Form in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Access 03 in WinXP

I am trying to set the recordsource for a form to a recordset which is
defined by which company database I am currently in. In defining the SQL
statement, I then need to define the ControlSource property for two text
boxes to the two fields in the SQL statement, which unfortunately are
different based upon the company database.

Dim MySQL, MyCompany as String

' MyCompany is defined in a lookup

If MyCompany = "ABC" Then
MySQL = "Select Address1, Address2 From TempABC;"
Forms!frmVerifyInfo.RecordSource = MySQL
Me.txtVerify1.ControlSource = MySQL.Address1
Me.txtVerify2.ControlSource = MySQL.Address2
ElseIf MyCompany = "XYZ" Then
MySQL = "Select Company1, Company2 From TempABC;"
Forms!frmVerifyInfo.RecordSource = MySQL
Me.txtVerify1.ControlSource = MySQL.Company1
Me.txtVerify2.ControlSource = MySQL.Company2
End If

This is producing the error on the first ControlSource definition with the
"MySQL." giving the error message "Invalid Qualifier". If I change the
definition from MySQL.Address1 to [Address1], and so on, then the form opens
but all records are have the data of the first record.

I initially tried using rs as Recordset, defining my recordset with the SQL
statement, and then setting the RecordSource to the rs recordset, but this
gave me an error of Type Mismatch.

Any suggestions on how to accomplish what I am trying to do will be truly
appreciated.

Thanks!
 
Me.txtVerify1.ControlSource = "Address1"
....
Me.txtVerify2.ControlSource = "Company2"
 
Excellent!

One more piece. I need to have a field that displays the length of the
data. I'm using Me.txtVerify1Length = Len(Me.txtVerify1) but this is giving
me #Name?

Can you provide the proper syntax?

Brendan Reynolds said:
Me.txtVerify1.ControlSource = "Address1"
....
Me.txtVerify2.ControlSource = "Company2"

--
Brendan Reynolds
Access MVP


Pendragon said:
Access 03 in WinXP

I am trying to set the recordsource for a form to a recordset which is
defined by which company database I am currently in. In defining the SQL
statement, I then need to define the ControlSource property for two text
boxes to the two fields in the SQL statement, which unfortunately are
different based upon the company database.

Dim MySQL, MyCompany as String

' MyCompany is defined in a lookup

If MyCompany = "ABC" Then
MySQL = "Select Address1, Address2 From TempABC;"
Forms!frmVerifyInfo.RecordSource = MySQL
Me.txtVerify1.ControlSource = MySQL.Address1
Me.txtVerify2.ControlSource = MySQL.Address2
ElseIf MyCompany = "XYZ" Then
MySQL = "Select Company1, Company2 From TempABC;"
Forms!frmVerifyInfo.RecordSource = MySQL
Me.txtVerify1.ControlSource = MySQL.Company1
Me.txtVerify2.ControlSource = MySQL.Company2
End If

This is producing the error on the first ControlSource definition with the
"MySQL." giving the error message "Invalid Qualifier". If I change the
definition from MySQL.Address1 to [Address1], and so on, then the form
opens
but all records are have the data of the first record.

I initially tried using rs as Recordset, defining my recordset with the
SQL
statement, and then setting the RecordSource to the rs recordset, but this
gave me an error of Type Mismatch.

Any suggestions on how to accomplish what I am trying to do will be truly
appreciated.

Thanks!
Click to expand...
Click to expand...
 
Try something like
Me.txtVerify1Length.ControlSource = "=Len([Address1])"
.....
Me.txtVerify1Length.ControlSource = "=Len([Company2])"


--
\\// Live Long and Prosper \\//
BS"D


Pendragon said:
Excellent!

One more piece. I need to have a field that displays the length of the
data. I'm using Me.txtVerify1Length = Len(Me.txtVerify1) but this is giving
me #Name?

Can you provide the proper syntax?

Brendan Reynolds said:
Me.txtVerify1.ControlSource = "Address1"
....
Me.txtVerify2.ControlSource = "Company2"

--
Brendan Reynolds
Access MVP


Pendragon said:
Access 03 in WinXP

I am trying to set the recordsource for a form to a recordset which is
defined by which company database I am currently in. In defining the SQL
statement, I then need to define the ControlSource property for two text
boxes to the two fields in the SQL statement, which unfortunately are
different based upon the company database.

Dim MySQL, MyCompany as String

' MyCompany is defined in a lookup

If MyCompany = "ABC" Then
MySQL = "Select Address1, Address2 From TempABC;"
Forms!frmVerifyInfo.RecordSource = MySQL
Me.txtVerify1.ControlSource = MySQL.Address1
Me.txtVerify2.ControlSource = MySQL.Address2
ElseIf MyCompany = "XYZ" Then
MySQL = "Select Company1, Company2 From TempABC;"
Forms!frmVerifyInfo.RecordSource = MySQL
Me.txtVerify1.ControlSource = MySQL.Company1
Me.txtVerify2.ControlSource = MySQL.Company2
End If

This is producing the error on the first ControlSource definition with the
"MySQL." giving the error message "Invalid Qualifier". If I change the
definition from MySQL.Address1 to [Address1], and so on, then the form
opens
but all records are have the data of the first record.

I initially tried using rs as Recordset, defining my recordset with the
SQL
statement, and then setting the RecordSource to the rs recordset, but this
gave me an error of Type Mismatch.

Any suggestions on how to accomplish what I am trying to do will be truly
appreciated.

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Absolutely perfect!

Ofer said:
Try something like
Me.txtVerify1Length.ControlSource = "=Len([Address1])"
....
Me.txtVerify1Length.ControlSource = "=Len([Company2])"


--
\\// Live Long and Prosper \\//
BS"D


Pendragon said:
Excellent!

One more piece. I need to have a field that displays the length of the
data. I'm using Me.txtVerify1Length = Len(Me.txtVerify1) but this is giving
me #Name?

Can you provide the proper syntax?

Brendan Reynolds said:
Me.txtVerify1.ControlSource = "Address1"
....
Me.txtVerify2.ControlSource = "Company2"

--
Brendan Reynolds
Access MVP


Access 03 in WinXP

I am trying to set the recordsource for a form to a recordset which is
defined by which company database I am currently in. In defining the SQL
statement, I then need to define the ControlSource property for two text
boxes to the two fields in the SQL statement, which unfortunately are
different based upon the company database.

Dim MySQL, MyCompany as String

' MyCompany is defined in a lookup

If MyCompany = "ABC" Then
MySQL = "Select Address1, Address2 From TempABC;"
Forms!frmVerifyInfo.RecordSource = MySQL
Me.txtVerify1.ControlSource = MySQL.Address1
Me.txtVerify2.ControlSource = MySQL.Address2
ElseIf MyCompany = "XYZ" Then
MySQL = "Select Company1, Company2 From TempABC;"
Forms!frmVerifyInfo.RecordSource = MySQL
Me.txtVerify1.ControlSource = MySQL.Company1
Me.txtVerify2.ControlSource = MySQL.Company2
End If

This is producing the error on the first ControlSource definition with the
"MySQL." giving the error message "Invalid Qualifier". If I change the
definition from MySQL.Address1 to [Address1], and so on, then the form
opens
but all records are have the data of the first record.

I initially tried using rs as Recordset, defining my recordset with the
SQL
statement, and then setting the RecordSource to the rs recordset, but this
gave me an error of Type Mismatch.

Any suggestions on how to accomplish what I am trying to do will be truly
appreciated.

Thanks!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Checking for a date in a table by using a recordset 3
set recordsource of a subform 3
Getting Around VBA Not Resolving Query Refs To Forms? 8
Setting a comboBox recordsource to a parameter query or recordset 2
Need help with Code Please!!! 26
ADP forms-Which is better-bound recordsource or dynamic recordset? 1
Moving up and down in a recordset to set userform values? 2
I need to set the sub-form RecordSource when I open, refresh or up 3

Back
Top