Automatic Form Data update with a one-to-one using cmd button

[gtlgirl11]

Hi All,

I'm in the process of building a database where users can access a 'Request' form, but it can only be 'Rectified' by an admin. My first question is how to set the relationship so that it it one-to-one. A Request can have a maximum of one rectification, or none (leaving it will a null rectification status).

I have the database set up so that when a Request is entered and saved an email is sent to an admin prompting them to recitify the request.
My second question is after an admin logs on, searches the request, and then chooses to recify it (I have this linking through a command button) I would like it so that the PK (RequestID) automatically links the two forms, so that Request ID is auto-populated on the form.

I have tried to link the forms, but this doesn't populate the RequestID on the Rectification form. I'm at this point --> :banghead:

I'm using Access 2000, if that's of any help (or hinder).

Thanks!!!

 

[rodmc]

you need to set the FK as indexed (no duplicates) as far as I can remember For 1 to 1 rerlationships

 

[rodmc]

should add that if you want to add a form you need to link the parent and child fields, Access will do this automatically if you use the form wizrd

 

[gtlgirl11]

okay, so I have the one-to-one relationship set up, thank-you rodmc. BUT my wizard doesn't seem to want to create a subform from my request table to the rectification table. Both tables have fields that are unique to them, so when im adding both tables to the wizard the option to create one as a sub doesn't even appear!

 

[rodmc]

create the rectification form as a normal form first then drag it to your main form and specify the parent child fields via the properties box!

 

[rodmc]

If you still cant get it to work, post your DB and we'll have a look

 

[Galaxiom]

One to one relationships indicate the data should be in the same table. Use appropriate queries as the RecordSources to get the fields you need in each form.

Then you won't need to worry about joining the tables.

 

[rodmc]

I've done 1-1's in the past just to break down massive tables into something more manageable, thankfully though nothing has ever gone wrong with them (yes)

 

[gtlgirl11]

Allright. Have at er.

 

[gtlgirl11]

While we're at it the other major issue I'm having, is that I would like to create a report (and make that the one that sends) where all open requests (including the newly created request) are sent in report for for the email. Now. This problem would be easier solved I think if I had all of the data as a part of one table, but with wizards it should have been no problem.
I basically want to report on all requests that have rectifications with either status "Not Started", "On-Going" or Is Null. I haven't had any luck with this either.

 

[rodmc]

Is ReqestRec your main form then? and you want to embed Request_Form into the RequestRec form?

 

[gtlgirl11]

No, I forgot to delete the REQUESTREC when I was playing around with it. As well, the Searh_Form is useless. I decided to go with a pull down that updates the fields by Title (not the most ideal, solution, but it is what it is). I want to embed the Rectification_Form in the Request_Form1.

 

[rodmc]

While we're at it the other major issue I'm having, is that I would like to create a report (and make that the one that sends) where all open requests (including the newly created request) are sent in report for for the email. Now. This problem would be easier solved I think if I had all of the data as a part of one table, but with wizards it should have been no problem.
I basically want to report on all requests that have rectifications with either status "Not Started", "On-Going" or Is Null. I haven't had any luck with this either.

If you use a query as your record source as Galaxiom has said then you would make like a lot easier for yourself.

Just noticed that your using lookup tables, not a good idea. Instead of having a lookup table for RecStatus, just use a query or define the values in a list via the properties, that would mean you could ditch that RectStatus table.

 

[gtlgirl11]

that would mean I could ditch all of the lookup tables actually, wouldn't it! Same would go for the LOCATION and REQTYPE tables right even though I'm using option buttons?? Maybe leave the employee ones.

 

[gtlgirl11]

any luck with the embedding?

 

[Galaxiom]

I've done 1-1's in the past just to break down massive tables into something more manageable,

By no means always but in the vast majority of circumstances where tables have very high field counts the real issue is structural and the data in many of the fields should really be converted to records in related tables.

 

[rodmc]

any luck with the embedding?

yes, it links up no problem for me, I had to expand your form, I'll leave setting up the position of the subform and the dimensions to your good self, seems to work ok