VBA Code for Ticking Checkbox if Field Entry is Found in Another Table (1 Viewer)

[BillieJ]

Hi,

I was wondering if anyone could help me out…up until 3 weeks ago I had never created a database on Access so I am VERY new to this but now I find myself writing queries, macros and even some VBA code! I am a complete novice so I apologise if this is ridiculously easy but I just can’t work it out from MS Access 2010 help and Microsoft online training and I have no one I know that could help.

I have created a Supplier Cost Database, within this I have a Table (Costs) and a Form (Cost Database) based on the Costs table. On the table and form there is a free type field called Second Tier Supplier Name and a Yes/No Checkbox called Corporate Rate.
I have another table in the database called CorporateRates.

I am trying to write the VBA code so that when a Supplier name has been entered in the Second Tier Supplier Name field the database will automatically check if this name is listed in the Corporate Rate Table (field would be Hotel) and auto ticks the check box if it is.

I am completely stumped and would be eternally grateful for anyone that can give me any pointers!

This is what I have so far….please don’t laugh at my pathetic attempt I did say I was very new to all this !

Private Sub Second_Tier_Supplier_Name_AfterUpdate()

Corporate_Rate = "1"
"WHERE Second_Tier_Supplier_Name = '" & tblCorporateRates Hotel & "' " & _


Many thanks!

 

[gblack]

I'll give it a shot... sorry if I miss something (I am sure the big Brains will catch it if I do).

In your form (design mode) right click your "Second Tier Supplier Name" and choose properties.

In the "On Change" area of the properties section, click the drop down arrow to the right and choose [Event Procedure] then click the elipse (i.e. "...") button to the right of the dropdown.

Now you should be in the VBA area. You'll wanna put your code where the cursor is blinking. Also make sure you have the DAO reference: Go to Tools>>references and choose: "Microsoft DAO 3.6 Object Library"

Cut and paste the code below and tweak it (based on the comments in green) to get what you're looking for.

Dim db as Database 

Set db = currentDb
Set rs = db.OpenRecordset("CorporateRates", dbOpenTable)

IF rs.recordcount > 0 Then

   Do while not rs.EOF
       'Put the actual field name from CorporateRates table inside the double quotes 
       'and the field name from the form inside the brackets.	
       If rs.Fields("Second Tier Supplier Name") = Me.[Second Teir Name]  Then 
	    Me.[Corporate Rate] = True   'This will check the checkbox when there's a hit
       End if

   Loop

End if

I'm not sure if that will run... but it'll be something close to that... maybe others can help you further...

GL,
Gary

 

[SOS]

I'll give it a shot... sorry if I miss something (I am sure the big Brains will catch it if I do).

In your form (design mode) right click your "Second Tier Supplier Name" and choose properties.

In the "On Change" area of the properties section, click the drop down arrow to the right and choose [Event Procedure] then click the elipse (i.e. "...") button to the right of the dropdown.

Now you should be in the VBA area. You'll wanna put your code where the cursor is blinking. Also make sure you have the DAO reference: Go to Tools>>references and choose: "Microsoft DAO 3.6 Object Library"

Cut and paste the code below and tweak it (based on the comments in green) to get what you're looking for.

Dim db as Database 
 
Set db = currentDb
Set rs = db.OpenRecordset("CorporateRates", dbOpenTable)
 
IF rs.recordcount > 0 Then
 
   Do while not rs.EOF
       'Put the actual field name from CorporateRates table inside the double quotes 
       'and the field name from the form inside the brackets.    
       If rs.Fields("Second Tier Supplier Name") = Me.[Second Teir Name]  Then 
        Me.[Corporate Rate] = True   'This will check the checkbox when there's a hit
       End if
 
   Loop
 
End if

I'm not sure if that will run... but it'll be something close to that... maybe others can help you further...

GL,
Gary

Gary - looks like you are missing a slight bit of code to move next.

rs.MoveNext

otherwise it will just hang there on the one record and be in an infinite loop since it will never get to the EOF.

 

[gblack]

UGh... yeah Thanks, I was trying to take out some comments as I was writing...in order to cut it down to the bare minimum...but I cut out any movement through the recordset... Yikes! Maybe this'll work... maybe not... Writing the code without the database is kind of like trying to play a game of chess in your head... I suppose some folks are great at it, I'm obviously not.

Dim db As Database
 
Set db = CurrentDb
Set rs = db.OpenRecordset("CorporateRates", dbOpenTable)
 
If rs.RecordCount > 0 Then
   rs.MoveFirst
   Do While Not rs.EOF
       'Put the actual field name from CorporateRates table inside the double quotes
       'and the field name from the form inside the brackets.
       If rs.Fields("Second_Tier_Supplier_Name") = Me.[Second_Tier_Supplier_Name] Then
        Me.[Corporate Rate] = True   'This will check the checkbox when there's a hit
       End If
       rs.MoveNext
   Loop
 
End If

GL

 

[gblack]

After rereading this and looking at his Query... I think this version might be better...

Dim db As Database
 
Set db = CurrentDb
Set rs = db.OpenRecordset("Corporate_Rate", dbOpenTable)
 
If rs.RecordCount > 0 Then
   rs.MoveFirst
   Do While Not rs.EOF
       If rs.Fields("Second_Tier_Supplier_Name") = Me.[Second_Tier_Supplier_Name] Then
        rs.Edit
        rs.Fields("Hotel") = True
        rs.Update
       End If
       rs.MoveNext
   Loop
 
End If

 

[BillieJ]

Thank you both so much...it works!

I was way off the mark...hope no one minded me asking for help, reading this forum has helped me so much as a complete newbie to Access.

Thanks again!