Select from List Box

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Franko - 22 Nov 2006 13:25 GMT

Have a list of names & Phone# in list box on Form1, want a macro to copy
selected names and paste to a table. Table is a subform in Form1(no
relationship). All field names and format are similar)

I have
the form is 'Form1'
the list box is SelectNames
the table is ListOfNames(subform name is fsubListOfNames)

Macro -- (On Dbl Click)

GoToControl -- fsubListOfNames
GoToRecord -- New

SetValue -- Item -- [Forms]![Form1]![fsubListOfNames]
Expression -- [Forms]![Form1]![SelectNames]

I step through the macro, and ok until SetValue, then error message. Please
help.

Reply to this Message

Steve Schapel - 24 Nov 2006 02:23 GMT

Franko,

In the Item argument, you will need to include reference to the specific
textbox within the fsubListOfNames subform where you want the
SelectNames value entered. So, something like this...
[Forms]![Form1]![fsubListOfNames].[Form]![ClientName]

However, if your macro is being called from an event within the Form1
form, then you don't need to reference the parent form. So the macro
arguments would be more like this...
Item: [fsubListOfNames].[Form]![ClientName]
Expression: [SelectNames]

Having said that, what you are doing here is very unusual, and possibly
indicates that your table design could benefit form revision.

Signature

Steve Schapel, Microsoft Access MVP

> Have a list of names & Phone# in list box on Form1, want a macro to copy
> selected names and paste to a table. Table is a subform in Form1(no
[quoted text clipped - 15 lines]
> I step through the macro, and ok until SetValue, then error message. Please
> help.

Reply to this Message

Franko - 26 Nov 2006 15:22 GMT

thank your for responding, makes sense and i will give it a try.

This is unusual and i keep thinking i am going about this db the wrong way.
The tables i mentioned were renamed for simplicity. The actual database is
medical and designed to add a new procedure at a hospital (such as Liver
Biopsy), then select billing codes (including values) from a list box, so
that we will know which codes we can bill for when this Liver Biopsy is
performed. Is there an easier way to select many codes from a list and paste
to a table, that a can build a report on?

> Franko,
>
[quoted text clipped - 31 lines]
> > I step through the macro, and ok until SetValue, then error message. Please
> > help.

Reply to this Message

Steve Schapel - 26 Nov 2006 18:08 GMT

Franko,

Normally, you would need 3 tables, something like this:

Procedures
    ProcedureID
    ProcedureName
    etc
This is a "master list" of all procedures

BillingCodes
    BillingCode
    Description
    StandardCharge
    etc
This is a "master list" of all codes

ProcedureCodes
    ProcedureCodeID
    ProcedureID
    BillingCode
This shows the combination of procedures and codes, i.e. which billing
codes are applicable to which procedures.

Data entry interface would almost certainly include a form based on the
Procedures table, with a continuous view subform based on the
ProcedureCodes table. This would allow the functionality that you
mentioned, i.e. the entry of a new procedure, along with all applicable
billing codes for that procedure.

In a nutshell... hope it makes sense.

Signature

Steve Schapel, Microsoft Access MVP

> thank your for responding, makes sense and i will give it a try.
>
[quoted text clipped - 41 lines]
>>> I step through the macro, and ok until SetValue, then error message. Please
>>> help.

Reply to this Message

Franko - 26 Nov 2006 21:24 GMT

Yes that does make sense, something i have tried, but maybe did not setup
correctly. In the subform I a datasheet view, with a drop down for the first
field, but could not get additional fields (such as description, value..), I
will give it a try, would be simpler than the method i am trying. Thanks alot
for the help.
Francis Debelo

> Franko,
>
[quoted text clipped - 73 lines]
> >>> I step through the macro, and ok until SetValue, then error message. Please
> >>> help.

Reply to this Message