Thread: checkbox
View Single Post
  #9 (permalink)  
Old April 14th, 2005, 05:35 AM
rjweers rjweers is offline
Friend of Wrox
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post

To give an answer BrianWren would be proud of me for... "Yes, it is possible".

I'd leave it at that because, as Brian likes to point out, there is barely enough description to know what the problem really is. But your first post did ask "How". So...

Two ways to accompish this... (Let's assume you're going to stay with the idea of having a button to initiate the task.)

If the recordsource for FormA can contain both tables and still be an updatable query, then Greg's method is best. The recordsource can be an updatable query where the key fields are linked. That will require an INNER join of the two tables (TblA and TblB) on the ID field. To update the checkbox (Yes/No) field, include it in the query -- this field doesn't have to display on the form for you to be able to update it in code.

The difficulty with trying to make this query work is that you're talking about "adding" records. If you can't build a query that can add a record, you'll have to find another way.

So if you can't come up with the updatable query, then you can use the Click event of the button to manipulate TblB in a variaty of ways. I tend to write SQL and use DoCmd.RunSQL to accomplish this kind of update. Code something like this.

If isnull(DLookup("ID","TblB","ID = " & Me.ID)) then
    ' TblB doesn't have the record so insert it
    Docmd.RunSQL "INSERT into TblB ([ID], [Ckbox]) Select " & Me.ID & ", True"
    ' TblB has the record so update it
    Docmd.RunSQL "UPDATE TblB Set ckbox = True Where [ID] = " & Me.ID
End If

If you don't want the user to know about the Insert/Update, put
   DoCmd.SetWarnings False
before the If, and
   DoCmd.SetWarnings True
after the End If.

Now, since you say you are "adding" a record using FormA, you could use the "AfterInsert" event to "add" a record to TblB and still not need the button. If that is the case then use the DoCmd.RunSQL "INSERT Into ..." statement.

The idea that FormA only adds to TblA is a very "final" way to think about it. That is, this assumes the only possibility is that FormA adds a record to TblA. This could be the way you've designed the form. I don't often find myself writing bound forms to allow ONLY additions.

I'm not complaining about your question when I say this, but once again, you are only lightly touching on the requirement. Do you want to keep TblB synchronized with TblA? Is the relationship between TblA and TblB, one-to-many? If it is, then what happens to cause an additional record in TblB? And how do you know if the checkbox (Yes/No) of the additional record should be True or False? How do the many records of TblB relate back to TblA as far as this Yes/No field? If the relationship is one-to-one, why isn't the Yes/No field just in TblA? What happens if someone uses FormA to update TblB?

Too many questions and too little information to know what the "correct" solution is. Hopefully there is enough information in all of these posts that you'll find the way to accomplish your task.

I guess the "correct" answer to the actual question you asked is "Yes, it is possible."

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
Reply With Quote