Advertisement

07.08.2008 at 01:52PM PDT, ID: 23548268
[x]
Attachment Details

Help updating records in a table

Asked by ryanbass in Access Forms

Tags: , ,

Hi everyone,  

I am hoping someone can help me out with my problem.  I am trying to help my boss out on a few projects but am very new to writing VBA.  I am trying to update a column in a table based off of a number in my invoice number table.  The invoice number will increment by one after each record in my orders table.  I have the function written so it will get the number from the invoice table and also written the code to update my orders table, but every time I run it I get the following error - "No value give for one or more required parameters."  Here is my function and my code to update the table:  

Thanks for all your helpStart Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:

Private Sub cmd_Export_Click()
 
Dim frm_VPD As String
Dim Title As String
 
Title = Me.cbo_title.Value
 
Dim x As Long
Dim cnConnection As ADODB.Connection
Set cnConnection = New ADODB.Connection
 
Dim rsInvoice As ADODB.Recordset
x = getInvoiceNum
 
Set rsInvoice = New ADODB.Recordset
 
With rsInvoice
    .Open "UPDATE tbl_VPDOrdersPlaced SET tbl_VPDOrdersPlaced.InvoiceNumber = x", CurrentProject.Connection
End With
 
Do Until rsInvoice.EOF
    rsInvoice.MoveNext
Loop
 
rsInvoice.Close
Set rsInvoice = Nothing
 
Dim strConnection As String
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & CurrentProject.Path & "\Studio Resale.mdb;"
cnConnection.Open strConnection
Dim sSql As String
 
sSql = "SELECT tbl_VPDOrdersPlaced.* INTO [" & Title & "_" & Format(Date, "mmddyy")
sSql = sSql & "]FROM tbl_VPDOrdersPlaced "
 
cnConnection.Execute sSql
 
DoCmd.TransferText acExportDelim, , "tbl_VPDOrdersPlaced", "\\cyclone\documents\ryan.bass\Studio Resale\" & [Title] & ".txt"
DoCmd.SetWarnings False
DoCmd.OpenQuery "DeleteOrders"
DoCmd.SetWarnings True
 
End Sub
 
 
 
Private Function getInvoiceNum()
Dim MaxInvNo As Long, curVal, newVal
If Nz(DMax("InvoiceNumber", "tbl_VPDOrdersPlaced"), "") = "" Then
    newVal = Nz(DMax("InvoiceNumber", "tbl_InvoiceNumber"))
    
    Else
    curVal = Nz(DMax("InvoiceNumber", "tbl_VPDOrdersPlaced"))
    
        MaxInvNo = Mid(curVal, 2)
        newVal = Left(curVal, 1) & MaxInvNo + 1
End If
getInvoiceNum = newVal
End Function
[+][-]07.08.2008 at 02:01PM PDT, ID: 21958217

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.08.2008 at 02:12PM PDT, ID: 21958309

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.08.2008 at 02:27PM PDT, ID: 21958438

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.08.2008 at 02:28PM PDT, ID: 21958457

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.08.2008 at 02:58PM PDT, ID: 21958693

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zone: Access Forms
Tags: Microsoft, Access, 2003
Sign Up Now!
Solution Provided By: capricorn1
Participating Experts: 1
Solution Grade: A
 
 
[+][-]07.09.2008 at 06:17AM PDT, ID: 21963205

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.09.2008 at 06:20AM PDT, ID: 21963254

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.09.2008 at 07:06AM PDT, ID: 21963699

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628