Need help with a Custom Function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Hunter57 - 14 May 2007 23:00 GMT

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

Reply to this Message

mscertified - 15 May 2007 00:34 GMT

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
[quoted text clipped - 42 lines]
> Thanks in advance,
> Hunter57

Reply to this Message

Hunter57 - 15 May 2007 01:45 GMT

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'
[quoted text clipped - 49 lines]
> > Thanks in advance,
> > Hunter57

Reply to this Message

Sergey Poberezovskiy - 15 May 2007 05:38 GMT

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.

> Hi Dorian,
>
[quoted text clipped - 66 lines]
> > > Thanks in advance,
> > > Hunter57

Reply to this Message

Hunter57 - 15 May 2007 07:48 GMT

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

> Dorian,
>
[quoted text clipped - 89 lines]
> > > > Thanks in advance,
> > > > Hunter57

Reply to this Message

Sergey Poberezovskiy - 15 May 2007 09:03 GMT

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.

> Hello Mr. Poberezovskiy,
>
[quoted text clipped - 101 lines]
> > > > > Thanks in advance,
> > > > > Hunter57

Reply to this Message

Bob Hairgrove - 15 May 2007 09:34 GMT

>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
[quoted text clipped - 8 lines]
>Private Sub cboClassIDMIV_AfterUpdate()
> Dim ctlCurrentControl As Control

Try this instead:
Dim ctlCurrentControl As ComboBox

> Dim strControlName As String
> Set ctlCurrentControl = Screen.ActiveControl

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)

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)

and this should be:
Public Function FillTxtBoxes(strControlName As ComboBox, _
myStr As String)

>
> Forms!Schedule1![txtBatches '" & myStr & "'"] = strControlName.Column(1)
[quoted text clipped - 4 lines]
> Forms!Schedule1![txtPeriod '" & myStr & "'"] = strControlName.Column(6)
> Forms!Schedule1![txtDay '" & myStr & "'"] = strControlName.Column(7)

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

>End Function
>
[quoted text clipped - 7 lines]
>Thanks in advance,
>Hunter57

--
Bob Hairgrove
NoSpamPlease@Home.com

Reply to this Message

Hunter57 - 16 May 2007 18:35 GMT

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

> >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
[quoted text clipped - 70 lines]
> Bob Hairgrove
> NoSpamPlease@Home.com

Reply to this Message