Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 20th, 2003, 11:27 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 126
Thanks: 5
Thanked 0 Times in 0 Posts
Default Macro or Code?

Good morning all,

Here is some background on the database that this applies to:
The database is for Quotes. It has many tables and relationships. The form that I want to apply this to is based off of a query. That query is based off of table1 and table 2. Table1 has the RFQ number as the primary key. Table2 has an auto number as the primary key. The relationship is a one-to-many using the RFQ number field of each table. When the end user opens the form to fill out his information, all the records are already there. I originally created them by filling in the info in Table1. The info he fills in ends up in table2. Sometimes, the end user will need to fill out more than one form for one RFQ number.

This brings me to what I want to do:
I have a button on the form. I want this button to open table2, add a new record and enter the same RFQ number in its field as the one on the form, and close the table.
For instance, say the form is opened to RFQ # 12345. When I click on the button, I want Access to open table2, add a new record, enter "12345" into the RFQ number field, close the table, and return to the form.

Should I use code for this or a macro? And how would I do this?

Thanks for the help!
Laura
__________________
Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!
 
Old June 21st, 2003, 09:51 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm not sure if I've got this right - but basically, as I see it - all you're wanting to do is add a record into a table with the RFQ field being the same as the form that's open.
If so, then you can just put an insert statement into the button - something along the lines of:
Code:
DoCmd.RunSQL ("INSERT INTO Table2 VALUES (" & Me.RFQ & ");")
But I'm thinking I got something wrong - since you said you want it to open table2, enter the value and then close the table.
Why do you want to open and close the table?

Steven

There are 10 kinds of people in the world - those who understand binary - and those with friends
 
Old June 23rd, 2003, 10:01 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 126
Thanks: 5
Thanked 0 Times in 0 Posts
Default

Good morning,
The reason I mentioned opening and closing the table, is that I assumed that it would need to do that if a macro was used since the table wasn't open at the time of clicking on the button.

When I used this line of code, I received the following error:
"Runtime Error # 3346
Number of query values and destination fields are not the same."

Not quite sure what to do next.

For my own knowledge, in (" & me.rfq & "), I understand the 'me.rfq' portion, but what does the '&' and spaces represent?

Thanks!
Laura
 
Old June 23rd, 2003, 12:32 PM
Authorized User
 
Join Date: Jun 2003
Posts: 91
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I agree with Steven it sounds like a simple SQL INSERT statement would suffice. I have found thru time that using code is far better than using macros for the primary reason of error trapping - which you can't do with a macro.

Kenny Alligood
 
Old June 23rd, 2003, 07:18 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

& is basically the same as it is in normal language - i.e. AND

Since the SQL statement is a string it's basically saying:
This string AND this variable AND this other string

which puts them all together into the main string.
As an example -
variable1="This"
variable2="thing"
which means that variable1 & variable2 = "Thisthing"
it's basically a joiner.

Some people use + instead of & but the standard, as far as I know is to use &

There are 10 kinds of people in the world - those who understand binary - and those with friends
 
Old August 23rd, 2004, 03:23 PM
Registered User
 
Join Date: Aug 2004
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to Bharani Send a message via MSN to Bharani Send a message via Yahoo to Bharani
Default

18991

 
Old August 23rd, 2004, 03:27 PM
Registered User
 
Join Date: Aug 2004
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to Bharani Send a message via MSN to Bharani Send a message via Yahoo to Bharani
Default

Hi,

Bug#45094: marked as done (apt: apt could use a preference system for selecting from multiple URIs)
 Any idea??

 
Old August 24th, 2004, 01:00 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

What's the deal with bringing back to life old threads, with posts that make no sense?

I am a loud man with a very large hat. This means I am in charge





Similar Threads
Thread Thread Starter Forum Replies Last Post
calling to xlam macro from macro inside xlsb SteveB Excel VBA 0 June 30th, 2008 06:43 PM
Run code in Macro Corey Access VBA 4 February 5th, 2007 10:54 AM
How to protect macro code? kotaiah Excel VBA 1 September 25th, 2006 02:57 PM
Need Help For A Macro Code vicky.dalmia Excel VBA 1 December 16th, 2005 07:37 AM
Hide VBA code when forwarding macro sh333384 Excel VBA 7 September 2nd, 2004 10:23 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.