Solved

Query Than Update Problem

Posted on 1999-01-29
3
120 Views
Last Modified: 2010-05-03
I am new to VB and having Problems creating the following situation;
Situation - Users input information for Product - must select from list
1)After entering information , I need to query a Different table,called "Parts", to check for Exact Matches in all fields that the user selected [ 8 fields ]
2) If match is found, I need to assign the pre-existing part # to the Product information that the customer selected, then save the new info to the "Parts" Table.
3) If match is not found,I need to assign new part number to the data and save to "Parts" Table.
 I am having problems writing the SQL statement for this operation and also how to setup the recordset operation.Any help would be greatly appreciated! Thanks!
0
Comment
Question by:apei
3 Comments
 

Expert Comment

by:meetze
ID: 1471218
what data access are you using...ADO,RDO, or DAO???
0
 
LVL 2

Expert Comment

by:cjwik
ID: 1471219
Show some code....
0
 
LVL 4

Accepted Solution

by:
arcusd earned 100 total points
ID: 1471220
here's some of the code

dim xstr as string
dim mset as Recordset

'assume that we mdb is the database

xstr = "SELECT * FROM Parts WHERE Field1 = " + EditBox1.Text + " _
 "AND Field2 = " + EditBox2.Text + """ _
" AND ...."""  + _
" AND Field8 = " + EditBox8.Text + """

set mset = mdb.Openrecordset(xstr,dbOpenDynaset)
if not mset.eof  'found then
   mset.Edit
   mset.Fields(0) = EditBox1.Text
else
   mset.Fields(0) = GetNewPartNo() 'assuming that the first field is the part no and                                                      GetNewPartNo() is a function that returns new part number
   mset.Addnew
end if

mset.Fields(1) = EditBox2.Text
..
mset.Fields(7) = EditBox8.Text

mset.Update

0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Vb.net 2008 2 51
add text to end of existing text in file 16 62
How to debug this code 7 51
MS Date Picker 64 bit 32 bit issue 12 43
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now