Help With a Form

[PsYc0TiC]

I tried searching for this but didn't find anything that helped so here goes...

I have a database I made with a data table. The data table has 21 fields in it... 4 of which are calculated fields that require no input from a user with a form and are simply there to be retrieved by a report by me (along with any and all other data in the records).

I have 2 different groups that need to be able to input different data in each record in the database.

Example:

Data input form #1 - Inputs data in fields 1 through 14 (fields 15 & 16 are calculated for a percentage and auto populated)
Data Input form #2 - Inputs data in fields 17 through 19 (fields 20 & 21 are calculated for a percentage and auto populated)

I have no trouble making "Data Input Form #1" and submitting that data but I don't know how to make a separate form "Data Input Form #2" know and show all incomplete records needing fields 17 through 19 filled out and be able to fill them out one at a time until there are no more.

Does this make sense?

Thank you very much in advance for any and all help.


EDIT: Forgot to add... using Access 2010

 

[alansidman]

First, calculated fields while they can be created in the later versions of Access are considered no no's by experienced Access developers. All calculations should be performed in Queries. Then use your query as your record source for any reports you wish to generate. In this manner, all calculations are up to date at all times.

Second, a table may only be bound to one form. Having two forms to input data to the same table is not a functionality that is available within Access.

Suggest you relook at your design.

Here is a link that will help with clarifying these issues.

http://sbuweb.tcu.edu/bjones/20263/access/ac101_fundamentalsdb_design.pdf

 

[Micron]

Agree on advice regarding calculated fields and use of queries.
Don't agree on second point. I'm sure I could make any number of forms that use the same table - just maybe not at the same time.
If poster was doing as suggested (using queries instead of tables) this would be much easier. The query behind the second form would present the fields for those records whose first sector was filled out.
Not that I believe I'd use two forms as opposed to one.

 

[PsYc0TiC]

Agree on advice regarding calculated fields and use of queries.
Don't agree on second point. I'm sure I could make any number of forms that use the same table - just maybe not at the same time.
If poster was doing as suggested (using queries instead of tables) this would be much easier. The query behind the second form would present the fields for those records whose first sector was filled out.
Not that I believe I'd use two forms as opposed to one.

That is along the lines of what I was trying to figure out.

Needless to say I am an absolute novice at access so queries and forms are obviously confusing me.

Forgetting about the calculated fields since that is not important in what I am trying to do... Lets just say I have a table with 10 fields and I have 2 different departments that have different fields they are responsible for filling out.

Department 1 fills out fields 1 through 7 and department 2 fills out fields 8 through 10 all using the same record ID.

Department 2 needs to open their form and see all of the records that department 1 has filled out and that need to be finished.

I don't have a problem understand that this can be accomplished with 2 tables... 1 for department 1 and another for department 2 but then I am lost on how to relate them together since even though there are 2 different tests being performed by 2 different departments... each record is related.

It would seem that I just do not know enough about access to pull this off at this time.

 

[xenou]

Micron's suggestion is where I would go also. Source the form with a query that selects only those records that have the first part filled out and not the second part. But if you have no idea what a query is then this probably means nothing to you.

 

[Micron]

I don't have a problem understand that this can be accomplished with 2 tables... 1 for department 1 and another for department 2 but then I am lost on how to relate them together since even though there are 2 different tests being performed by 2 different departments... each record is related.

You have missed the point. This can be accomplished easily with queries. To have two tables for this is not only poor database design, it unnecessarily complicates things - to a substantial degree. If you have no understanding of Access queries or database normalization, do yourself a BIG favor and go through some tutorials on the various topics. There are TONS of them out there. I'd venture to say that this forum is for helping others solve issues where there is at least some understanding of the issues involved as opposed to being a means of teaching the basics from scratch and you need to understand the basics to understand the solutions proposed. Find some tutorials, and check out these links for other important topics, then come back if you need help to structure a query.
General: Use Autonumbers properly
Microsoft Access tips: Problem names and reserved words in Access
Autonumbers - Access wiki - Access Help and How-to - Microsoft Office by UtterAccess.com
MS Access Naming Conventions - Access World Forums
General: Commonly used naming conventions

 

[PsYc0TiC]

So doing much more digging online I was able to create the form I was needing and using VBA code make it do great.

However I am also a novice at the code so I was hoping someone could help with a little modification.

The code I used to pull up records in a drop down is this:

Private Sub cboGoToRecord_AfterUpdate()
     On Error Resume Next
     Dim rst As Object
     Set rst = Me.RecordsetClone
     rst.FindFirst "AuditID = " & Me.cboGoToRecord.Value
     Me.Bookmark = rst.Bookmark
End Sub

This displays all records in the database in order showing the P.O. number and the status of the ticket. The 2 choices of status (which are in a table of their own) are "Waiting on Lab" and "Complete:

I have 1 form for people to input data and the status is default value of "Waiting on Lab" and they submit their set of the data.

I have the second form for the lab personnel and the drop-down box mentioned above using the code... however I really would like it to only show data with a status of "Waiting on Lab" and none of the "Complete" since the list could get very long at some point and the second form only needs to be used to update the unfinished records (Waiting on Lab).

Anyone know how to make that happen in the drop-down box code?

 

[xenou]

There is no need for code. Just use a form that only pulls records with the specific criteria applied. If you have never done this before, then experiment with a test database. For instance, load a database with 10 movies in it (Batman Lives, Batman Returns, Batman vs. Joker, Superman XVI, Superman vs. Megamind). Then build a form that only shows movies with Batman in the title. That's the basic idea. No vba is required at all.

 

[PsYc0TiC]

There is no need for code. Just use a form that only pulls records with the specific criteria applied. If you have never done this before, then experiment with a test database. For instance, load a database with 10 movies in it (Batman Lives, Batman Returns, Batman vs. Joker, Superman XVI, Superman vs. Megamind). Then build a form that only shows movies with Batman in the title. That's the basic idea. No vba is required at all.

Ok... that is the idea.

Will you please help me with that?

 

[xenou]

There are numerous videos on youtube for creating forms and queries in Access as well as hundreds of sites for learning Access. You really just need to get some basic lessons for yourself.

As far as your current task goes, it would just be:

SELECT 
    Field1, Field2, Field3, Field4, Field5, Field6, Field7 
FROM Table1 
WHERE
    (Field8 IS NULL and Field9 IS NULL and Field10 IS NULL)

That a simple query that gives you the records with the first seve fields filled in and the last three fields not filled in. That query becomes your form source.