CODEWITHSUNDEEP
ms-accessvba
Mike
Mike

Reputation: 1938

How to pull data from a row in Access with VBA

This might be a stupid question, but I'm still new to Access VBA. I have a form that has a lot of fields on it. I have just created a subform that is pulling data from another table, but I need it to base its pull on data in the main form.

So the main form has Project Number, Project Type, and GroupID and I want to use that combination to pull the Permit from a secondary table into the subform.

The Primary table is called PMDBII and the Secondary table is called Project_Permit.

Forms currently looks like this:

Project form

So the Project that is selected it shows the Permit that is associated with it as highlighted.

What I want to do is have the list on the left be a list of all Permits that are not associated with the Project and the list on the right be all Permits that are associated with the Project.

So that it would look something like this:

+----------------+--------+
| Federal        | Tribal |
| Power Easement |        |
| Railroad       |        |
| State          |        |
| Township       |        |
+----------------+--------+

Then I could select another Permit on the left and click the > button to move it to the right.

The problem is that I don't know how to get the correct data entered into the Project_Permit table. I know that I'll need to enter the Project number, Project Type, GroupID and Permit. I can get the Permit it's the rest that I'm having trouble with.

This is what I've got so far:

Private Sub cmd_Select_Click()
    Dim SQL As String
    Dim ProjectNum As String
    Dim PermitNum As String
    Dim GROUPID As String
    Dim ProjectType As String

    With Me.PERMIT
        PermitNum = .Value
    End With


    SQL = "INSERT INTO pmdb.Project_Permit(Project,Permit,ProjectType,GroupID)" _
           & " Select '" & ProjectNum & "', " & PermitNum & ", " & ProjectType & ", '" & GROUPID & "';"

    CurrentDb.Execute SQL

    Forms![DATASHEET - CAF2].Form!PERMIT.Requery
    Forms![DATASHEET - CAF2].Form!Selected.Requery

End Sub

This is on the main form. I don't know how to use this to get the rest of the data that I need to insert the new record.

Upvotes: 1

Views: 1641

Answers (1)

Parfait
Parfait

Reputation: 107577

Simply reference the main forms controls in your append query called from subform. Below also shows how to parameterize the query using querydefs either as a VBA string or saved Access query approach. Of course adjust form and/or control names and param data types.

VBA String Query Approach

...
Dim qdef as QueryDef

' PREPARE STATEMENT (DEFINES PARAMS AND ADDS THEM AS PLACEHOLDERS)
SQL = "PARAMETERS [ProjectNumParam] TEXT(255), [PermitNumParam] LONG," _
       & "        [ProjectType] TEXT(255), [GROUPIDParam] LONG;" _
       & " INSERT INTO Project_Permit(Project, Permit," _ 
       & "                            ProjectType, GroupID)" _
       & " VALUES ([ProjectNumParam], [PermitNumParam], " _
       & "         [ProjectTypeParam], [GROUPIDParam]);"

' INITIALIZE QUERY OBJECT
Set qdef = CurrentDb.CreateQueryDef("", SQL)

' BIND VALUES TO PARAMS
qdef![ProjectNumParam] = Forms!mymainform!ProjectNum
qdef![PermitNumParam] = Forms!mysubform!PermitNum
qdef![ProjectTypeParam] = Forms!mymainform!ProjectType
qdef![GROUPIDParam] = Forms!mymainform!GROUPID

' EXECUTE ACTION  
qdef.Execute dbfailOnError

Set qdef = NOthing

Saved Query Approach

SQL (save as Access stored query, PARAMETERS is valid in Access SQL dialect)

PARAMETERS [ProjectNumParam] TEXT(255), [PermitNumParam] LONG,
           [ProjectType] TEXT(255), [GROUPIDParam] LONG;
INSERT INTO Project_Permit(Project, Permit, ProjectType, GroupID)
VALUES ([ProjectNumParam], [PermitNumParam], [ProjectTypeParam], [GROUPIDParam])

VBA (reference above query by name)

' INITIALIZE QUERY OBJECT
Set qdef = CurrentDb.QueryDefs("mySavedQuery")

' BIND VALUES TO PARAMS
qdef![ProjectNumParam] = Forms!mymainform!ProjectNum
qdef![PermitNumParam] = Forms!mysubform!PermitNum
qdef![ProjectTypeParam] = Forms!mymainform!ProjectType
qdef![GROUPIDParam] = Forms!mymainform!GROUPID

' EXECUTE ACTION  
qdef.Execute dbfailOnError

Set qdef = Nothing

Upvotes: 2

Related Questions