Copy data from one table to another through a form

[SheilaAlighieri]

Hi all,

This may sound like a silly question. But I am quite new to Access Here is my problem. I am developing an Access database for personal use at the office. I would like to do the following:

I have two tables: Budget and Invoice. I also have a form named 'Invoice Data Entry' which fills my table 'Invoice'. Since some invoices return each year I would like to add an option 'Copy invoice to budget of next year' to my 'Invoice Data Entry' form. I was personally thinking of using a check box but I have no clue of what code I should attach.

Naturally I don't want to copy all fields on the form. Just a few, which have the same name in the Budget and Invoice tables. For example: Account Code and Plan Region.

Furthermore, there is also a difference between the budget date (in the 'Budget' table) and the invoice date (in the 'Invoice' table). When I copy an invoice to the budget table, I would simply like to add one year to the invoice date to get the budget date.

I know I am terrible at explaining things If I need to clarify anything don't hesitate to contact me.

Can anyone help me with the code?

Thanks,

Sheila!

 

[LittleSmudge]

Sounds a reasonable explanation to me.

The apprach I'd take is to add a command button to the form and place code in the On_Click event

The code will need to open a writable recordset on the Budget table
Then simply write each of the values from the controls on the form to the appropriate fields in the recordset
( adding one year to the InvoiceDate value )


Opening recordsets is 'relativly' painless once you've done it a couple of times. The 'wording' of the code differs greatly depending on the version of Access you are using.
So if you want guidance on recordset code - let us know which version of Access ( Up to A97 OR A2k & beyond ) that you are using.


'ope-that-'elps.

G LS

 

[SheilaAlighieri]

Hi Smudge!

Thanks for your reply. I think I could use some help with the code I am using Access 2K.

Sheila

 

[LittleSmudge]

Okay Sheila

Naming I've used ( & you'll need you change to your names ):
tblBudget is table to contain Budget data

Supplier is a field in tblBudget
txtSupplier is control on the form bound to Supplier field in Invoice table
Same with Value..
DueDate is a field in tblBudget
txtInvDate is control on the form bound to InvoiceDate field in Invoice table.

Private Sub cmdCopyTobudget_Click()

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenKeyset
rst.LockType = adLockPessimistic

rst.Open "SELECT * FROM tblBudget ;"
rst.AddNew
rst!Supplier = txtSupplier
rst!Value = txtValue
rst!DueDate = DateAdd("yyyy", 1, txtInvDate)
' etc.. Add other fields as required.

rst.Update ' Not technically needed - but it helps to remind you what's going on

rst.Close
Set rst = Nothing

End Sub

'ope-that-'elps.


G LS

 

[LittleSmudge]

One other thing.

Do you have a plan in mind to prevent the user from adding the invoice into next year's budget multiple times ?

Would this be allowable ?




G LS

 

[SheilaAlighieri]

Hi G LS,

It works perfectly! Thank you so much!

It would indeed be best if users are prevented adding the invoice into next year's budget multiple times. I don't want to be a nuisance for you, since you already did more for me than I could ask, but it would be wonderful if I could insert a prevention. Would it be possile to show a message, like "Invoice already added to the budget of next year"?

 

[LittleSmudge]

ALL things are possible .. ..

To save me "inventing" more field names please tell me what the field names are in tblBudget that would need to match the contents of controls on the form in order for you to KNOW that these match.

Eg.
Would it be just
Supplier & Value
or are there other fields ?



G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[SheilaAlighieri]

You are too kind, smudge

The fields are as follows:
Supplier
Value
Country
Channel
Description

That's it

Furthermore, I've been a dummy. In the office we use Access 97. Does that infect our code much? I am so sorry

Regards,

Sheila

 

[LittleSmudge]

BIG TIME it affects the code.

Rip it all up and start again !

For now lets fix the prevention problem :-

REPLACE
rst.Open "SELECT * FROM tblBudget ;"


WITH

rst.Open "SELECT * " _
       & "FROM tblBudget " _
       & "WHERE (Supplier = '" & txtSupplier & "') " _
       & "AND (Value = " & txtValue & ") " _
       & "AND (Country = '" & txtCountry & "') _
       & "AND (Channel = '" & txtChannel & "') _
       & "AND (Description = '" & txtDescription & "');"
If rst.EOF Then
  ' All the rest of the original code in here
    rst.AddNew
    etc .. ..
    etc ..
    rst.Update
' Then add
Else
    MsgBox "I already have this invoice in next year's budget.",,"Go Away"
End If

rst.Close
Set rst = Nothing
End Sub

NOTE the single quotes around what I think are TEXT fields and the absence of singles quotes for the number field


Test that on your A2k system.


A97 solution to follow.




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[LittleSmudge]

DAO solution for Access 97


Start with

Dim db As Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * " _
& "FROM tblBudget " _
& "WHERE (Supplier = '" & txtSupplier & "') " _
& "AND (Value = " & txtValue & &quot " _
& "AND (Country = '" & txtCountry & "') _
& "AND (Channel = '" & txtChannel & "') _
& "AND (Description = '" & txtDescription & "');&quot

Then carry on as before

rst.Update is VITAL in DAO whereas it is 'optional' in ADO



'ope-that-'elps.







G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[SheilaAlighieri]

Hi Smudge

Now you'll see that I am complete newbie.
I compiled all the codes as follows. But It doens't work. I placed the errors I get in parentheses. I hope we can get this to work Thanks again!

Dim db As Database (error: User-defined type not defined)
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * " _
& "FROM tblBudget " _
& "WHERE (Supplier = '" & txtSupplier & "') " _
& "AND (Value = " & txtValue & &quot " _
& "AND (Country = '" & txtCountry & "')" _
& "AND (Channel = '" & txtChannel & "')" _
& "AND (Description = '" & txtDescription & "');&quot

Reset (I changed replace to reset cause it gave me problems too. Reset seems to work though)
rst.Open "SELECT * FROM tblBudget ;"

WITH (error: syntax error)
rst.Open "SELECT * " _
& "FROM tblBudget " _
& "WHERE (Supplier = '" & txtSupplier & "') " _
& "AND (Value = " & txtValue & &quot " _
& "AND (Country = '" & txtCountry & "')" _
& "AND (Channel = '" & txtChannel & "')" _
& "AND (Description = '" & txtDescription & "');"

If rst.EOF Then
rst.AddNew
rst!Supplier = txtSupplier rst!BudgetDate = DateAdd("yyyy", 1, txtInvoiceDate)
rst!Value = txtValue
rst!Country = txtCountry
rst!Channel = txtChannel
rst!Description = txtDescription

rst.Update

rst.Close
Set rst = Nothing
MsgBox "This invoice is added to next year's budget."
Else
MsgBox "I already have this invoice in next year's budget.", , "Go Away" (haha )
End If

rst.Close
Set rst = Nothing
End Sub

 

[LittleSmudge]

No NO NO

REPLACE mean take the following line and delete it

and in its place put the stuff that comes after WITH

As in REPLACE this WITH that.


Dim db As Database WITH NOT WORK IN Access 2000 without fiddling with the Library References. It is only for use on your A97 system.

Are you working on the A97 system when you get the compile proble with Dim db As Database





G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[SheilaAlighieri]

Ok So the code should work like this?
I will test in the office tomorrow

Dim db As Database
Dim rst As DAO.Recordset
Set db = CurrentDb

REPLACE
rst.Open "SELECT * FROM tblBudget ;"

WITH
rst.Open "SELECT * " _
& "FROM tblBudget " _
& "WHERE (Supplier = '" & txtSupplier & "') " _
& "AND (Value = " & txtValue & &quot " _
& "AND (Country = '" & txtCountry & "')" _
& "AND (Channel = '" & txtChannel & "')" _
& "AND (Description = '" & txtDescription & "');"

If rst.EOF Then
rst.AddNew
rst.Open "SELECT * " _
& "FROM tblBudget " _
& "WHERE (Supplier = '" & txtSupplier & "') " _
& "AND (Value = " & txtValue & &quot " _
& "AND (Country = '" & txtCountry & "')" _
& "AND (Channel = '" & txtChannel & "')" _
& "AND (Description = '" & txtDescription & "');"

rst.Update
rst.Close
Set rst = Nothing

MsgBox "This invoice is added to next year's budget."

Else

MsgBox "I already have this invoice in next year's budget.", , "Go Away"

End If

rst.Close
Set rst = Nothing

End Sub

 

[WildHare]

I don't mean to interrupt, but you could get around all that code by simply writing an append query with the appropriate field matchings and call it from a command button...

I've done this exact thing several times in the past (copy some form record stuff to another table) and used append queries each time. Much simpler.

Jim
Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[SheilaAlighieri]

Now that we started this code I would like to finish it But thanks any way Hare

 

[LittleSmudge]

Hello Jim,

I've not seen you around recently - been busy elsewhere ?




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[SheilaAlighieri]

Hi Smudge! Sorry for not replying for such a long time. But I got the code to work

I only have some difficulties comparing the fields "BudgetDate" and "InvoiceDte", cause the Budgetdate is the Invoicedate + one year. I tried to solve it as follows, but now it copies all invoices (also the invoices which are already in the budget).

Dim myDte as Date
myDte = DateAdd("yyyy",1,Forms!FormName!InvoiceDte)

rst.Open "SELECT * " _
& "FROM Budget " _
& "WHERE (AccountCode = " & AccountName & &quot "
& "AND (Channel = '" & Channel & "')" _
& "AND (BudgetDate = #" & myDte & "#)" _
& "AND (Description = '" & Description & "');"


This is the code

Private Sub Button__Add__Click()
On Error GoTo Err_Button__Add__Click

Dim db As Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * " _
& "FROM Budget " _
& "WHERE (AccountCode = " & AccountName & &quot " _
& "AND (Channel = '" & Channel & "')" _
& "AND (Description = '" & Description & "');&quot

If (MsgBox("The system will now copy this invoice to the budget of next year.", vbInformation + vbOKCancel, "Copy?&quot = vbOK) Then

If rst.EOF Then
rst.AddNew
rst!AccountCode = AccountName
rst!BudgetDate = DateAdd("yyyy", 1, InvoiceDte)
rst!Budget = Amount / Rate
rst!Planned = Amount / Rate
rst!Channel = Channel
rst!Description = Description

rst.Update

DoCmd.GoToRecord , , acNext
DoCmd.Beep
Call MsgBox("Invoice copied.", vbInformation, "Copied&quot

Else

DoCmd.Beep
MsgBox "This invoice is already in next year's budget.", vbCritical, "Error"

End If

rst.Close
Set rst = Nothing

End If

Exit_Err_Button__Add__Click:
Exit Sub

Err_Button__Add__Click:
DoCmd.Beep
MsgBox "You haven't entered all required data", vbCritical, "error"
Resume Exit_Err_Button__Add__Click

End Sub