Coping records?

[OAKLEYS21]

Hello,

I have a table called transactions in my accounting d-base. There is column heading called date, description, account, amount, and control. I post tansactions in there feilds by filling the required feilds. Sometimes I have the same transactions every month (recurring) and was wandering if there is a way I can copy the transaction and paste them so I don't have to keep typing the data? Thanks.

 

[kjv1611]

You could create a table for each field that would contain commonly recurring data, then link a combo box to that table.
If I read you correctly, it would be something like this:
1):
create a table called tblDescription, and in that table, have fields titled "description", "amount", and "control". Here, you could enter common "descriptions" along with the associating amount and control values - if those are the same for each type of transaction.

2):
create a combo box for "description" or if you already have a textbox, right-click it, choose "change to" and select combo box from the fly-out menu.

3):
In the combo box, under the "Data" tab set your [blue]Record Source Type[/blue to Table/Query (if not already there), and select the table, tblDescription from the [blue]Row Source[/blue].

4):
While still in the properties for cmbDescription, choose the [blue]Event[/blue] tab, and find the After Update event. click the build button to the right of that event, and choose "code builder".

5):
In your code builder, you should now see the following:

Private Sub cmbDescription_AfterUpdate()

End Sub

6):
You need to type the following code within this sub, so the final code would look something like this:

Private Sub cmbDescription_AfterUpdate()
  If DLookup("[Description]","tblDescription","[Description] =" _
  & Forms![MyFormName]!cmbDescription) Then
      txtAmount = DLookup("[Amount]", "tblDescription", & _
        "[Description] =" Forms![MyFormName]!cmbDescription)
      txtControl = DLookup("[Control]", "tblDescription", & _
        "[Description] =" Forms![MyFormName]!cmbDescription)

  End If
End Sub

Try it out, and post back if any problems...


Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[OAKLEYS21]

Still need some help.

The table that contains my transactions is called "transactions". I created the "tbldescriptions" table per your instructions. Do I put the combo box in the description feild in "transactions" or "tbldescription". Aslo, step 4 and on and a little confusing I cannot find the event tab or the code builder? Help please...thanks

 

[kjv1611]

Well, my method was assuming you were using a form. So, in order for it to work correctly, you would have to create a form which is linked to your table, "transactions", and put the combo and text boxes there.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[OAKLEYS21]

I don't know code very well, Im getting a compile error message with that code you gave me?? Thanks It is getting close to working.

 

[kjv1611]

Do you have a form for the code?

If so, what is the error message, and what line does it highlight in the vb editor when you press "debug" or "ok" if no debug in the error message box.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[OAKLEYS21]

Hello,

It highlights the "&" and the whole first line of the code? Thanks

 

[kjv1611]

I'm assuming you nean this part:
If DLookup("[Description]","tblDescription","[Description] =" _
& Forms![MyFormName]!cmbDescription) Then

If so, try putting it this way:

  If DLookup("[Description]","tblDescription","[Description] =" Forms![MyFormName]!cmbDescription) Then

And make sure it's all on one line, not 2.


Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[OAKLEYS21]

Still doesn't work any other ideas? Thanks.

 

[kjv1611]

To make sure on same page, let me ask these question:

1.) Did you create a form? If so, make sure you use the name you gave your form in place of MyFormName at any point in the code. To make sure you change all of them, you can hit ctrl + h in order to bring up the find and replace tool in the VB window. When that is up, tell it to replace MyFormName with whatever you named your form.

2.) Copy and Paste the exact line of code where the error is highlighting here, to be sure looking at the same problem.

3.) Type out the exact error message as it appears in the error messge window here, so can be sure exactly what the error is.

Then, we can go from there.. [WINK]

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[OAKLEYS21]

Private Sub TransactionDescription_AfterUpdate()
If DLookup("[Description]", "tblDescription", "[Description] =" _& Forms![transactions]!cmbDescription) Then

the error message I get is compile error, syntax error. Don't know whats wrong.

 

[kjv1611]

Did you name your form "transactions", the same name as your table "transactions"? Because, I remember you saying your main table was "transactions." So, from what I am seeing, you are trying to pull a table as a form, and Access won't do that. If you did create a form, then make sure you saved it with a different name.. if nothing else, name it frmTransactions instead of just "transactions," and put that in place of "transactions" in your code there.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV