Populate New Record 1

[hdgirl]

I would like to be able to select a members ID No that would open a new record but have all the members details automatically filled in the form and then be able to input a new transaction as a new record against the member No

CJB

 

[KenReay]

Hi

Presumably you mean open an existing member record and add a new transaction for that member?

Depending if you want to see existing transactions for the member and/or potentially input 'n' transactions for the member it may be useful to use a master form / sub form set up.

I think you need to explain a little more (including tables in use) before meaningful advice can be given

It is possible for example (using the column property), to select Member id from a combo, and have member name, address etc displayed, without having the member table bound to the form.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[hdgirl]

Hi Ken

Thanks for your response (you've helped me in the past)i have a customer table & THe PK is RefNo which is an autonumber, it also has a field called Member No which is number. I have another table called transactions with no primary key, a field called transactions which is an autonumber. i want to be able to open an existing member number with all details showing and add new transactions

THx

CJB

 

[hdgirl]

Also i forgot to mention on my form (that is based on the customer table) i have a subform (based on the transactions table) i do not really mind if the new record opens up showing all existing transactions for the member just so long as we can add a new transaction

CJB

 

[KenReay]

Hi

On the Main form (the one based on Customer), put a combo box (cboLookUp) based on the customer table, have two columns, the RefNo and the member No, set the width of the first column(RefNo) to zero, so user does not see it. Set limit to list to yes.

In the after update event of the combo box put code like so


Me.RecordsetClone.FindFirst "RefNo = " & cboLookUp
Me.Bookmark = Me.RecordsetClone.Bookmark

Provided you have the Child/Parent link fields set on the subform control, you should automatically get the trasnactions fro the relevant customer (member) displayed in the subform. You will see the existing transactions and be able to add new ones

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[hdgirl]

Hi Ken

Thanks this worked brilliant the only problem is the new transactions are only seen in the transactions table and not in the customer when i open the subform but then that'll be because it's linked via the refernce number won't it, Looks like i'll have to sort this out first then hopefully come back for more help, but thanks on the first part

HD

CJB

 

[hdgirl]

I have now changed this database and the above code doesnt work so perhaps i can call on you to help again

i have 1 table (customer) the fields are:
Transaction A/N, Membership number Text, and then name address field etc.

I have a form for new members and a form on existing members, i use the above code to open the existing member number this it does but it opens to the existing tranaction and not to a new one. The reason why i aborted the two tables and the sub form was that i was unable to form the correct relationships for all the requirements within the very short time scale that i have been given for this project, so can you help?

CJB

 

[KenReay]

Hi

The basic idea should still work, if the 'key' is not a number you need to bound teh value in quotes so:

Me.RecordsetClone.FindFirst "RefNo = " & cboLookUp
Me.Bookmark = Me.RecordsetClone.Bookmark


Me.RecordsetClone.FindFirst "strRefNo = '" & cboLookUp &"'"
Me.Bookmark = Me.RecordsetClone.Bookmark


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[hdgirl]

This is what happens, i select a member number from the combo box and the form fills with all that members details and current transaction number but as soon as i press return to go to the next field the form empties except the next transaction number is filled in...what am i doing wrong!

CJB

 

[hdgirl]

Hi ken or anyone that can help

I have now gone back to my original plan, all relationships are working OK now so on my form i have the combobox that allows you to select a member number - lets say i select member number 111, this then fills in all the members details but as soon as i tab out of the combo box to go to the next field it changes to all the details for member number 112 and the transaction is then shown for member number 112, can you help again please

CJB

 

[KenReay]

What code do you have in the after update eevnt of the combo box?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK