need help!! - running sql statements based on form inputs

[Gaby Sandoval]

hi everyone,
I really need help. Trying to get this simple form to work for a
meeting tomorrow.

I have 2 tables with data -- company, roles. each table has the name
and a corresponding code

I have form that ask for your name, what company you are from (list) ,
and your role (also a list selection) at the meeting.

I then want generate a unique number for that participant. the unique
number is 7 digits (xxyyzzz)
xx= role code, yy=company code, zzz=sequential number (based on the
role)

i built the form. it has two buttons that both need to run some sql
to accomplish my task.

here is my vb code (i commented out things i am not sure how to get
working). i think by reading it you can see what i am trying to
accomplish, but cant get to work. please help. i appreciate any
advice....
-----
Dim OrgName As String
Dim RoleName As String
Dim UniqueId As String
Dim LastName As String
Dim FirstName As String
Dim TotalNumber As String

Private Sub btnGenerate_Click()

UserRoleNum = lstRole.Value
UserOrgNum = lstOrg.Value


' SELECT Count(ParticipantRole) AS [TotalNumber] FROM Roster WHERE
((Roster.participantrole)=[UserRoleNum]);"

TotalNumber = TotalNumber + 1

' Total number needs to be formated as three digits - Ex: 001, 002,

Me.txtUnique = UserOrgNum & UserRoleNum & TotalNumber


UniqueId = txtUnique.Value

End Sub


Private Sub btnCreate_Click()

' LastName = txtLastName.Value
' FirstName = txtFirstName.Value

'SELECT organization.comp AS [OrgName] , organization.number AS
[OrgNum]FROM organization WHERE organization.number = [UserOrgNum];
' SELECT roles.comp AS [RoleName] , roles.number AS [RoleNum]FROM
roles WHERE roles.number = [UserRoleNum];

'INSERT INTO roster (lastname, firstname, organization,
organizationname, participantrole, participantrolename, uniqueID)
VALUES ([LastName], [FirstName], [OrgNum], [OrgName], [RoleNum],
[RoleName], [UniqueID])

End Sub

 

[Dorian]

Which part is not working?
I can see a couple of problems.
If this is a multi-user system your technique might generate duplicate
numbers.
You probably also need this:
Me.txtUnique = UserOrgNum & UserRoleNum & Right$("00" & CStr(TotalNumber),3)

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Gaby Sandoval]

The part i cant get to work is all the SQL statements that i have
commented.

What is the correct way to run a select and insert statement in the
code?
I want to pull values from the select statements, use them, and output
some values back into another (roster) table.

Is refrencing the values in [ ] the correct way or does that syxtax
need to be corrected.

 

[Gaby Sandoval]

And to followup your other point, this form will only be used by one
user - so there is no worries about duplicate values being generated
if more than one person is writing to the database. Thanks.

 

[KARL DEWEY]

Try this --
INSERT INTO roster (lastname, firstname, organization,
organizationname, participantrole, participantrolename, uniqueID)
SELECT TOP ! [LastName], [FirstName], [OrgNum], [OrgName], [RoleNum],
[RoleName], (SELECT Max([UniqueID]) +1 FROM [Roster] WHERE [RoleNum] =
participantrole) AS Expr1
FROM [Roster];

 

[Gaby Sandoval]

Great,
Thanks.

The thing that still confuses me is how to execute the sql statement.
I want to run these statements after the button click event. I cant
just paste in the SQL code. I get an error.

What do i need to do to execute the statement. Do I need to call some
sort of function? That is what I am not sure of.

Thanks for all the help so far.

 

[KARL DEWEY]

I did not text what I posted but I would paste the SQL in a query and save it.
Then create a macro to call the query.
Next for the event property of the button call the macro.

 

[KARL DEWEY]

I have a typo - reads - SELECT TOP ! [LastName], - to read --
SELECT TOP 1 [LastName],

 

[Dale Fye]

Another way, besides the macro is to just call the Execute method.

Private sub SomeButton_Click

On Error GoTo ProcError
currentdb.execute "qry_yourQueryName", dbFailOnError

Exit Sub
ProcError:
debug.print err.number, err.description
debug.print currentdb.querydefs("qry_yourQueryName").SQL
msgbox "Check immediate window for error message and SQL string.

End Sub

If the query is a parameter query, you can use:

Private sub SomeButton_Click

Dim qdf as dao.querydef

On Error GoTo ProcError
Set qdf = currentdb.querydefs("qry_yourQueryName")
qdf.Parameters(0) = me.txt_SomeField
qdf.execute dbFailOnError


ProcExit:
set qdf = nothing
Exit Sub
ProcError:
debug.print err.number, err.description
debug.print currentdb.querydefs("qry_yourQueryName").SQL
msgbox "Check immediate window for error message and SQL string.
Resume ProcExit
End Sub

HTH
Dale