Solved: Linking Table entries with IIF statement

[David627]

Is there a way to pull information from fields in a table to an IIF statement to run? I have a user who has a form and On Update he wants to check for conflicts that are currently hardcoded in the property sheet of the form attached to a command button. Instead of hardcoding them, I thought since there will be many months of data, if he could have his date checking pieces in a table and a VBA module checks the table, it would be more versatile - the table data could be easily added to and events deleted once they have passed...

He has an IIF statement that works -
=IIf([residency]="9-1" And [DateofIncident]="2/14/2009" Or "2/15/2009",MsgBox("Attention: Potential Conflict (STAc Basketball-BU Events center)with an ETIP(Events with Transportation Impact)"))


I am looking to have fields setup for [residency], [DateofIncident] - which would probably have to be a date range and [Event].

Can this be done?

Thanks
David

[CreganTur]

The only way to pull field information I can think of right now is off of a Form. Go into your query, pick the field you want to check criteria for, right click on it, and select build. Drill down to the form you want and then double click on the field you want to work with. This will setup your criteria to look at the field's value on the form- the form must be open to do this!

The criteria will look like: Forms!FormName!FieldName
where FormName is the form's name, and FieldName is the name of the field on the form you're wanting to look at.

HTH:thumb

[David627]

The criteria will look like: Forms!FormName!FieldName where FormName is the form's name, and FieldName is the name of the field on the form you're wanting to look at.

Using this logic, can I call a table by using Tables!TableName!FieldName where TableName is the table's name, and FieldName is the field name?

David

[CreganTur]

Using this logic, can I call a table by using Tables!TableName!FieldName where TableName is the table's name, and FieldName is the field name?

Here's the issue with that: you're calling the field's name, but are not able to specify which record in the field you want to look at. Theoretically, you would be pulling all values for that field within the table. This, obviously, is not what you want.

When you are calling the value of a field on a form, you are only calling that field's value (it's actually a textbox and not a field... but that's just semantics). You're referncing a single object and its value, which is why it can work within the query.

Does that answer your question?

[David627]

Does that answer your question?

That answers my question - just not sure a form would work although user wants to use a form to enter the data. Guess I could build him a query to pull this stuff.

As an alternate - can you take info on a form and populate a VBA procedure?

Is this more complicated (making dynamic) than just hardcoding everything in???

David

[CreganTur]

As an alternate - can you take info on a form and populate a VBA procedure?


Quite easily. Take the value from a form's textbox object and save it as a variable value. You can use variables in your hardcoded SQL statements. You would use the DoCmd.RunSQL method. Next to this you would write out your SQL statement as a string.

I imagine that the variable values would be used as a part of your SQL string's WHERE clause. To do this you would concatenate the variable with the string, but you must wrap the variable with a symbol that tells SQL what data type you're dealing with. Strings are wrapped with single quotes, dates with pound signs (#), and numbers do not use symbols.

Example:
DoCmd.RunSQL "UPDATE tblName Set tblName.DueDate = #" & varDate _
& "# WHERE (tblName.CustName = '" & strCustName & "' AND tblName.ID = " & intCustID & ");"

HTH:thumb

NinjaEdithttp://img293.imageshack.us/img293/9060/ninja3od8.gif: You cannot run SELECT statements using the DoCmd.RunSQL method. It is only for running Action queries or SQL DDL queries. If you want to run a SELECT query via your VBA, then the query must exist as a query object in your database. You can call it via DoCmd.OpenQuery.

The exception to this is that you can run SELECT statements via ADO and DAO connections to create recordsets... but that is a part of ADO/DAO functionality.

[David627]

Thanks for all the info, however this is way over my head and beyond my skill level. :hide:

He's just going to have to hardcode the IIF statements...

David

[David627]

Could an option be to make it a recordset based VBA like the following?

On Before Update

Set variables of [residency], [DateofIncident] and [MsgBox Message]
Declare recordset
Locate value in [residency] and [DateofIncident]
If [residency] = value in recordset
If [DateofIncident] = value in recordset
Display Msgbox [MsgBox Message]
Docmd.CancelEvent (so record isn't updated)


Is any of this viable?

Thanks
David

[CreganTur]

Doing this via a recordset would be just as difficult for you, since it would require some of the procedures I outlined in post #6.

Have you considered the idea of using a DLookup function to pull the data you need from your table? It might not be a good fit for you.

If it isn't, then describe exactly what you're trying to accomplish and provide the name of any Form objects that you are trying to accomplish and I will give you a code example for a recordset that fits your requirements.

[David627]

A DLookup would be a good choice - I'll try that.

Thanks
David

[David627]

According to a programmer I know she thinks my alternative might work.... going on that assumption what do you think the code would look like?

David

[CreganTur]

According to a programmer I know she thinks my alternative might work.... going on that assumption what do you think the code would look like?


Well personally, I prefer to use ADO (ActiveX Data Objects) to create recordsets (As opposed to DAO - Data Access Objects). There's really no difference between the two (except for syntax), unless you use them to interact with other database constructs... mySQl, for example. Then which one you use can become very important.

You can create a recordset by using a SQL string to query the database table, so that it only pulls the records that you want. Here's the example code; explination will follow:

Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset

Set conn = CurrentProject.Connection '<<<Connect to this database
Set rst = New ADODB.Recordset '<<<Create new instance of recordset

'we tell the recordset what records to pull by using a SQL string
rst.Open "SELECT FirstName, LastName, EMployeeID " _
& "FROM tblEmployees " _
& "WHERE EmployeeID = " & Me.txtEmpID & ";", conn

rst.MoveFirst '<<<Explicitly move to first record in recordset
'^This is only really required if multiple records are expected to be returned

'display returned results for first record in recordset
MsgBox "Returned Results:" & vbCrlf & vbCrlf _
& rst.Fields(0).Value & " " & rst.Fields(1).Value & rst.Fields(2).Value

'clear memory and release objects; very important
rst.Close
Set rst = Nothing
Set conn = Nothing

In this example I am assuming that you have a form with a textbox named txtEmpID. Your user would enter the number of the employee whose records they want to return. You can see where I pull the value from this txtBox in the SQL string.

One way to return values from a recordset is by using the Fields() method. The numbers in the parentheses refer to the index number of the field in the recordset. The index numbers generally are in the same order as the fields you outline in your SQL string. If it's the wrong order, you can always rearrange ;)

I hope this gives you a good starting point for your idea. Let us know if you need any more help to get it working.

[David627]

I hope this gives you a good starting point for your idea. Let us know if you need any more help to get it working.

This does - thanks Randy.. something to start fresh next week. I'll bounce the code off you for syntax after I customize.

David