Global Variable

[Samt]

So heres what i have so far. I am trying to make a form in which new staff members can enter in their data (emergency cotnact info, ect.) in a form that acts as a program.

The first tab has a place where htey enter thier first name and last name into 2 text boxes and then saves it into a staff table. there are numbers assigned to each staff in that table which are called Staff ID.

The way this form is set up is that a new staff can enter in thier information without having to enter in thier first and last name for each page.

I think i need to assign a global variable, but i am not sure how.

Here is what i have on the general part of the vba code

Option Compare Database
Dim PermanentStaffID As Integer
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT [staffid] From [Staff Table]WHERE [First]= '" & Text5.Value & "', [Last] = '" & Text7.Value & "'")
rs.MoveFirst
PermanentStaffID = db.Staff.Column(0)

If anyone can help me figure this out it would be great!
Thanks
-Sam

 

[pbaldy]

Your SQL is invalid. Try

Set rs = db.OpenRecordset("SELECT [staffid] From [Staff Table] WHERE [First]= '" & Text5.Value & "' AND [Last] = '" & Text7.Value & "'")

And the last line would be:

PermanentStaffID = rs!staffid

 

[Samt]

Still no luck. could you write the whole code out for me? here is what my code looks with your edits.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim PermanentStaffID As Integer
Dim strSQL As String

strSQL = "SELECT [staffid] From [Staff Table]WHERE [First]= '" & Text5.Value & "', [Last] = '" & Text7.Value & "'"

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT [staffid] FROM [Staff Table]WHERE [First]= '" & Text5.Value & "', [Last] = '" & Text7.Value & "'")
rs.MoveFirst
PermanentStaffID = rs!staffid

 

[pbaldy]

Not sure what you mean by "with your edits", as the SQL is unchanged. You still don't have a space before the word "WHERE", and you still use a comma to separate the fields in the WHERE clause instead of "AND", which is what I used. Also, you create the strSQL variable, and then don't use it.

 

[Samt]

my bad. im still not understanding. its comes up with an error every time, "invalid outside procedure". i added a space before "Where" and changed the comma to an "AND". still no luck. is it in the right order? or am i completely working backwards.

Basically what i am trying to do is keep the assigned staff id in the first table to transfer to all the other tables linked to the form.

so the first page the new staff enters: First Name, Last name, and Job Title. the table is on auto number so it will assign the staff ID automatically.

The next page the new employee enters the emergency contact info: primary contact name, Primary contact number, Cell phone, ect.

when they enter this info, the assigned staff Id from the first tables auto number should be inserted as the staff id for the emergency contact info table

 

[pbaldy]

Ah, just noticed you have that right under Option Compare Database. It needs to be within a procedure of some sort. Access is very event driven, so we need to find the event appropriate to run this code. It could be the click event of a button, or perhaps the after update event of the second textbox they would fill out, or whatever. You will have a better idea of that than I will, as you know the process a user will follow. In the end, it will look like:

Option Compare Database

Private Sub ...
your code here
End Sub

 

[Samt]

still no luck, thanks for the help though. i need to make an assigned variable the same accross each of the tables

 

[pbaldy]

Presuming you have a form and subforms, master/child links on that ID field will automatically populate the subforms with the ID when you add a record.