Need help with a Custom Function

G

Guest

I am designing (as a public service) a database for a client who wants a lot
of text boxes on a form to help him set up colleges class schedules. There
are 7 textboxes that go with each ComboBox. I am using the ComboBox query to
autopopulate the TextBoxes. I have everything pretty well set up except for a
custom function I am trying to create to save a lot of redundant coding in a
form module. I do not have much experience with custom functions and would
appreciate some help. Here is what I have so far:

In the Form Module:

Private Sub cboClassIDMIV_AfterUpdate()
Dim ctlCurrentControl As Control
Dim strControlName As String
Set ctlCurrentControl = Screen.ActiveControl
strControlName = ctlCurrentControl.Name

Dim myStr As String

myStr = "MIV"
Call FillTxtBoxes(strControlName, myStr)

End Sub


In a Module I named GlobalCode:

Public Function FillTxtBoxes(strControlName As Control.Name, myStr As String)

Forms!Schedule1![txtBatches '" & myStr & "'"] = strControlName.Column(1)
Forms!Schedule1![txtSubject '" & myStr & "'"] = strControlName.Column(2)
Forms!Schedule1![txtClassType '" & myStr & "'"] = strControlName.Column(3)
Forms!Schedule1![txtTeacher '" & myStr & "'"] = strControlName.Column(4)
Forms!Schedule1![txtVenue '" & myStr & "'"] = strControlName.Column(5)
Forms!Schedule1![txtPeriod '" & myStr & "'"] = strControlName.Column(6)
Forms!Schedule1![txtDay '" & myStr & "'"] = strControlName.Column(7)

End Function

I have tried various things but I keep getting error messages concerning the
syntax of the strControlName Variable in the Function.

Also, I am looking for a way to trim the first 10 letters off of the
ComboBox name so I can use the rest of the name to pass on as a variable to
add to the textbox names as the "myStr" Variable and save more time.

Thanks in advance,
Hunter57
 
G

Guest

In your function strControlName needs to be declared as STRING.
You have 'strControlName As Control.Name'
You need 'strControlName As String'
There may be other problems but that one jumps out at me.

-Dorian
 
G

Guest

Hi Dorian,

Thanks for your input. I have declared it as string, control, comboBox, and
anything else I can think of and I still get an error. I declare it as
string like this:

Public Function FillTxtBoxes(strControlName As String, myStr As String)

and when I compile the code I get this error Message:

Compile Error:

Invalid Qualifier

Thanks for trying to help.
Hunter57

mscertified said:
In your function strControlName needs to be declared as STRING.
You have 'strControlName As Control.Name'
You need 'strControlName As String'
There may be other problems but that one jumps out at me.

-Dorian

Hunter57 said:
I am designing (as a public service) a database for a client who wants a lot
of text boxes on a form to help him set up colleges class schedules. There
are 7 textboxes that go with each ComboBox. I am using the ComboBox query to
autopopulate the TextBoxes. I have everything pretty well set up except for a
custom function I am trying to create to save a lot of redundant coding in a
form module. I do not have much experience with custom functions and would
appreciate some help. Here is what I have so far:

In the Form Module:

Private Sub cboClassIDMIV_AfterUpdate()
Dim ctlCurrentControl As Control
Dim strControlName As String
Set ctlCurrentControl = Screen.ActiveControl
strControlName = ctlCurrentControl.Name

Dim myStr As String

myStr = "MIV"
Call FillTxtBoxes(strControlName, myStr)

End Sub


In a Module I named GlobalCode:

Public Function FillTxtBoxes(strControlName As Control.Name, myStr As String)

Forms!Schedule1![txtBatches '" & myStr & "'"] = strControlName.Column(1)
Forms!Schedule1![txtSubject '" & myStr & "'"] = strControlName.Column(2)
Forms!Schedule1![txtClassType '" & myStr & "'"] = strControlName.Column(3)
Forms!Schedule1![txtTeacher '" & myStr & "'"] = strControlName.Column(4)
Forms!Schedule1![txtVenue '" & myStr & "'"] = strControlName.Column(5)
Forms!Schedule1![txtPeriod '" & myStr & "'"] = strControlName.Column(6)
Forms!Schedule1![txtDay '" & myStr & "'"] = strControlName.Column(7)

End Function

I have tried various things but I keep getting error messages concerning the
syntax of the strControlName Variable in the Function.

Also, I am looking for a way to trim the first 10 letters off of the
ComboBox name so I can use the rest of the name to pass on as a variable to
add to the textbox names as the "myStr" Variable and save more time.

Thanks in advance,
Hunter57
Click to expand...
Click to expand...
 
G

Guest

Dorian,

If you need to reference Combobox in your custom function, you will want to
pass the reference to this control into your function, something like:

Public Sub FillTextBoxes(cmb As ComboBox, frmName As String, myStr As String)
On Error GoTo Fill_Err
With Forms(frmName).Controls
' the line below assumes that if myStr='ABC' then the control name is
txtBatchesABC - please tell me if that is not what you want
.Item("txtBatches" & myStr) = cmb.Column(1)
...
End With
Fill_Exit:
Exit Sub
Fill_Err:
MsgBox Err.Description
Resume Fill_Exit
End Sub

As your Function does not return anything it would make sense to use a Sub
instead. I have also included some generic error handling code for you.


Hunter57 said:
Hi Dorian,

Thanks for your input. I have declared it as string, control, comboBox, and
anything else I can think of and I still get an error. I declare it as
string like this:

Public Function FillTxtBoxes(strControlName As String, myStr As String)

and when I compile the code I get this error Message:

Compile Error:

Invalid Qualifier

Thanks for trying to help.
Hunter57

mscertified said:
In your function strControlName needs to be declared as STRING.
You have 'strControlName As Control.Name'
You need 'strControlName As String'
There may be other problems but that one jumps out at me.

-Dorian

Hunter57 said:
I am designing (as a public service) a database for a client who wants a lot
of text boxes on a form to help him set up colleges class schedules. There
are 7 textboxes that go with each ComboBox. I am using the ComboBox query to
autopopulate the TextBoxes. I have everything pretty well set up except for a
custom function I am trying to create to save a lot of redundant coding in a
form module. I do not have much experience with custom functions and would
appreciate some help. Here is what I have so far:

In the Form Module:

Private Sub cboClassIDMIV_AfterUpdate()
Dim ctlCurrentControl As Control
Dim strControlName As String
Set ctlCurrentControl = Screen.ActiveControl
strControlName = ctlCurrentControl.Name

Dim myStr As String

myStr = "MIV"
Call FillTxtBoxes(strControlName, myStr)

End Sub


In a Module I named GlobalCode:

Public Function FillTxtBoxes(strControlName As Control.Name, myStr As String)

Forms!Schedule1![txtBatches '" & myStr & "'"] = strControlName.Column(1)
Forms!Schedule1![txtSubject '" & myStr & "'"] = strControlName.Column(2)
Forms!Schedule1![txtClassType '" & myStr & "'"] = strControlName.Column(3)
Forms!Schedule1![txtTeacher '" & myStr & "'"] = strControlName.Column(4)
Forms!Schedule1![txtVenue '" & myStr & "'"] = strControlName.Column(5)
Forms!Schedule1![txtPeriod '" & myStr & "'"] = strControlName.Column(6)
Forms!Schedule1![txtDay '" & myStr & "'"] = strControlName.Column(7)

End Function

I have tried various things but I keep getting error messages concerning the
syntax of the strControlName Variable in the Function.

Also, I am looking for a way to trim the first 10 letters off of the
ComboBox name so I can use the rest of the name to pass on as a variable to
add to the textbox names as the "myStr" Variable and save more time.

Thanks in advance,
Hunter57
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hello Mr. Poberezovskiy,

Thank you for your help. Yes, I do want to add the Variable myStr to the
end of txtBatches.

I tried your suggestions but I am still betting an error code saying
concerning the Variable strControlName. It reads, Compile Error: ByRef
arguement type mismatch.

Thanks for your help,
Hunter57

Sergey Poberezovskiy said:
Dorian,

If you need to reference Combobox in your custom function, you will want to
pass the reference to this control into your function, something like:

Public Sub FillTextBoxes(cmb As ComboBox, frmName As String, myStr As String)
On Error GoTo Fill_Err
With Forms(frmName).Controls
' the line below assumes that if myStr='ABC' then the control name is
txtBatchesABC - please tell me if that is not what you want
.Item("txtBatches" & myStr) = cmb.Column(1)
...
End With
Fill_Exit:
Exit Sub
Fill_Err:
MsgBox Err.Description
Resume Fill_Exit
End Sub

As your Function does not return anything it would make sense to use a Sub
instead. I have also included some generic error handling code for you.


Hunter57 said:
Hi Dorian,

Thanks for your input. I have declared it as string, control, comboBox, and
anything else I can think of and I still get an error. I declare it as
string like this:

Public Function FillTxtBoxes(strControlName As String, myStr As String)

and when I compile the code I get this error Message:

Compile Error:

Invalid Qualifier

Thanks for trying to help.
Hunter57

mscertified said:
In your function strControlName needs to be declared as STRING.
You have 'strControlName As Control.Name'
You need 'strControlName As String'
There may be other problems but that one jumps out at me.

-Dorian

:

I am designing (as a public service) a database for a client who wants a lot
of text boxes on a form to help him set up colleges class schedules. There
are 7 textboxes that go with each ComboBox. I am using the ComboBox query to
autopopulate the TextBoxes. I have everything pretty well set up except for a
custom function I am trying to create to save a lot of redundant coding in a
form module. I do not have much experience with custom functions and would
appreciate some help. Here is what I have so far:

In the Form Module:

Private Sub cboClassIDMIV_AfterUpdate()
Dim ctlCurrentControl As Control
Dim strControlName As String
Set ctlCurrentControl = Screen.ActiveControl
strControlName = ctlCurrentControl.Name

Dim myStr As String

myStr = "MIV"
Call FillTxtBoxes(strControlName, myStr)

End Sub


In a Module I named GlobalCode:

Public Function FillTxtBoxes(strControlName As Control.Name, myStr As String)

Forms!Schedule1![txtBatches '" & myStr & "'"] = strControlName.Column(1)
Forms!Schedule1![txtSubject '" & myStr & "'"] = strControlName.Column(2)
Forms!Schedule1![txtClassType '" & myStr & "'"] = strControlName.Column(3)
Forms!Schedule1![txtTeacher '" & myStr & "'"] = strControlName.Column(4)
Forms!Schedule1![txtVenue '" & myStr & "'"] = strControlName.Column(5)
Forms!Schedule1![txtPeriod '" & myStr & "'"] = strControlName.Column(6)
Forms!Schedule1![txtDay '" & myStr & "'"] = strControlName.Column(7)

End Function

I have tried various things but I keep getting error messages concerning the
syntax of the strControlName Variable in the Function.

Also, I am looking for a way to trim the first 10 letters off of the
ComboBox name so I can use the rest of the name to pass on as a variable to
add to the textbox names as the "myStr" Variable and save more time.

Thanks in advance,
Hunter57
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hunter57,

If you want to resolve it in one go, please include the code where you call
your FixxTextBoxes function from. If there is more than one place you are
calling it from (say from multiple forms, or different controls) please
provide all typical examples.
To correctly write the code, we would need the exact names of:
- Form
- Combobox
- TextBoxes

Then I would be able to tell you the exact syntax, so that we will not have
compilation errors - it looks like you are trying to pass a string where
combobox is expected.


Hunter57 said:
Hello Mr. Poberezovskiy,

Thank you for your help. Yes, I do want to add the Variable myStr to the
end of txtBatches.

I tried your suggestions but I am still betting an error code saying
concerning the Variable strControlName. It reads, Compile Error: ByRef
arguement type mismatch.

Thanks for your help,
Hunter57

Sergey Poberezovskiy said:
Dorian,

If you need to reference Combobox in your custom function, you will want to
pass the reference to this control into your function, something like:

Public Sub FillTextBoxes(cmb As ComboBox, frmName As String, myStr As String)
On Error GoTo Fill_Err
With Forms(frmName).Controls
' the line below assumes that if myStr='ABC' then the control name is
txtBatchesABC - please tell me if that is not what you want
.Item("txtBatches" & myStr) = cmb.Column(1)
...
End With
Fill_Exit:
Exit Sub
Fill_Err:
MsgBox Err.Description
Resume Fill_Exit
End Sub

As your Function does not return anything it would make sense to use a Sub
instead. I have also included some generic error handling code for you.


Hunter57 said:
Hi Dorian,

Thanks for your input. I have declared it as string, control, comboBox, and
anything else I can think of and I still get an error. I declare it as
string like this:

Public Function FillTxtBoxes(strControlName As String, myStr As String)

and when I compile the code I get this error Message:

Compile Error:

Invalid Qualifier

Thanks for trying to help.
Hunter57

:

In your function strControlName needs to be declared as STRING.
You have 'strControlName As Control.Name'
You need 'strControlName As String'
There may be other problems but that one jumps out at me.

-Dorian

:

I am designing (as a public service) a database for a client who wants a lot
of text boxes on a form to help him set up colleges class schedules. There
are 7 textboxes that go with each ComboBox. I am using the ComboBox query to
autopopulate the TextBoxes. I have everything pretty well set up except for a
custom function I am trying to create to save a lot of redundant coding in a
form module. I do not have much experience with custom functions and would
appreciate some help. Here is what I have so far:

In the Form Module:

Private Sub cboClassIDMIV_AfterUpdate()
Dim ctlCurrentControl As Control
Dim strControlName As String
Set ctlCurrentControl = Screen.ActiveControl
strControlName = ctlCurrentControl.Name

Dim myStr As String

myStr = "MIV"
Call FillTxtBoxes(strControlName, myStr)

End Sub


In a Module I named GlobalCode:

Public Function FillTxtBoxes(strControlName As Control.Name, myStr As String)

Forms!Schedule1![txtBatches '" & myStr & "'"] = strControlName.Column(1)
Forms!Schedule1![txtSubject '" & myStr & "'"] = strControlName.Column(2)
Forms!Schedule1![txtClassType '" & myStr & "'"] = strControlName.Column(3)
Forms!Schedule1![txtTeacher '" & myStr & "'"] = strControlName.Column(4)
Forms!Schedule1![txtVenue '" & myStr & "'"] = strControlName.Column(5)
Forms!Schedule1![txtPeriod '" & myStr & "'"] = strControlName.Column(6)
Forms!Schedule1![txtDay '" & myStr & "'"] = strControlName.Column(7)

End Function

I have tried various things but I keep getting error messages concerning the
syntax of the strControlName Variable in the Function.

Also, I am looking for a way to trim the first 10 letters off of the
ComboBox name so I can use the rest of the name to pass on as a variable to
add to the textbox names as the "myStr" Variable and save more time.

Thanks in advance,
Hunter57
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Hairgrove

I am designing (as a public service) a database for a client who wants alot
of text boxes on a form to help him set up colleges class schedules. There
are 7 textboxes that go with each ComboBox. I am using the ComboBox query to
autopopulate the TextBoxes. I have everything pretty well set up except for a
custom function I am trying to create to save a lot of redundant coding in a
form module. I do not have much experience with custom functions and would
appreciate some help. Here is what I have so far:

In the Form Module:

Private Sub cboClassIDMIV_AfterUpdate()
Dim ctlCurrentControl As Control
Click to expand...

Try this instead:
Dim ctlCurrentControl As ComboBox
Dim strControlName As String
Set ctlCurrentControl = Screen.ActiveControl
Click to expand...

If you are running this in the AfterUpdate event of your combo box,
then you already know which control is the active control...just
write:

Set ctlCurrentControl = Me.cboClassIDMIV

Screen.ActveControl can sometimes play tricks on you. I would play it
safe and use the control name here.
strControlName = ctlCurrentControl.Name

Dim myStr As String

myStr = "MIV"
Call FillTxtBoxes(strControlName, myStr)
Click to expand...

This should be:
Call FillTxtBoxes(ctlCurrentControl, myStr)
End Sub


In a Module I named GlobalCode:

Public Function FillTxtBoxes(strControlName As Control.Name, myStr As String)
Click to expand...

and this should be:
Public Function FillTxtBoxes(strControlName As ComboBox, _
myStr As String)
Forms!Schedule1![txtBatches '" & myStr & "'"] = strControlName.Column(1)
Forms!Schedule1![txtSubject '" & myStr & "'"] = strControlName.Column(2)
Forms!Schedule1![txtClassType '" & myStr & "'"] = strControlName.Column(3)
Forms!Schedule1![txtTeacher '" & myStr & "'"] = strControlName.Column(4)
Forms!Schedule1![txtVenue '" & myStr & "'"] = strControlName.Column(5)
Forms!Schedule1![txtPeriod '" & myStr & "'"] = strControlName.Column(6)
Forms!Schedule1![txtDay '" & myStr & "'"] = strControlName.Column(7)
Click to expand...

The quotes are not correct. Can you post the real names of these
controls?
 
G

Guest

Hi Bob,

Thanks for your help. Your suggestions have helped, but I still can't get
it to work correctly. So I think I am going to use another method instead of
a function.

Thankfully yours,

Hunter57

Bob Hairgrove said:
I am designing (as a public service) a database for a client who wants a lot
of text boxes on a form to help him set up colleges class schedules. There
are 7 textboxes that go with each ComboBox. I am using the ComboBox query to
autopopulate the TextBoxes. I have everything pretty well set up except for a
custom function I am trying to create to save a lot of redundant coding in a
form module. I do not have much experience with custom functions and would
appreciate some help. Here is what I have so far:

In the Form Module:

Private Sub cboClassIDMIV_AfterUpdate()
Dim ctlCurrentControl As Control
Click to expand...

Try this instead:
Dim ctlCurrentControl As ComboBox
Dim strControlName As String
Set ctlCurrentControl = Screen.ActiveControl
Click to expand...

If you are running this in the AfterUpdate event of your combo box,
then you already know which control is the active control...just
write:

Set ctlCurrentControl = Me.cboClassIDMIV

Screen.ActveControl can sometimes play tricks on you. I would play it
safe and use the control name here.
strControlName = ctlCurrentControl.Name

Dim myStr As String

myStr = "MIV"
Call FillTxtBoxes(strControlName, myStr)
Click to expand...

This should be:
Call FillTxtBoxes(ctlCurrentControl, myStr)
End Sub


In a Module I named GlobalCode:

Public Function FillTxtBoxes(strControlName As Control.Name, myStr As String)
Click to expand...

and this should be:
Public Function FillTxtBoxes(strControlName As ComboBox, _
myStr As String)
Forms!Schedule1![txtBatches '" & myStr & "'"] = strControlName.Column(1)
Forms!Schedule1![txtSubject '" & myStr & "'"] = strControlName.Column(2)
Forms!Schedule1![txtClassType '" & myStr & "'"] = strControlName.Column(3)
Forms!Schedule1![txtTeacher '" & myStr & "'"] = strControlName.Column(4)
Forms!Schedule1![txtVenue '" & myStr & "'"] = strControlName.Column(5)
Forms!Schedule1![txtPeriod '" & myStr & "'"] = strControlName.Column(6)
Forms!Schedule1![txtDay '" & myStr & "'"] = strControlName.Column(7)
Click to expand...

The quotes are not correct. Can you post the real names of these
controls?
End Function

I have tried various things but I keep getting error messages concerning the
syntax of the strControlName Variable in the Function.

Also, I am looking for a way to trim the first 10 letters off of the
ComboBox name so I can use the rest of the name to pass on as a variable to
add to the textbox names as the "myStr" Variable and save more time.

Thanks in advance,
Hunter57
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

Referring to text control in a form 2
Compile error on VBA Function 13
Make a text box visible if a different text box has text in it 4
Custom function 4
Need help with Subform problems 3
Using a calculated Variable from a function in another function 7
Help with this function... 2
Function call 13

Top