First record in table to appear in unbound text field on Form? (1 Viewer)

[Robert88]

Hi All,

I hope someone can help me.

I have a table here tblTemp with a field client. This table can have from 1 to 100 records at times as it keeps getting cleared for the purpose of this exercise.

I also have a form frmLabel1Data with an unbound text box txtclient.

I was hoping to get the first record of the table into this text box upon opening the form.

I tried creating a query which gave me the following sql;

SELECT First(tblTemp.client) AS FirstOfclient FROM tblTemp;


I also tried placing it in this code with no luck

Private Sub Form_Open(Cancel As Integer)

    Dim SQL
    
    SQL = "SELECT First(tblTemp.client) AS FirstOfclient FROM tblTemp;"
    MsgBox SQL
    
End Sub

Does anyone know why I cannot see the same result as when I open the query it gives me my client name but the code above does not????

Any help is appreciated.

Robert88

 

[Pat Hartman]

Just creating an SQL string doesn't do anything. You need to open a recordset based on the SQL string and read the first record.

Why isn't your form bound? It would automatically open to the first record in the recordset.

You could use DFirst() in the Open event of the form.

Private Sub Form_Open(Cancel As Integer)

    Me.txtclient = DFirst("client","tblTemp")
    
End Sub

 

[Robert88]

Thank you

Hi Pat,

Thanks Pat.

I still need to do more work on SQL it appears, I am only a newby to it. Been playing around with Access2, 97, 2000 and now with 2003 over the years. Now looking to expand with VBA incorporating SQL. I think I have a lot of learning to do. This site is fantastic for help.

The reason for it not being bound is that I am trying to draw many different fields from different tables in order to make-up a CD label on company info and data in relation to the client. The CD's have a standard format with Fox Pro tables on board, amongst other info that I have managed to attain from them and place into table within my mdb using VBA.

http://www.access-programmers.co.uk/forums/showthread.php?t=102185&highlight=fox+pro

Sometimes the CD's are not made correctly, more so structure and as such if I make it when the form opens that it is not complete the user will know that something is wrong and then allow them to add with unbound so that the info can be stored in another table as the labeller makes up 6 CD's on a Avery J8166 A4 page.

This has got me thinking that I should include a routine to check the CD before starting but that can come later as there is already a lot different types of CD's I am considering with this and I hope this approach overcomes the majority of my problems. Sometimes the table I am extracting the info from is not even present, this is not good.

I suppose it is the old saying of "Information in = Information out"!!!!

Thanks for your help and keep up the good work, I appreciate it!

Robert88