Visual Basic & Access Newbie - Help!

G

gjoneshtfc

Hello

I have never used VB before but have been asked to look at coding a
button in Microsoft Access. The button currently exits a form and opens
a new form and works great. However, when the new form opens i want it
to open with information about the record i had open in the previous
form.

For example: I am looking at a record for everything to do with invoice
number 50 in the first form. The second form is the invoice print
layout that i open if i need to print the invoice information alone.
When i click on the button in the first form i need to have invoice
number 50 open in the second form.

Ideally i need to store the record number and then have it open in the
next form - if this is possible??!! Alternatively i could store the
stock number or invoice number and then have the button find the record
in the next form. I have tried a few things but they all give errors or
fail to work!

Any help will be greatly appreciated.

Regards, Gareth Jones
 
R

rico

Gareth,

I think the solution is much simpler. I'm assuming the only reason to open
the second form is to print the information in the first?

Assuming this is correct, my first suggestion would be to make the second
form a report and not a form, as these are design to be for printing, were
forms are not.(Have a look at the reports in the northwind database for
examples of how reports work).

Then in the button you are reffering to you would need something like the
following:

Docmd.openreport "TheReport",,,"[FormOneID]=" & Me![ReportID]"

This 'opens' the report which has the same primary id number as your form,
prints it then closes it.

If you want to see your report before you print it change the above to:

Docmd.openreport "TheReport",acViewPreview,,"[FormOneID]=" & Me![ReportID]"

Hope i have understood your situation correctly.

HTH

Rico
 
R

rico

Sorry just realised i got the where condition the wrong way round, should be
as follows:

Docmd.openreport "TheReport",,,"[ReportID]=" & Me![FormOneID]"

HTH

Rico
 
G

gjoneshtfc

Hello Rico

Thanks for the reply first of all!

Second of all you assume correct. I am trying the report option now.
The reason i was using a form is because the person who wants this
database wants to be able to edit the information after clicking
invoice. (I know it makes no sense as she could edit it in the first
form but she's the client!!)

My VB skills are very weak (actually non-existing!) so may have some
questions about the code you wrote if i cant get it to work!

Actually... have just tried it... I get errors. In your code you have
one speach mark at the end. Is this correct as it is not matched to
anything else? Also, what is Me! - do i need to change that to
something in my DB? My FormOneID bit is "[Stock No]=" and my ReportID
bit is Me![Stock No] currently.

Thanks again for your help
Regards, Gareth
 
R

rico

Sorry Gareth rushed the code didn't check it properly, i'll try again:

DoCmd.OpenReport "NameofReport", , , "[Stock No] = " & Me![Stock No] & ""

This SHOULD work lol.

He 'Me' mean the current form, saves you writing '[Forms]![MyFormName]',
when you are reffering to controls within the current form.

Also, bad idea to have spaces in your control names, will be fine most of
the time, but eventually will give you problems.

Regards

Rico
 
G

gjoneshtfc

Hey

Thanks again for the reply... I now get the error message:

"Data type mismatch in criteria expression"

It doesnt even open the report anymore!

Any ideas?

Regards, Gareth
 
R

rico

This means that the datatype of your two ID's are not the same. Go to tables
that supply both and check 'DataType' and 'fieldsize'. If they are not the
same you will not be able to reference them to each other untill they are the
same. This also suggests to me that maybe you are not using a unique ID for
stock no.

HTH

Rico
 
G

gjoneshtfc

Hey,

There is only one table that contains the Stock No - its primary key so
it is unique. Both forms take the stock number from that table so it is
the same - confusing huh?!

Thanks again
Gareth
 
R

rico

The only thing i can think of is that if the datatype of Stock No is not a
number (which i doubt it is) you would need to change the code slightly:

DoCmd.OpenReport "Report", , , "[Stock No] = '" & Me![Stock No] & "'"
 
G

gjoneshtfc

Hello

I have sorted the problem out using a button to open up the form as she
wanted. It was very simple in the end! In the button wizard there is
the option to open a form and find specific data - which is exactly
what i wanted!

Thanks for your help on the matter though, without it i wouldn't have
found this option!

Regards, Gareth
 
G

gjoneshtfc

Hello

Just out of interest - this does now open the report but the report is
blank! I have no idea why cause if you open the report itself without
using the button it works fine! Like I said though - i have sorted the
problem as described below

Thanks for all your help.
Kind regards
Gareth
 
R

rico

This is because the stock no to stock no connection is not working, so it
returns no records. If you just do Docmd.openreport "ReportName" then it will
open all the records.

Regards

Rico
 
Top