SharePoint linked Access Database form

[dilipramadas]

Hi Guys,

I'm working on a piece of automation which is based on Access DB form which is linked to SharePoint.

I've created a form with multiple combo boxes and couple of text boxes. After googling a LOT, I was able to automate the event procedure for one of the combo box. But when i tried replicating the same for another combo box, it doesn't work. I've changed the declarations and I've also looked into the tables the
I'm new to Access and tried writing the macro based on the excel VBA I written in past.
Here is what I'm trying to achieve: When the form user selects one option from the 1st combo box, in the 2nd combo box it show a list which corresponds to the selection in 1st combo box.

Below is the code.

Option Compare Database


Public Function GetDivisionPrivate Sub cboFunction_Change()
Call GetDeptList(cboFunction.SelText)
End Sub
 
Public Function GetDeptList(sFunctionName As String) As Boolean
Dim db As DAO.Database
Dim Trs As DAO.Recordset
Dim FtrSQL As String
 
On Error GoTo GetTaskName_Error
 
GetTaskName = False
 
Set db = CurrentDb
 
FtrSQL = "SELECT tb_tasks.[Tasks] ,tb_tasks.[ID] " & _
         "FROM tb_function INNER JOIN tb_tasks ON tb_function.ID = tb_tasks.Task_ID " & _
         "Where tb_tasks.[Tasks]='" & sFunctionName & "'"
                 
    Set Trs = db.OpenRecordset(FtrSQL, dbOpenSnapshot)
Trs.MoveFirst
 
Me!cboTask = Null
 
If Not Trs.EOF Then
    Me!cboTask.RowSource = FtrSQL
End If
 
Trs.Close
db.Close
 
GetTaskName = True
 
GetTaskName_Error:
    'MsgBox Err.Description
 
 
End

Any help on this is appreciated!

Best regards,
Dilip

 

[xenou]

Not something I've done in a while but you are setting a rowsource to a sql statement. So all the stuff about opening the recordset is irrelevant (it's not doing anything). Also weird that you are closing the database in this sub (db.Close). If setting the rowsource to a sql statement is correct, then you probably need to refresh the combobox control after doing that.

I also follow this method:
Access Tips: Cascading Lists for Access Forms

you can also google for examples using keywords "msaccess dependent combobox" or "msaccess cascading combobox"

 

[dilipramadas]

Not something I've done in a while but you are setting a rowsource to a sql statement. So all the stuff about opening the recordset is irrelevant (it's not doing anything). Also weird that you are closing the database in this sub (db.Close). If setting the rowsource to a sql statement is correct, then you probably need to refresh the combobox control after doing that.

I also follow this method:
Access Tips: Cascading Lists for Access Forms

you can also google for examples using keywords "msaccess dependent combobox" or "msaccess cascading combobox"

Hi,

Thank you for responding.

This was the first time i was looking into access automation. Since this database is linked to SharePoint, fields are bound to the columns. And the cascading combo is something I have looked into and will be referring to the link.

I just saw an example and tried to replicate it. Still learning

Best regards,
Dilip

 

[dilipramadas]

Hi Again!
I'm in need of some help with the same form. Below is the code where i'm using a textbox:

Public Function GetPodEmail(sPyramidName As String) As Boolean
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String


On Error GoTo GetPodEmail_Error


GetPodEmail = False




Set db = CurrentDb


strSQL = "SELECT tb_podemail.[Email] " & _
         "FROM tb_Pyramid INNER JOIN tb_podemail ON tb_Pyramid.Id = tb_podemail.ID " & _
         "Where tb_Pyramid.Division='" & sPyramidName & "'"
    
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
rs.MoveFirst


Me!txtEmail = Null




If Not rs.EOF Then
Me!txtEmail.RowSource = strSQL
'txtEmail.Value = strSQL
End If


rs.Close
db.Close


GetPodEmail = True




GetPodEmail_Error:
    'MsgBox Err.Description




End Function

What is the property which I need to use? I've used .text, .rowsource, .Value and also without any properties. But the data didn't populate in the text box. xenou, can you guide me here? I've even googled several times, .column was mentioned in some post, I tried that too. What is the logic to be used here?

 

[xenou]

Does your query here return one value or more than one value?

 

[dilipramadas]

Does your query here return one value or more than one value?

No sir it does not.

 

[dilipramadas]

it returns only one value. Additionally, could you please help me with below code? I am unable to clear field for attachments. Below is my code. Can you please tell me what I'm doing wrong?

Function ClearAll()'Stop


Me.cboPyramid = ""
'Me.txtEmail = ""
Me.CboDivision = ""
Me.cboFunction = ""
Me.CboSubTask = ""
Me.cboTask = ""
Me.txtNotes = ""
Me.cboTIP = ""
Me.txtPdesc = ""
Me.Text48 = ""
Me.Text10 = ""
Me.cboReason = ""
[U][I][B]Me.Attachment87 = ""[/B][/I][/U]


End Function

 

[xenou]

Try setting it to Null, instead. Not sure what's in that field - is it supposed to be a string value (address of an attachment) or an actual attachment (binary file).

 

[dilipramadas]

Hi Xenou, I've set it to null and nothing but no changes there.
The issue seems to be that it doesn't move to the next line in the db. and it gives me an error.

 

[xenou]

Make sure your spelling is right - it's very common people just don't have the right names for controls on forms. I don't know what you mean by move to next line in DB - what's supposed to move? Move to what, in where?