Excel/Access

[justjohn]

I have a database at work which includes the account numbers of customers.

In an Excel spreadsheet I have produced a job card for use in the workshop.

I would like to be able to run a macro that takes details like name and address etc and puts them in appropriate cells.

I would like it to do this by searching for an account number and then copying the required data.
I can get it to run but only for an account number I put in as an example.

any help would be appreciated, particularly if it is explained simply.

thanks

John

[Bob Phillips]

What do you mean by search for an account number, do you mean pass that in
the query? That should be straight-forward, but explain where you will get
that number from, and post your existing code.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"justjohn" <[email protected]> wrote in
message news:[email protected]...
>
> I have a database at work which includes the account numbers of
> customers.
>
> In an Excel spreadsheet I have produced a job card for use in the
> workshop.
>
> I would like to be able to run a macro that takes details like name and
> address etc and puts them in appropriate cells.
>
> I would like it to do this by searching for an account number and then
> copying the required data.
> I can get it to run but only for an account number I put in as an
> example.
>
> any help would be appreciated, particularly if it is explained simply.
>
> thanks
>
> John
>
>
> --
> justjohn
> ------------------------------------------------------------------------
> justjohn's Profile:
http://www.excelforum.com/member.php...o&userid=30663
> View this thread: http://www.excelforum.com/showthread...hreadid=503672
>

[justjohn]

Hi Bob,
and thankyou for taking the time to reply.

As you may have guessed, my ability to use excel and access is VERY limited. I use it in a very simple manner and have no knowledge at all of visual basic, and I think that was the code that you refer to.

I will try to give more details of the problem, and perhaps you could take the time to help.

Access database contains all our customers' details (rows), these include account number, name, address, postcode, vehicle details (columns).

I have made a simple jobcard in excel to be printed for use in the workshop.

instead of typing or copying and pasting the customer details from the database to the jobcard each time a jobcard is required I thought that it could be possible for MS to "search" (if that it the right word) through the database for name and address details and copy them to the appropriate cells in the excel jobcard. This "search" would be "triggered" by putting a customer's account number into a cell in the excel spreadsheet/jobcard that would be somehow attached to a function/macro.

As you can tell by what is probably poor use of terminology I am way out of my depth here, and I have no idea whether any of this is possible.

The part that I thought I had working was a simple copy and paste macro, but even that failed.

If you can help/advise/write whatever it takes I would be very grateful, but thanks for reading anyway.

PS
thanks for the previous advice that you gave on validation too, it turned out to be exactly what I needed.

[Bob Phillips]

That should be easy. The way that I would do it would be to write an ADO/SQL
query agfainst the database and pull back the data for the jobcard.

You need a method to get the job id, a simple InputBox would suffice, then
just pass that in the query.

If you send me the database and a copy of your Excel jobcard, I can knock up
a simple demo for you.

Send to

bob (dot) phillips (at) tiscali (dot) co (dot) uk

do the obvious with the bits in brackets

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"justjohn" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Bob,
> and thankyou for taking the time to reply.
>
> As you may have guessed, my ability to use excel and access is VERY
> limited. I use it in a very simple manner and have no knowledge at all
> of visual basic, and I think that was the code that you refer to.
>
> I will try to give more details of the problem, and perhaps you could
> take the time to help.
>
> Access database contains all our customers' details (rows), these
> include account number, name, address, postcode, vehicle details
> (columns).
>
> I have made a simple jobcard in excel to be printed for use in the
> workshop.
>
> instead of typing or copying and pasting the customer details from the
> database to the jobcard each time a jobcard is required I thought that
> it could be possible for MS to "search" (if that it the right word)
> through the database for name and address details and copy them to the
> appropriate cells in the excel jobcard. This "search" would be
> "triggered" by putting a customer's account number into a cell in the
> excel spreadsheet/jobcard that would be somehow attached to a
> function/macro.
>
> As you can tell by what is probably poor use of terminology I am way
> out of my depth here, and I have no idea whether any of this is
> possible.
>
> The part that I thought I had working was a simple copy and paste
> macro, but even that failed.
>
> If you can help/advise/write whatever it takes I would be very
> grateful, but thanks for reading anyway.
>
> PS
> thanks for the previous advice that you gave on validation too, it
> turned out to be exactly what I needed.
>
>
> --
> justjohn
> ------------------------------------------------------------------------
> justjohn's Profile:
http://www.excelforum.com/member.php...o&userid=30663
> View this thread: http://www.excelforum.com/showthread...hreadid=503672
>