More on checkboxes

[Visiting]

Hi again, I'm having trouble on another part of the form now.
Recap- User enters a lastname into TextEntry textbox
Checkbox CheckM1 becomes checked if there is a date in TextEntry's field M1
If the user checks an unchecked box, TextEntry's DateField value should change to the current date
If the user unchecks a checked box, TextEntry's DateField value should change to null

The problem is with the last two parts, I'm not getting any update in TextEntry's DateField? Instead, new records with the DateField filled in are created in table.

I'm using the code Bry (BTW Bry, Thanks ;-)) gave out for the text entry box

Private Sub TextEntry_AfterUpdate()
Dim last As String

last = "LastName = '" & TextEntry & "'"

DLookup("CheckField", "TABLE", last)

If IsNull(DLookup("CheckField", "TABLE", last)) Then
Me.CheckM1 = False
Else
Me.CheckM1 = True
End If

End Sub

And this is CheckM1's code

Private Sub CheckM1_AfterUpdate()
If Me.CheckM1 Then
Me!DateField = Date
Else
Me!DateField = Null
End If

End Sub

 

[WildHare]

There's something missing here. What is the key to this table?

What happens after I type in "smith" and the dlookup returns a value? Are positioned at SMITH's record? Or is "SMITH" now stuck in a "last name" field, thus leading Access to think that you want to enter a new record? What happens after we retrieve the last name? How do you position to the record you want to see? And if you're positioned there, why do you need to do the second DLookup?

Jim How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[Visiting]

Jim,
The key to the table is an autonum field CustomerNum.
I think my problem is that I'm not positioned at SMITH's record. I'm just using the DLookup to display the appropriate check/uncheckedness of the box depending on the name.
So I think the Me!DateField = Date command creates a new record in my table so that it can modify the DateField field? How could I set the record to edit to be the one where the field LastName is the same as the TextEntry?

Thanks

 

[WildHare]

Ahhh...you need to position to SMITH and display that record on the form.

However, there may be some problems with using a LAST name to identify and retrive a value - how many potential SMITHs might there be in your table? Your Dlookup will only get the 'first' one.

Perhaps a better idea, that doesn't need a text box to start with, is to design a query that combines last and first names, and includes the key field:


Barer, Paul 123
Clozehoff, Oliver 49
Lingerson, Hope 17
Smith, Blacque 20
....


Then, use this query as the source for an UNBOUND combo box. Just use the combo box wizard to insert the c/b, and choose the third option, "Find a record on my form..."

When the user uses the c/b, a first-character pick will be in effect, so s/he will see ALL the Smith's if they type "sm-" and they then need to pick the right one according to First name.

The code behind the c/b will then position to the correct record according to the key value carried along with it, and display the fields on the form (assuming it's not an unbound form)

Jim





How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[Visiting]

Oooooh! That so much prettier! :-D Thanks Jim! ;-)