paste from selected record to selected record

[DeanRowe]

Hi,

I have two tables, "NewPurchaseTable" and "TheirPurchaseTable", I am viewing them side by side in subforms on a main form called "InvoiceForm". Each subform is called "OurPurchaseTableSubForm" and "TheirPurchaseTableSubform" respectively, and they are both being viewed in datasheet view.

My objective is too select/highlight a record in each table, then click a command button to paste the "PurchaseID" from the selected record in "TheirPurchaseTableSubform" into the "Invoice ID" field of the selected record in the "OurPurchaseTableSubForm".

I'm not sure where to start and if this is even possible to do in code, or if I should use a macro with an append query.

Any advice would be greatly appreciated, thank you.

Dean

 

[ajetrumpet]

Dean,

First of all I would like to say, thank you for asking a very well-worded and descriptive question! It certainly is a relief. And, for the question...

My objective is too select/highlight a record in each table, then click a command button to paste the "PurchaseID" from the selected record in "TheirPurchaseTableSubform" into the "Invoice ID" field of the selected record in the "OurPurchaseTableSubForm".

Here is my view:

Put your button on the main part of the form. If you do this, you can use the two recordsets to copy the value from one to the other. I would do it like this...

Dim rsSource as recordset
   dim rsTarget as recordset
      
set rsSource = me.TheirSub.form.recordsetclone
   set rsTarget = me.Oursub.form.recordsetclone

rsTarget!InvoiceID = rsSource!PurchaseID

If the records in both of the subforms are selected before you run this, it should work fine.

 

[CEH]

OK...... that IS a way to do it..... But my first question is why? Maybe I am not reading your post correctly, but it doesn't sound like the DB is normalized..... You will have duplicate info in two tables.......why not something like a checkbox Yes/No... "New Purchase included in Their Purchase?"
Many ways to do this.... but having the same fields in different tables is usually not correct.

 

[boblarson]

OK...... that IS a way to do it..... But my first question is why? Maybe I am not reading your post correctly, but it doesn't sound like the DB is normalized..... You will have duplicate info in two tables.......why not something like a checkbox Yes/No... "New Purchase included in Their Purchase?"
Many ways to do this.... but having the same fields in different tables is usually not correct.

I'm proud of you Curtis - you have definitely hit upon the same questions I would have asked.

 

[DeanRowe]

Hi Guys, Thank you for your posts, the reason why I need do it like this is because my company has sneeky suppliers and they have tendancies to overcharge and to put through the same order twice.

What used to happen before was we would place an order on ID 3456, and our supplier would invoice us for it on the 29th Jan and then bill us again 3 days later on 2nd Feb, so when I got through January's statement it would have an invoice for 3456 so I'd tick a yes/no box to say the charge was correct, then a month later when Febuary's bill came in I'd tick this invoice as well.

Even when I had a check box on our table and a check box on their table it was tricky because when the second invoice arrived I could see that it had already been paid but it didn't say when or where.

By doing it in this new way, when February's bill comes in and theres a charge for 3456, I can see straight away that we were already charged for this in January and because I've copied the purchase ID I can bring up all the details for the Jan 29th invoice.

ajetrumpet, thank you for the code and the nice comment. I'm having trouble implementing the code properly. I've made a command button on the main "InvoiceForm" called "Match". However the following code:

Private Sub Match_Click()
Dim rsSource As Recordset
   Dim rsTarget As Recordset
      
Set rsSource = Me.TheirPurchaseTableSubform.Form.RecordsetClone
   Set rsTarget = Me.OurPurchaseTableSubForm.Form.RecordsetClone

rsTarget!Invoice_ID = rsSource!PurchaseID
End Sub

Brings up an error:

run-time error '13':
Type Mismatch


I tried the following code and various variations:

Private Sub Match_Click()
Dim rsSource As Recordset
   Dim rsTarget As Recordset
      
Set rsSource = Me.InvoiceForm.Form.TheirPurchaseTableSubform.Form.RecordsetClone
   Set rsTarget = Me.InvoiceForm.Form.OurPurchaseTableSubForm.Form.RecordsetClone

rsTarget!Invoice_ID = rsSource!PurchaseID
End Sub

However they all give various errors, any ideas guys? Thank you for your time.

 

[Dreamweaver]

You should if using 2000+ dont use Recordset but DAO.Recordset

Also If you have a stock contol and payments system it should give you the information you need esp if normalized

If you use PO Orders you have an order you created which will require your supplier to supply you x number of units at a given price that was agreed at the point of order or with one of there reps.

now you have said info when you take delivery of x# of items it will do one of to things if the amount incoming is equal to the amount on said PO Order that order is filled but if the number of units is less than was ordered then x# will be on back order.

so now you and the supplier both have your PO Order# which should be used when receiving any invoice from said supplier.

now a payments system will show if designed to what amounts have been paid toward what PO Order and weather said PO Order has items on backorder so to put it simply you can't pay to much as the system will tell you you have fully paid a PO Order.

I may be incorrect there and would hope some kind sole would put me right if I am incorrect.

Marry Xmas

Mick

 

[DeanRowe]

Hi Dreamweaver,

First of all, regarding the code I didn't really understand what you meant by "dao.recordset" - I tried using:

Private Sub Match_Click()
Dim rsSource As DAO.Recordset
   Dim rsTarget As DAO.Recordset
      
Set rsSource = Me.InvoiceForm.TheirPurchaseTableSubform.Form.RecordsetClone
   Set rsTarget = Me.InvoiceForm.OurPurchaseTableSubForm.Form.RecordsetClone

rsTarget!Invoice_ID = rsSource!PurchaseID
End Sub

but to no avail.

We do not have a stock control and payments system - how we operate is a customer places an order with us, then in turn we place an order with our supplier, with an Order ID, and they send the goods directly to our customer (effectively we are middlemen, but honestly priced middlemen).

Our supplier has our card information and they charge us for the goods they send out, then at the end of the month I type out their monthly statement of goods we've purchased and match up what we should have paid according to our records, to what they have actually taken. I then bring up any discrepancies and get creditted back for any errors. Common errors include us placing an order, and they charge for it, find it out of stock, then when it comes instock in a few days time they accidently charge for it again (it's a poor system that they use but it's hard to change other peoples practices, and in every other aspect of the supplier/customer relationship they are perfect).

What I am trying to achieve with this code is to make it easier to correlate our records to theirs, and spot any overcharges.

Any ideas on the code? Thanks again for your help guys

 

[Dreamweaver]

A PO Order for say where housing will have a supplier you are using, the delivery address they have to deliver too, and an invoicing address where they send the invoice you have with that the 3 bits of a puzzle.

you seem to be making it hard for yourself All I can suggest is let goggle do the searching and see what example info is out there for the sort of system you are trying to design.

It's an age old problem with an age old solution well documented and probably on this site when using the search feature

 

[DeanRowe]

Hi Dreamweaver,

I get what you are saying, but I already have a system that tells the supplier our order number, where to send the invoice to, and where to send the goods to. I've used the system for a long time and it does work, and it works a lot better than many order systems that are availble for purchase online.

However I need to plan for the instances when things do go wrong at the supplier end, and by hopefully fixing the code problem above, I'll be able to spot their mistakes quickly and easily.

Although it looks like the above situation is producing duplicate data, it is only one field and its a relational database - by adding the purchaseID into the invoice field, I'm linking the two tables together.

Any ideas on fixing the above code would be appreciated very much.

Thank you

Dean

 

[Dreamweaver]

I do think it's a floor in your system esp if as you say you have all the bits in place don't take it wrong but just look at what you have and different ways you can do what you want .

I can't correct something I don't feel is correct.

I'm no expert so just read a few other comments you have had both are better at it than me.

 

[ajetrumpet]

ajetrumpet, thank you for the code and the nice comment. I'm having trouble implementing the code properly. I've made a command button on the main "InvoiceForm" called "Match". However the following code:

Private Sub Match_Click()
Dim rsSource As Recordset
   Dim rsTarget As Recordset
      
Set rsSource = Me.TheirPurchaseTableSubform.Form.RecordsetClone
   Set rsTarget = Me.OurPurchaseTableSubForm.Form.RecordsetClone

rsTarget!Invoice_ID = rsSource!PurchaseID
End Sub

Brings up an error:

run-time error '13':
Type Mismatch

What's the data type of the Invoice ID field? The Purchase ID field? Is either an autonumber field? The error means just that, a type mismatch. Check this stuff first. I think you can paste an autonumber value into another field, but not if it too, is an autonumber. An autonumber field cannot serve as an incomming port for data. It is already in use by the program.

 

[DeanRowe]

the datatype of the [Invoice ID] field is Number>Long Integer>Indexed with No Duplicates allowed.

the datatype of the [PurchaseID] field is Autonumber>Long Integer>Indexed with No Duplicates, it's also the primary key for this table.

So in theory it should be fine but it's not, I'll backup and save and then try fiddling around with the table properties to see if that helps.

 

[DeanRowe]

I think I've got it now:

Private Sub Match_Click()
Forms!InvoiceForm!OurPurchaseTableSubForm.Form.[Invoice ID] = Forms!InvoiceForm!TheirPurchaseTableSubform.Form.[PurchaseID]
End Sub

Thanks for your help ajetrumpet, and for your advice dreamweaver.