Auto Tab Control Based on If Value is Present 1

[Mke85]

Hello! Having troubel figuring this one out.

My main form is called "Loans". Within that form I have a Tab control called "LoanDetailsTabs". Tab one is called "IndividualItemsLoans and has a subform called "Loandetails. Tab two is called "DeployableKitsLoan" and subform called "LoanDetailsKits".

What I am trying to do is check to see if there is a value present within a field called "EquipmentID" on the "LoanDetailsKits" subform. If a value is present I want the tab control to automatically switch to the "DeployableKitsLoan" tab. I currently have the coding on the Form_Current event but it always returns a "Object doesn't support this method error".



Any ideas?

 

[dhookom]

Please share your code. I am thinking I would just do a DLookup() in code to find a value in a table.

Duane
Hook'D on Access
MS Access MVP

 

[Mke85]

Haha my bad forgot to paste it. Ive tried a few things so far. Im still a little new to vba and access so I may be referencing tables and fields completely wrong.

If [LoanDetailsKits].[EquipmentID] <> 0 Then
[DeployableKitsLoan].SetFocus
End If

If IsNull(DeployableKitsLoan) Then
[Loandetails].SetFocus
End if

Thanks for the help!

 

[dhookom]

Where is this code running? There must be some statements like Private Sub or similar.
Again, I would NOT look at the subforms in the tab controls. Just look at the tables that actually store the data.

Duane
Hook'D on Access
MS Access MVP

 

[Mke85]

Sorry yes it is a private sub running On Current from the main form "Loans"

Private Sub Form_Current()
End sub

LoanDetailsKits is the subform that appears on Page Index 1 of the Tabcontrol. LoanDetails is the subform that appears on Page index 0.

 

[dhookom]

Do you understand what I mean by "Just look at the tables that actually store the data"?

Duane
Hook'D on Access
MS Access MVP

 

[Mke85]

I dont think I do.

Can you explain a little more? I still a little new to sql, vba and access

 

[dhookom]

I think you want to set the focus based on if there is a related record in another table. It would help if we knew the table and field names as well as the key fields and data types. The key fields are typically used in the Link Master/Child properties.

If DCount("*","KitsTableName","PrimaryKeyField =" & ForeignKeyField )> 0 Then
   Me.[DeployableKitsLoan].SetFocus
End If

Duane
Hook'D on Access
MS Access MVP

 

[Mke85]

Alrighty:

Main table/form: Loans
P-Key: LoanID

LoanID is the Master/Child link within properties.
Subform #1: LoanDetails
P-key: LoanDetailsID
Fields: LoanID,ProductID, Quanity, EquipmentID, Notes

Subform #2: LoanDetailsKits
P-Key: LoanDetailsKitsID
Fields: LoanID, EquipmentID, Notes

Tab Control: LoanDetailsTabs
Page Index #0: LoanDetails
Page Index #1: LoanDetailsKits

I'll see if I can come up with a DCount solution.

Thanks!

 

[Mke85]

Got it!

If DCount("[EquipmentID]", "[LoanDetailsKits]", "[LoanID] = [Loans].[LoanID]") > 0 Then
'MsgBox (DCount("[EquipmentID]", "[LoanDetailsKits]", "[LoanID] = [Loans].[LoanID]"))
[DeployableKitsLoan].SetFocus
Else
[LoanDetails].SetFocus
End If

Thanks for the help Dhookom!

 

[dhookom]

I would have expected the code to be the following assuming LoanID is numeric.

If DCount("[EquipmentID]", "[LoanDetailsKits]", "[LoanID] = " & [LoanID]) > 0 Then
 'MsgBox (DCount("[EquipmentID]", "[LoanDetailsKits]", "[LoanID] = [Loans].[LoanID]"))
    [DeployableKitsLoan].SetFocus
 Else
    [LoanDetails].SetFocus
End If

Duane
Hook'D on Access
MS Access MVP