Visual Basic & Access Newbie - Help!

#1 (**permalink**) 28-Oct-2005, 17:59 PM

Hello

I have never used VB before but have been asked to look at coding a
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/5981-visual-basic-and-access-newbie-help.html
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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=5981
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

#2 (**permalink**) 28-Oct-2005, 18:00 PM

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

"gjoneshtfc@volcanomail.com" wrote:

> 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=5981
> When i click on the button in the first form i need to have invoice
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=5981
> 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
>
>

#3 (**permalink**) 28-Oct-2005, 18:00 PM

Sorry just realised i got the where condition the wrong way round, should be
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=5981
as follows:

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

HTH

Rico

"gjoneshtfc@volcanomail.com" wrote:

> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=5981
> 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
>
>

#4 (**permalink**) 28-Oct-2005, 18:00 PM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=5981
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=5981
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

#5 (**permalink**) 28-Oct-2005, 18:00 PM

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

.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=5981

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

"gjoneshtfc@volcanomail.com" wrote:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=5981

> 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
>
>

#6 (**permalink**) 28-Oct-2005, 18:00 PM

Hey

Thanks again for the reply... I now get the error message:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=5981

"Data type mismatch in criteria expression"

It doesnt even open the report anymore!

Any ideas?

Regards, Gareth

#7 (**permalink**) 28-Oct-2005, 18:00 PM

This means that the datatype of your two ID's are not the same. Go to tables
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=5981
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

"gjoneshtfc@volcanomail.com" wrote:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=5981

> 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
>
>

#8 (**permalink**) 28-Oct-2005, 18:00 PM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=5981
the same - confusing huh?!

Thanks again
Gareth

#9 (**permalink**) 28-Oct-2005, 18:00 PM

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] & "'"

"gjoneshtfc@volcanomail.com" wrote:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=5981
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=5981

> 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
>
>