using Dlookup and criteria to match controls' value on a form

[catbeasy]

The below code is meant to tell me if the values selected in two controls already exists in the forms underlying table. So the date is selected first, then after the state is selected, per the On Update event, the code is supposed to check the table data to see if the combination of the reporting date (dt_rpt) and state selected on the form already exist in the underlying table.

I tried to add the rpt date and state as criteria so the lookup wouldn't just give me the first record. It would automatically filter for the values selected on the form controls..

It isn't working. It blows up here:

str_dt_rpt_tbl = DLookup("DT_RPT", "tbl_Data", str_cri_dt)

with an error saying: invalid use of null

Would appreciate some assistance in maybe showing if I have incorrect syntax or the approach is wrong..

Thanks for any help..

Dim str_dt_rpt_tbl As String
Dim str_state_tbl As String
Dim str_dt_rpt_state_tbl As String
Dim str_dt_rpt_state_frm As String
Dim str_msg_rec_exists As String
Dim str_cri_dt As String
Dim str_cri_state As String

str_cri_dt = "DT_RPT = " & Me.txt_DT_RPT
str_cri_state = "STATE = '" & Me.cbo_State & "'"

str_dt_rpt_tbl = DLookup("DT_RPT", "tbl_Data", str_cri_dt)

str_state_tbl = DLookup("STATE", "tbl_Data", str_cri_state)

str_dt_rpt_state_tbl = str_dt_rpt_tbl & str_state_tbl

str_dt_rpt_state_frm = Me.txt_DT_RPT & Me.cbo_State

str_msg_rec_exists = "This reporting period and state already exists in the database. Please reneter a new Reporting Period and State"

If str_dt_rpt_state_frm = str_dt_rpt_state_tbl Then
MsgBox str_msg_rec_exists
Me.cbo_Rpt_Per_MO = Null
Me.cbo_State = Null
End If

 

[pbaldy]

DLookup() will return a Null if it finds no matching record, and a string variable can not accept a Null. You can change those to Variant, or add an Nz() function to that line.

 

[catbeasy]

Thanks, you're suggestion fixed the problem, except i now have another issue. The dlookup won't work right because for some reason the Dlookup part of the code that is supposed to read the date value is returning a value of 12:00:00 AM. Here is the part:

str_cri_dt = "DT_RPT = " & Me.txt_DT_RPT
str_dt_rpt_tbl = Nz(DLookup("DT_RPT", "tbl_Data", str_cri_dt))

When I put a value of 5/1/2009 in the date field, the str_cri_dt returns "DT_RPT = 5/1/2009", but the str_dt_rpt_tbl returns the value: 12:00:00 AM. 5/1/2009 is in the table tbl_Data. The Dlookup does return the correct value for the State, so I'm thinking there is a problem in my code evaluating the date field (DT_RPT)

NOTE: I did originally have the str_dt_rpt_tbl dim'd as string and thought that was the problem, but I changed it to Date and since it shows the 12:00:00 AM value.

I tried changing the variable value of str_cri_dt to just the string and then adding the date part at the end of the criteria option in the Dlookup, but still get the same 12:00:00 AM value..

str_cri_dt = "DT_RPT = "
str_dt_rpt_tbl = Nz(DLookup("DT_RPT", "tbl_Data", str_cri_dt & Me.txt_DT_RPT))

Any ideas what I may be doing wrong?

 

[pbaldy]

A date value requires a slight syntax change. Here's a good reference:

http://www.mvps.org/access/general/gen0018.htm

 

[catbeasy]

A date value requires a slight syntax change. Here's a good reference:

http://www.mvps.org/access/general/gen0018.htm

Thanks, I made the adjustment and it now works..

There is one other thing that I wanted to do as well though that I can't figure out.

After the user has selected the Date and then the state and it determines that the record already exists, the msgbox comes up stating that's so. However, then if the user tries to do something else, a MS Access error appears telling them that the record can't contain a null value. This is because the primary key is the date and state..

I would like to be able to put some code in that, right after the message box appears, the record resets. In the form, this would be accomplished by hitting escape.

How can I do this with code? Note: I tried using the sendkeys command {ESC}, but for some reason, that piece of the code doesn't run. The code stops immediately after resetting the state and date values to null. The part of the code: str_unlock_ctls = "" doesn't fire either. If I remove the sendkeys though, it will fire..

Do you know of a way to fix this? Also, I looked up some stuff on the sendkeys command and it recommends not using this. So, is there perhaps another way for me to get the same result? Thanks for your help!

Here is the revised code:

Dim str_dt_rpt_tbl As Date
Dim str_state_tbl As String
Dim str_dt_rpt_state_tbl As String
Dim str_dt_rpt_state_frm As String
Dim str_msg_rec_exists As String
Dim str_cri_dt As String
Dim str_cri_state As String
Dim ctl As Control
If Me.NewRecord = True Then
str_unlock_ctls = str_unlock_ctls & "1"
End If
If str_unlock_ctls = "11" Then

str_cri_dt = "DT_RPT = #" & Me.txt_DT_RPT & "#"
str_cri_state = "STATE = '" & Me.cbo_State & "'"
str_dt_rpt_tbl = Nz(DLookup("DT_RPT", "tbl_Data", str_cri_dt))
str_state_tbl = Nz(DLookup("STATE", "tbl_Data", str_cri_state))
str_dt_rpt_state_tbl = str_dt_rpt_tbl & str_state_tbl
str_dt_rpt_state_frm = Me.txt_DT_RPT & Me.cbo_State
str_msg_rec_exists = "This reporting period and state already exists in the database. Please re-enter a new Reporting Period and State"
If str_dt_rpt_state_frm = str_dt_rpt_state_tbl Then
MsgBox str_msg_rec_exists
Me.cbo_Rpt_Per_MO = Null
Me.cbo_State = Null 'code stops right after this
SendKeys "{ESC}" 'This never fires
str_unlock_ctls = "" 'This doesn't fire either
Else
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
ctl.Locked = False
End If
Next ctl
str_unlock_ctls = ""
End If
End If

 

[boblarson]

1. I would argue that you should not use a composite key as a primary key. I would use an autonumber - period and let Access manage the keys. Then

2. Create a multi-field index where you can keep duplicates out by setting the multi-field index to your state/date combo.

How to create a multi-field index

 

[pbaldy]

You can try

Me.Undo

and see if it helps.

 

[catbeasy]

1. I would argue that you should not use a composite key as a primary key. I would use an autonumber - period and let Access manage the keys. Then

2. Create a multi-field index where you can keep duplicates out by setting the multi-field index to your state/date combo.

How to create a multi-field index

Ok, I can do that. Do you have any suggestions for my issue with using the sendkeys issue? Any ideas how to resolve that so the sendkeys will run or a different method to reset the form?

Thanks..

 

[boblarson]

a different method to reset the form?

Me.Undo should do it for you.

 

[catbeasy]

Me.Undo should do it for you.

Thanks, that worked perfectly..Can I also ask a general question?

Why would you not want to set up the primary key as composite? What advantage is gained by letting Access choose the PK in an autonumber and then setting an index up whose members must add up to be unique in the record..?

Thanks for any info on that. I'm not very strong in on the why's and wherefores of indexing versus primary keys..

 

[pbaldy]

Personally I have no problem with the composite key. I've got it in a few applications, with no issues. It is a debate that rages occasionally, and both sides have their proponents. I don't have a strong feeling either way.

 

[boblarson]

Primary keys are something that the system needs in order to maintain relationships to data. Who better to manage those than the system on which it relies? By using autonumbers (guarantees - for the most part - a unique number) the system can be assured that, even if the rules change for your items, that you still have a working key system. Too many times have we seen where someone creates their own keys and then finds later that the rules have to change and then it is a big pain to try to go fix old data. Also, using composite keys require you to store that same number of fields as a foreign key in the child tables, so it is inefficient. One key, one foreign key to match - much more efficient.

Hopefully that helps.

 

[catbeasy]

Primary keys are something that the system needs in order to maintain relationships to data. Who better to manage those than the system on which it relies? By using autonumbers (guarantees - for the most part - a unique number) the system can be assured that, even if the rules change for your items, that you still have a working key system. Too many times have we seen where someone creates their own keys and then finds later that the rules have to change and then it is a big pain to try to go fix old data. Also, using composite keys require you to store that same number of fields as a foreign key in the child tables, so it is inefficient. One key, one foreign key to match - much more efficient.

Hopefully that helps.

It makes sense, except the foreign key part. Wouldn't you still have to have two fields in the foreign key table? It doesn't appear to me that you could use the autonumber PK to link to a foreign key primary number since that is just a number and doesn't relate to anything specific in the foreign key table. The numbers would match up, but the names would..?

 

[boblarson]

It makes sense, except the foreign key part. Wouldn't you still have to have two fields in the foreign key table? It doesn't appear to me that you could use the autonumber PK to link to a foreign key primary number since that is just a number and doesn't relate to anything specific in the foreign key table. The numbers would match up, but the names would..?

When you use ID numbers you do things like this:

Table1
Table1ID - Autonumber (PK)
Table1OtherField - Whatever
Table1OtherField2 - Whatever

Table2
Table2ID - Autonumber (PK)
Table1ID - Long Integer (FK) ' foreign key which is primary key from Table1
Table2Otherfields - Whatever
Table2OtherFields2 - Whatever

And then when you add data to your Table2, usually it is using maybe a combo box to select Table1OtherField but the Table1ID is the data that is actually stored in the Table1ID field in Table2.

Then, when you want to get the data in a query you link between Table1ID in both tables and then select out the fields you want.

 

[catbeasy]

When you use ID numbers you do things like this:

Table1
Table1ID - Autonumber (PK)
Table1OtherField - Whatever
Table1OtherField2 - Whatever

Table2
Table2ID - Autonumber (PK)
Table1ID - Long Integer (FK) ' foreign key which is primary key from Table1
Table2Otherfields - Whatever
Table2OtherFields2 - Whatever

And then when you add data to your Table2, usually it is using maybe a combo box to select Table1OtherField but the Table1ID is the data that is actually stored in the Table1ID field in Table2.

Then, when you want to get the data in a query you link between Table1ID in both tables and then select out the fields you want.

OK, I understand now. I think I was stuck on the fact that my table doesn't have any need (currently) for another table to link it to.

Your explanation snapped something that made me realize that they are (or at least can be or are supposed to be) two separate things for separate purposes..Thanks for pushing that last atom of confusion over the cliff into the valley of understanding..