Question about dlookup

[rikklaney1]

I am trying to make a single form that picks up variables when opened by a macro. Easy enough. Now the problem part. when the form opens I'm trying to use dlookup to set values in some other fields. I have this.....

Me.Text1.Value = DLookup(Form!act1, Me.table.Value, "[SCdate] = Form!date24")


but it is giving me a run time error 3464 data type mismatch in criteria expression. Any ideas what I'm getting wrong?

 

[MarkK]

Look at your criteria expression. It is a single string literal. It does not evaluate the contents of the control at "Form!date24". You should do that evaluation outside the string, and then concatenate that result, like . .

"[SCdate] = " & Form!date24

Do you see the difference?

 

[rikklaney1]

I see what you meant about it not evaluating the last control. When I changed it to your example I still get the error but at least when I hover over each control they are showing the correct values so I'm one step closer. Now for the part I don't understand... concatenate the result?

 

[MarkK]

Oh yeah, and dates need date delimiters, I wasn't looking at it from that angle. Try . . .

"[SCdate] = #" & Form!date24 & "#"

Maybe that actually works now?
Cheers,

 

[rikklaney1]

Nope. Still a data type mismatch. I'm out of my depth. lol

 

[Galaxiom]

I am somewhat confused by what you are trying to do but I don't think Form is a valid reference in that context.

Me.Text1.Value = DLookup(Forms!formname!act1, Me.table.Value, "[SCdate] = Forms!formname!date24")

act1 would need to be control or field holding a string that is the name of a field or an expression comprising fields from the table.

The current form would have to include a control or field called "table" that held a string that was the name of a table or query.

BTW It is not necessary to concatenate a value to a DLookup. Access will interpret the reference and deal with the datatype and delimiters automatically.

 

[rikklaney1]

Still a damn date type mismatch. Have I actually stumped the experts? You guys are awesome BTW. Every time I come here for help I learn something new. Even if it sometimes is just that you can't do that.

 

[Galaxiom]

Still a damn date type mismatch. Have I actually stumped the experts?

No you have not stumped the experts. You simply have not provided enough information about what you are trying to do.

I suspect either act1 or Me.table do not hold strings.

Either that or you tried my suggestion before I completed the edit.

 

[rikklaney1]

act1 is a a combination of 2 text boxes with a value set when the form is open so the value is either act1-1 or act1-3. Activity 1 on first shift or third shift depending on when the form is open. act2 = act2-1 or act2-3 etc. etc.

Table is set by the macro used to open the form, in this case it is tblCln-RS5.


TblCln-RS5 contains records for activities 1 to 10 for both shifts. Act1-1 thru act1-10 and act1-3 to act10-3. The activities are scheduled for different dates for each shift and operator so rather than make 16 individual forms I thought I could set table and shift when opening the form and use dlookup to set the records shown by those variables. Does that make any sense? I had actually tried your solution before the edit but even after the edit I am still getting the data type mismatch.

 

[Galaxiom]

act1 is a text box with a value set when the form is open so the value is either act1-1 or act1-3.

There is the problem. Special characters in the names so they must be surrounded by square brackets. Similarly the table name.

More importantly your data structure is incorrect.

The activities should not be in separate fields. They should be in separate records, probably in a related table, with another field to record the activity number.

With this structure the activity is selected in the RecordSource query or a Filter and you avoid having to do weird stuff like that DLookup.

 

[rikklaney1]

Ok. Here's what I have in those tables. Each activity is in a field act1-1, act1-3, act2-1, act2-3, etc.

each record is a day. 1/1/2016, 1/2/2016/, 1/3/2016, etc.

the form has 7 columns with 10 rows each. There is a value in each box, p, on the day the activity should be done. so the operator has this


_______date___date+1___date+2___date+3___Date+4___date+5___date+6___date+7

act1_____p________p________p________p________p________p
act2_____p________p________p________p________p________p
act3_____p________p________p________p________p________p
act4_____p________p________p________p________p________p
act5_______________________p
act6______________p
act7________________________________p
act8_________________________________________p
act9________________________________p
act10_________________________________________________p


The p signifies that on that day the activity is to be done. It varies for each operator and shift. Some items are done by everyone daily others are done by one person once a week and the activities are not the same for all stations.

Now the first date column always represents the day the form is opened. the others are the coming week When the operator clicks on the box containing the P it changes it to a Q and records it to the field for that activity on the record with that date. On the activities that are dailies the operator can only click on the column for todays date. Something done weekly they can work ahead and when they click it will change to a T signifying it was done early. Now, does anyone have a headache following that? lol. Is there a better way to do this than what I am attempting with about 70 dlookups on the open event for the form? I'm open to any suggestions.

 

[rikklaney1]

BTW I figured out the answer to the original question. It's this

Me.text1.value= DLookup("[" & act1 & "]", "[" & table & "]", "[scdate] = form![date24]")

 

[Galaxiom]

BTW I figured out the answer to the original question. It's this

Me.text1.value= DLookup("[" & act1 & "]", "[" & table & "]", "[scdate] = form![date24]")

Yes that will work but there is one small point to consider. When used in VBA, expressions best practice is to qualify the names by referring to controls. So best use Me.act1 rather than just act1.

This makes it clear to both the program and any developer who follows you (and indeed, you sometime down the track) that the term is referring to an object on the form rather than a variable in the code.

(Don't use Me on expressions on the ControlSources on the form itself.)

 

[Galaxiom]

Ok. Here's what I have in those tables. Each activity is in a field act1-1, act1-3, act2-1, act2-3, etc.

each record is a day. 1/1/2016, 1/2/2016/, 1/3/2016, etc.

the form has 7 columns with 10 rows each. There is a value in each box, p, on the day the activity should be done. so the operator has this


_______date___date+1___date+2___date+3___Date+4___date+5___date+6___date+7

act1_____p________p________p________p________p________p
act2_____p________p________p________p________p________p
act3_____p________p________p________p________p________p
act4_____p________p________p________p________p________p
act5_______________________p
act6______________p
act7________________________________p
act8_________________________________________p
act9________________________________p
act10_________________________________________________p


The p signifies that on that day the activity is to be done. It varies for each operator and shift. Some items are done by everyone daily others are done by one person once a week and the activities are not the same for all stations.

Now the first date column always represents the day the form is opened. the others are the coming week When the operator clicks on the box containing the P it changes it to a Q and records it to the field for that activity on the record with that date. On the activities that are dailies the operator can only click on the column for todays date. Something done weekly they can work ahead and when they click it will change to a T signifying it was done early. Now, does anyone have a headache following that? lol. Is there a better way to do this than what I am attempting with about 70 dlookups on the open event for the form? I'm open to any suggestions.

The table would normally be constructed with fields:

ActivityID (Primary Key)
OperatorID
ActivityDate
ActivityTypeID
ActionCode (what the user enters)

Your form would have the Recordsource varied for each operator. This can be done in the RecordSource query. Similarly the date range so that only records for the specified time period are shown.

If you want to maintain the same form layout then put seven subforms on the main form each covering a day. Set the Border, RecordSelector ect Properties of the subform and SubFormControls to No so the subforms look like they are part of the main form.

Note you only need one subform object, not seven. Use multiple instances of this one form (one for each subformcontrol) and set the Recordsources to the date and operator as they load. (I can tell you about that later once you get the idea of the underlying structure.)

In this structure the user can actually bring up any time in the future so you wouldn't need Q to signify "early".

Any number of actions can be loaded by simply adding more records. Only the actions relevant to the user and date need exist so there are no spare "holes" in the form.

Code can be used to generate the required records based on a tables with operators, actions and action frequency.

Take your time to get your head around this structure and the rest of the project will start falling into place without things like seventy DLookups. Always remember, information should be held in fields in records. If you find yourself putting information like dates into the structure itself you are heading down the wrong track.

 

[rikklaney1]

I like the table idea. Here's a funny thing though. One of my reasons for going with dlookup was to keep forms to a minimum. I've used subforms before but it actually NEVER occurred to me to use the same subform and just change it's recordsource on the main form. I've had seven subforms all the same with different sources on a main form before. lol. It just never occurred to me to do it that way. Okay. now to do a bit of a redesign. Thanks for the help.

 

[Galaxiom]

Note that you will need to use separate instances of the subform. Otherwise the RecordSource will change for them all.

In the main form Form_Load Event, each subformcontrol will need to be processed with code like this.

 Dim mysubform1 As Form
  
 Set mysubform1 = New Form_mysubform
 
 With Me.Controls("subformcontrol1").
     Set .SourceObject = mysubform1
     .Form.RecordSource = "SELECT ....etc"
 End With

You can do it in a loop to minimise the code if you use a naming pattern for the subformcontrols.