Open Form and add new record, but autofill some info based on current combobox selection

[tslupphaug]

Hello friendly internet!





I'm working with the following 3 forms (see screenshot above):
1. fmnuNavH
2. frmDocListByProject
3. frmDocListByProject_SUB

For simplicity within this thread, I'll simply call them "Nav", "Sort" and "Show".
Well, the Nav form can be ignored completely I guess.

My "Show" subform shows all documents based upon what project you choose from the Combobox in the "Sort" form. I want to make it easy for users to add more documents to that project, so I added a "Add New Document" button to my "Sort" form. OnClick I want to open my frmDocDetails (see below) and go to new entry.



Opening up a new (entirely blank) new entry in the frmDocDetails is easy enough. But what I'm having problems with is having my fields "Project No", "Project Name" and "Client" (3 fields at the top there) being autofilled based upon my current view selected in my "Sort" form (and currently shown in my "Show" form for that matter).
Don't know if it matters, but for info: "Project No" and "Project Name" comes from "tblProjects", while "Client" comes from "tblClients". They're obviously linked together in Relationships though.

I'm pretty new to Access and macros etc.. So please go easy on me
Best regards.

[ItsMe]

I am having a hard time following. You have a new form open so that you can add a new record? Then you would want to open your form to open and show only a new record.

It is confusing when you state that you want a data entry form and also display relative records.

Perhaps you should work on creating a Main form with a subform and get your Master/Child Link fields set up correctly. Maybe use the wizard. Afterwards, you could create an unbound combo to "Filter" the Main form. The combo would really work independently from your main form. The combo would act as a way to get user input. It would not be bound and would not update data in your tables. You would simply use the value of the combo to apply a Filter to the form.

[tslupphaug]

I am having a hard time following. You have a new form open so that you can add a new record? Then you would want to open your form to open and show only a new record.

It is confusing when you state that you want a data entry form and also display relative records.

Perhaps you should work on creating a Main form with a subform and get your Master/Child Link fields set up correctly. Maybe use the wizard. Afterwards, you could create an unbound combo to "Filter" the Main form. The combo would really work independently from your main form. The combo would act as a way to get user input. It would not be bound and would not update data in your tables. You would simply use the value of the combo to apply a Filter to the form.

I'll try to explain as good as I can.
My subform is set up with master/child link fields (linked to project). But the subform actually gets its data from a query, not directly from a table. The query asks for only the latest revision of each document. If you take a look at my second screenshot ("Document Details" pop-up form), I've made it so that you may add several revisions for each document. My overview "Show" form is just showing the latest status of all documents. If you double click any of the documents on the list, you'll get this pop-up so that you'll have the full history shown for each document.

The only thing I'd like to do is open the "Document Details" form, keep "Project No", "Project Name" and "Client" but have the rest emptied ready for a new document to be added (for the project that I'm currently viewing). I don't know if this helped any?


Edit, additional info about how my database is set up and works: I see that I cut out a lot of details on my first screenshot. Tried to make it easier to understand, but I might just have caused more confusion:
This is how my subform looks like when getting the data directly from my "last revision query" (qryDocsByLastRev):
(NB! Note that this shows ALL documents in my database and each document's latest revision - not sorted by which project they belong to)


And this is how it looks after I've sorted it by project using my combobox in my main form:


So if I from the previous overview e.g. double-click on the "Historical Data" document, this is what I open up: "Document Details" form, with the basic doc.info at the top and a revision sub-form:

[ItsMe]

But the subform actually gets its data from a query

I am still not understanding, perfectly. However, there are only a couple ways you can affect which records are viewed in a form. One way, you can use the .Filter property of the form. To do this, all you need is WHERE criteria.
"[ThisField] = " & MyVariable

Another way is to edit the Recordsource of the form and limit the number of records retrieved. You can create your standard SELECT statement that would apply to any scenario when the form is open and also a dynamic WHERE clause. You would combine the two strings, the generic SELECT statement and the WHERE statement, and apply this directly to the Form's RecordSource or to the SQL of a Query Object.

In order to edit the SQL of an existing Query Object, you would use DAO and QueryDefs to access a QueryDef's SQL property.