How would the table structure for this work?

[The_Doc_Man]

My blood pressure is fine. I am still able to give more headaches than I get, so my net balance is positive.

For the record, when I come on strong it is because I'm not trying to be excessively polite. That leads to politically correct speech, which I equate with null-speak. I try to be brief and to the point. If it comes out looking like I'm hot under the collar, 'tain't so. I'm just being my usual abrasive self. But then, in a government environment, you KNOW that if you don't state your case unequivocally and using short words for the government bosses then you won't get your point across. Your point MUST be sharper than the points on their heads or you will get ignored.

Or is that just an artifact of my office environment?

 

[thisisntwally]

Or is that just an artifact of my office environment?

unless we're in the same office, i get the impression that egotism and obtusity is par for the course.

 

[rainman89]

edit: all of my silly questions aside, i think you guys have convinced me that the marginal benifit of childing out the documentation at least equals the marginal cost. which is good enough for me

Now we have the same notion of value added?????!!!!

 

[thisisntwally]

well rainman, i can't be totally sure, but in short - yes for now . hey, how did that linking form from a new record business you were looking at the other day work for you?

 

[rainman89]

well rainman, i can't be totally sure, but in short - yes for now . hey, how did that linking form from a new record business you were looking at the other day work for you?

didnt figure it out. i tried a whole bunch of different ways, but couldnt get it like i wanted

 

[thisisntwally]

Private Sub comContinue_Click()
Dim temp       As Variant 
On Error GoTo Err_comContinue_Click

temp = SysCmd(acSysCmdSetStatus, "Filling table...")       
         With DoCmd
        .RunCommand acCmdSaveRecord
        .RunSQL "UPDATE [tbl Modifications] SET [EntryStatus]=(1) WHERE [keyID]= [txtkey]"
        .openForm "Update", acNormal, , "[keyID]=" & Me!txtkey, acFormEdit, acWindowNormal
        End With
        
With CurrentDb.OpenRecordset("tbl Modifications", dbOpenTable)
        .AddNew
          !EntryStatus = 0
          .Update
End With

does this look at all helpful?

 

[Banana]

Why are we doing this in VBA? This seems simple enough to let Access handle it, either by using a query or a subform.... No code required.

 

[rainman89]

Private Sub comContinue_Click()
Dim temp       As Variant 
On Error GoTo Err_comContinue_Click

temp = SysCmd(acSysCmdSetStatus, "Filling table...")       
         With DoCmd
        .RunCommand acCmdSaveRecord
        .RunSQL "UPDATE [tbl Modifications] SET [EntryStatus]=(1) WHERE [keyID]= [txtkey]"
        .openForm "Update", acNormal, , "[keyID]=" & Me!txtkey, acFormEdit, acWindowNormal
        End With
        
With CurrentDb.OpenRecordset("tbl Modifications", dbOpenTable)
        .AddNew
          !EntryStatus = 0
          .Update
End With

does this look at all helpful?

No, not at the moment it doesnt.....
all i want is the combo box to change to the value when the other form gets focus back....

 

[thisisntwally]

ah. not that im going to be able to help you, but the problem does lie in the fact that in adding the new value for the combo there is no explicit record reference in the add form, as it is new?

edit: Banana, how would you do this with queries or whatever. While i was proud of my solution, Im always interested in alternatives.

 

[CraigDolphin]

Maybe CraigDolphin can do a better job of describing the summary problem

The day I can ever do a better job with anything to do with Access than you or Doc_Man is the day I take up skiing in hell

I take back my comment about nested iifs. You can avoid that easily enough as it turns out. To count files found in 3 fields of a table using a query might look something like Banana's example. Or for an alternative:

MyCount: iif([Field1] & "" = "",0,1) + iif([Field2] & "" = "",0,1) + iif([Field3] & "" = "",0,1)


Any time you add another field you need to add another + iif([Fieldx] & "" = "",0,1) bit to this query (and anywhere else you might do a count [eg forms, vba etc]).

By contrast, using a subtable the expression would be
MyCount: DCount("FileFieldName","ChildTableName","[TitleID]=" & [ParentTableName]![TitleID])

It never needs to be changed when additional files begin to be tracked.

Now what if you only want to count certain types of files? Say, ".doc"?

The solution with a child table setup is simple and future proof:

MyCount: DCount("FileFieldName","ChildTableName","[TitleID]=" & [ParentTableName]![TitleID] & " AND Right([FileFieldName],4)='.doc'")

Alternatively, the following would work but needs 'adding to' if ever more than 3 files are required.

MyCount: iif(Right(Nz([Field1],"Missing"),4)=".doc",0,1) + iif(Right(Nz([Field2],"Missing"),4)=".doc",0,1) + iif(Right(Nz([Field3],"Missing"),4)=".doc",0,1)

Lord help you if you forget to edit a counting function buried somewhere in your db app (maybe in a vba module, maybe a query) and calculations start going awry, totals start being off, and your PHB starts looking for someone to blame.

My experience over the last couple years has taught me one big lesson: whenever someone says there will only ever be a maximum of x do-hickeys they are probably lying. They may not realize it yet, but there will come a day when they will arrive and ask for you to add 'just one more' type of do-hickey. This will probably not be the last time they do this.

For example, you are talking about a maximum of two required documents by current law and you are expecting one document=one file.

1. Laws can change. Do you really want to have to redo your app every time this happens?
2. Is it ever possible that one 'document' might be split into separate files? Eg, one document might arrive in two differing formats for different sections of the same 'document'. Maybe 'maindocument1.doc' and 'appendixAtomaindocument1.xls'. Or it might be broken into 'parts' because some file formats have a filesize limit (eg. mdb).
3. A future PHB decides he wants to track more than the legally rerqired types of documents. (Illegal warrantless wiretapping transcripts, for example )

 

[rainman89]

ah. not that im going to be able to help you, but the problem does lie in the fact that in adding the new value for the combo there is no explicit record reference in the add form, as it is new?

edit: Banana, how would you do this with queries or whatever. While i was proud of my solution, Im always interested in alternatives.

There is a save function done before close and a requery on the combobox on rentry to the form. so the record will show in the combobox.the user will have to navigate to it to find it. just want to have it default to that after the new addition is all.

 

[thisisntwally]

wow. i think there was enough in that post to keep me busy for a week, craigdolphin.

grazie mille

rainman, i understand now. out of curiousity, how did you do what you did? something along those lines is on my todo list, though it had yet to make 'action item'

 

[rainman89]

based my cbo off a query, then used my other form to add a new entry to the table and then requeried on the gotfocus of the main form.

 

[thisisntwally]

capisco. i've yet to add requery or refresh, to my list of readily accessible tools. heheh

 

[boblarson]

capisco. i've yet to add requery or refresh, to my list of readily accessible tools. heheh

Just a simplistic, quick Tutorial -

Refresh will only return changes made to records that existed within the recordset. So, if you add any new records, refresh will not reflect those.

Requery is just that. It pulls the data into the recordset again and that would include all additions, deletions, and edits since it was done before.

 

[rainman89]

Just a simplistic, quick Tutorial -

Refresh will only return changes made to records that existed within the recordset. So, if you add any new records, refresh will not reflect those.

Requery is just that. It pulls the data into the recordset again and that would include all additions, deletions, and edits since it was done before.

go to work!!! you arent supposed to be on here i thouhght!

 

[boblarson]

go to work!!! you arent supposed to be on here i thouhght!

I AM on lunch break at work (my old job) . I still have a week to go until I move to the other full-time. And, I haven't been around as much lately, but when I am I hit as many posts as I can.

 

[Banana]

Wait, wait. You can hit posts?

How do that you do that? Does that hurt your computer monitor?

 

[boblarson]

Does that hurt your computer monitor?

Yep, and they wonder, here at work, why I've gone through 12 monitors in 12 months

 

[rainman89]

I AM on lunch break at work (my old job) . I still have a week to go until I move to the other full-time. And, I haven't been around as much lately, but when I am I hit as many posts as I can.

forgot you are on the WRONG side of the country