Automatically uncheck box 2

[wx5chr]

I have a subform which is datasheet view. I would like to have the database automatically uncheck the previous checkbox when a new record is added.

What I'm doing is using the check box (CurrentCk) to determine which record is current.

example:

Amdt 5 12/25/03 CurrentCk = Yes

When I add Amdt 6, I want Amdt 5 CurrentCk to be set to no.

Any help is appreciated.

 

[LarryDeLaruelle]

I have a similar situation where I use SQL to select the currently identifed active record and uncheck the box.

This is the code:

Dim dbTemp As DAO.Database
Dim rsTemp As DAO.Recordset
Dim strSQL As String


strSQL = "SELECT DateEnd, CurrentStatus FROM tblRefStatus "
strSQL = strSQL & "WHERE (((tblRefStatus.RefID)='" & Me.RefID & "') AND ((tblRefStatus.CurrentStatus)=-1));"

Set dbTemp = CurrentDb
Set rsTemp = dbTemp.OpenRecordset(strSQL)
If rsTemp.RecordCount > 0 Then
rsTemp.MoveFirst
Do Until rsTemp.EOF
With rsTemp
.Edit
!DateEnd = Me.txtStart
!CurrentStatus = 0
.Update
End With
rsTemp.MoveNext
Loop
End If
rsTemp.Close

I call this from a Save command button.

Although only one record should be returned, I use a Do Loop just in case.

If you need any help tweaking this code for your use, let me know.


Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[gasx]

try this code:

if currentCK=ture then
(01/25/03.value=amdt5
else
(01/25/03.value=amdt6

end if

have a good luck!

 

[wx5chr]

I apologize, but I do need some help customizing the code.

The table name is T_Dates with the following fields:
Amdt
Rev_Pub_Date
Next_Rev_Date
Remarks
CurrentCK

Do you need anything else? I'm going to try tweaking it myself as well.

 

[wx5chr]

Larry,

I tried using your code, however I got a "User-defined type not defined" error on the Dim dbTemp as DAO.Database line.

Any ideas?

 

[Golom]

DeVere
You need a reference to Microsoft DAO 3.x Object Library. From any form select Tools / References and check the appropriate reference.

 

[LarryDeLaruelle]

DeVere:

Did Golom's advice fix it for you?

Do you still need help with the code?


Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[wx5chr]

I don't see any selection for references under the tools menu. Am I just that blind. By the way, I'm using Access 2000.

 

[wx5chr]

Larry and Golom,

I found the references area. I understood Golom to say just to open a form, but in actuality I needed to open the VB window, then use the Tools menu.

Larry, I'll get back with you about helping modify the code.

 

[wx5chr]

Larry, here's my modified code:

Dim dbTemp As DAO.Database
Dim rsTemp As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Rev_Pub_Date, CurrentCk FROM T_Dates "
strSQL = strSQL & "WHERE (((T_Dates.Amdt)='" & Me.Amdt & "') and ((T_Dates.CurrentCk)=-1));"

Set dbTemp = CurrentDb
Set rsTemp = dbTemp.OpenRecordset(strSQL)

If rsTemp.RecordCount > 0 Then
rsTemp.MoveFirst
Do Until rs.Temp.EOF
With rsTemp
.Edit
!Rev_Pub_Date = Me.Rev_Pub_Date
!CurrentCk = 0
.Update
End With
rsTemp.MoveNext
Loop
End If
rsTemp.Close

Now I'm getting a "too few parameters. Expected 2." on the Set rsTemp = dbTemp.OpenRecordset(strSQL) line. Any ideas?

 

[LarryDeLaruelle]

DeVere:

Is Amdt a date field?

If so, use the # sign instead of the single quote.

If it's a numerical field, you do not need to use the single quotes (only necessary for text fields).

That's all I can see that might be a problem.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[wx5chr]

I tried it, to no avail. I still get the same error, "too few parameters. Expected 2." My field types are as follows:

Rev_Pub_Date is a date field
Proc_ID is numerical
CurrentCk is a checkbox (Yes/No)

Here's my updated code:

Dim dbTemp As DAO.Database
Dim rsTemp As DAO.Recordset
Dim strSQL As String

Set dbTemp = CurrentDb

strSQL = "SELECT Rev_Pub_Date, CurrentCk FROM T_Dates " & _
"WHERE (((T_Dates.Proc_ID)=" & Me.Proc_ID & &quot " & _
"AND ((T_Dates.CurrentCk)=-1)) ORDER BY Rev_Pub_Date"

Set rsTemp = dbTemp.OpenRecordset(strSQL)

If rsTemp.RecordCount > 0 Then
rsTemp.MoveFirst
rsTemp.MoveNext
Do Until rs.Temp.EOF
With rsTemp
.Edit
!CurrentCk = 0
.Update
End With
rsTemp.MoveNext
Loop
End If
rsTemp.Close

 

[LarryDeLaruelle]

DeVere:

If that is an accurate copy of the code, you are missing the semi-colon at the end of the statement.

Proc_ID is a Number type?

What I frequently do to check myself with sql code is to create the query using the query grid and then look at the sql (Tool Bar - View/SQL View). Try that and see if your code matches the sql view.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[wx5chr]

I added the semicolon and Proc_ID is a number type.

I've never done the procedure you describe. Can you give me more info on how to do it.

 

[wx5chr]

I Feel so dumb. I didn't bother to check the field name. It turns out that for some reason, I used CurrentCk as the caption, but the actual field is Current. It's working now. Thanks.

 

[LarryDeLaruelle]

DeVere:

Great.

To answer the question, create a query as you normally would using the query grid. Once you have it working and producing the results you want, click on view from the menu bar (My apologies, not Tools) and then select SQL View.

This will show you the SQL structure behind the query.

I hate SQL syntax so I will usually create my query with the grid and then copy/paste the SQL code in to my event procedure.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[wx5chr]

Thanks for the tip. I really appreciate everything you've done for me.

 

[LarryDeLaruelle]

DeVere:

Happy to help; I've receive (and still do) my share of help from TekTips experts.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org