Using forms to update/create record

[juliewhite]

I have tried so many different solutions to my problem that I think I have just made a bigger mess. I have a database with forms Referral, Case data, plaintiff and defendant. What I want to do is in my form referral, enter the case number and have the case form open to either a new record if the case number is new or open to that particular record. The Case data table only contains the plaintiff and defendent's social security number. After the plaintiff's social security number is verified, I would like the plaintiff form to open to either a blank record with only the Social Security number and the other fields to be entered by the user. Then, once that record has either been updated (people move alot) or created, I would like to go back to the case data form and do the same for defendant. double checking data entry is key as this is a legal department.

I don't know if I am asking too much. I am very new and am very unfamiliar with VB(if I even need it for this). Maybe I'm trying to make it harder than it is....I don't know. Any help or suggestions would be greatly appreciated.
Thank you

 

[juliewhite]

Please help!?! Is what I want to do even possible? or is there an easier or better way to accomplish this? thanks

 

[EdV]

hi, I've been hoping to find the solution too. It may not be the right fix but what I did to get around it for now is with my multi tabbed form was to add a command button on the main form with the open form function . I already created a all records display form with all the fields in it (justified). Then I referenced the command button to it , once it poped up I was able to edit any field, that is if the record exists.

I don't' know enough of the access vb to code it, but was able to see the display form and make updates to the record.

On my tabbed form it looks like I'll have to live with adding records(many side) with the linked key of the primary records.

I'm sure there are folks here that can share a better solution.

Cheers
Ed

 

[Brianwarnock]

You have asked for help on this database on another thread, but when Ukraine82 suggested posting a copy of your database you did not respond, but have now started another thread! You have to help people to help you.

Brian

 

[juliewhite]

Yes, thank you, I realize this. I was working on other things in the mean time and it took me a while to get back on the site. I found another thread that seems to have me in the right direction I think

http://www.access-programmers.co.uk/forums/showthread.php?t=64963

Pat's sample is proving to be invaluable to me. I am using her autofill best example. The place I am having trouble now is I don't think I have the case number and plaintiff ID related properly, b/c I was getting a #Name? instead of the number. any thoughts?

 

[juliewhite]

okay, I'm stuck. Why won't the plaintiff and defendant ss# autopopulate correctly. I thought I understood Pat's example, but I guess I didn't. any help?

 

[Brianwarnock]

Hi

to autopopulate the 2 fields quoted you need to make the control source of cboCase Number Case Number this is the only change, can I at this point say that you should avoid blanks in ACCESS names it makes coding far simpler.

brian

 

[juliewhite]

I was able to figure out autopopulating fields using Pat's many to many example and it works beautifully for existing records, however when I did that, I can't seem to add any new records. I need to add records to my case number combo box and have it update in my subform, so that I can then add my plaintiff and defendent information. any thoughts?

 

[PierreR]

Julie,

Why do you have separate tables for referrals and cases? Isn't there only one referral for every case and one case for every referral? You could separate the two in queries, forms and reports, but I don't think it is necessary with tables. OR CAN THERE BE MORE THAN ONE CASE PER REFERRAL OR MORE THAN ONE REFERRAL PER CASE?

I'm trying to work out a database for you.

Pierre.

 

[juliewhite]

My thinking was that because each case can be referred to the legal department numerous times and we need to track referrals and cases. I need to know if the case has already been referred and if it still has items outstanding, or if (as of the creation of the database) there is no pending action on the case (such as an active bench warrant on the obligor). This database will need to be expanded actually in order to track service status and results of hearings, but I just wanted to get the basic up and running.

Thank you so much for all of your help. I am basically teaching myself Access and am grateful for this site. I am pursuing my masters in information systems management and can't wait to dive deeper into the mysteries of database design and administration. It really seems to be the way to go.

 

[PierreR]

Don't have much choice, Julie. Have to get ahead.

I've attached the sample I've been working on.

Pierre.

 

[juliewhite]

Thank you very much. So far it looks great! I have a few other things to do around here before I can look at it deeper. I'll let you know if I have any questions.

Thank you so much!!