Vba to make the textbox show record value

[pedie]

Hi, i want to know how to make Vba show record value in textbox1 in myform1.

when the form load, if the environ username is pedie1 then i want "pedie1" table's record1 of field1 show in textbox1.

Please please help me out.

Thanks in advance.

 

[xenou]

Is Pedie1 a value in a field also?

I think you could add your vba to the Form's Initialize Event to alter the form's record source dynamically:

Dim s As String
s = Environ("UserName")
s = "SELECT X FROM Y WHERE Z = '" & s & "';"
Me.RecordSource = s

 

[Norie]

pedie

Do you have a table for each user?

 

[xenou]

Correction - access forms have no intialize event. I mean the Load event.

 

[pedie]

Xen, thanks for the code: I tried it this way and it errors so i excluded underlined line and now it does not error but it does not show anything as textbox1.value
'm i suppose to change anything in the code?

[/FONT]
[FONT=Courier New]Private Sub Form_Load()
Dim s As String
s = Environ("UserName")
s = "SELECT X FROM Y WHERE Z = '" & s & "';"
[U]'Me.RecordSource = s[/U]
End Sub

Norie, yes i do have table for each user.
Thanks again.

 

[pedie]

I also did try this way...but i have no idea how to make this things work...
I have reviewed so many web pages but to no luck...

[FONT=Courier New]dim x as string[/FONT]
[FONT=Courier New]x = environ("Username")[/FONT]
 
[FONT=Courier New]Set rs = CurrentDb.OpenRecordset("SELECT " & x & ".Name FROM & " x & ";")[/FONT]
[FONT=Courier New]Me.Text2.Value = x!rs(0).Value[/FONT]

 

[xenou]

s = "SELECT X FROM Y WHERE Z = '" & s & "';"

What's the correct SQL to get the data you need? Probably something in the sql query is wrong. Of course your form also needs to have controls that match the fields returned.

 

[pedie]

Please tell me what should i exactly do...
my form name is "Myform1"
it want table "Pedie", 1st column that is "Names" 1st row value in Form textbox named "Textbox2".

I thought it should be simple like...
me.textbox2.value = Pedie!names(1) something like that

Thanks again...Please advice.

 

[Norie]

Set the Record Source property of the form to pedie1.

You should now see a list of available fields.

Drag the field you want from that list onto the form.

That should create a textbox bound to that field.

When you open the form you should see the value of that field for the first record in the textbox.

That's it - no code needed.

 

[pedie]

Norie, thanks, thanks alot..that was a learning...but what if i want textbox value to be "table2" name value when someone else opens the form then..
How do i take care of that

Also please show me the vba way too..
I tried this way and it show Myform1 in texbox value

[/FONT]
[FONT=Courier New]Private Sub Form_Load()
Dim s As String
s = Environ("UserName")
Me.RecordSource = s
Me.Text2.Value = Name
End Sub