Access form auto populate query

[moneypennie21]

Hi, I have a form - lets call it Form A based upon a query

I have placed a button on my data entry form (Form B) to open Form A in edit mode based upon criteria in Form B (criteria is set in the query) which works fine when a record that I am looking at on Form B exists in the query. However if a record does not exist in the query a blank Form A opens and I can enter the data and save it and it creates a new record. all works fine. (I understand why this happens just want to see if I can do something with it.

However what I would really like is form A to open in edit mode but with some of the fields pre populated based on what record is open in Form B and then allow me to edit the other fields and save a new record in the table which forms part of the query - I have tried setting the properties so the fields I want are linked to Form B but then when I create a new record in the table the query is based on it does not save these entries on the record.

I have created a form based on the table rather than the query (i need the query to extract data so have left it as it is) this is Form C - I have linked this to Form B and it opens fine and displays data if there is a record in the complaints table that has the same system number as the record open on Form A however if there is not it is blank, can I pre populate fields such as branch code and system number based on the open record then enter the remaining fields and create this a a new record in the complaints table?



I have attached a dummy copy of the database to demonstrate what I have done/want to do.

Is there any way to do what I am trying to do?

Thanks

J

[Micron]

yes you can. Haven't downloaded your db as this might be simple enough for you to implement. When the edit form (we're really talking about an append here) opens from a call in your first form (e.g. DoCmd.OpenForm frmMyFormName) you continue code there that populates the edit form. Like

With Forms!frmMyEditForm
.controlA = Me.control1 (where control# is the name of the control of the form whose values you want to grab)
.controlB = Me.control2 (and controlA,B are the controls you want to contain the info from the calling form)
...
End With

You might want to first detect that the target form is open since it's likely that this could be initiated after someone has closed it and gone back to the calling form to click the button again. Let's see what you think of the suggestion first.

[June7]

Advise not to use spaces in naming convention. Advise not to include queries in Relationships builder.

The link between Data and Complaint is not on PK/FK fields. Table links should be on PK/FK fields.

What is the relationships of these data entities? Master appears to have info about Branch codes. Complaint is info about complaints and Data is what - more details about each complaint? If so, this table should have Complaint ID as foreign key and not link to Master. Why are you linking on SystemNumber fields, neither of which is a PK? Do you need a table for Systems?

Data structure is a mess and should not proceed until fixed.

[moneypennie21]

Thanks Micron - I will make a note of this code as I can see how it would work.

However June7 is right and the relationships are a bit of a mess and I realised I was making it more complicated than I needed so I added columns to the data table for the complaint details, deleted the complaints table and then cleaned up the relationships and then built the queries to extract only the information I needed.

I then created fields and button on the data entry form to say that if the call outcome showed EDO or complaint (using the exact phrases) so that the new fields only show when one of them is selected and the enter details button is clicked (set form properties on load to set them all as invisble

Thanks for taking the time to have a look and get back to me.

J