Is there a way to load individual text boxes with coded SQL statements Please?

[ZaphodBBB]

I have a form that is used just to review previously saved records.(1 record at a time).

It also allows just a couple of fields to be updatable (in a given record)and resaved.

I load all the text boxes in this this form simply by creating a recordset in the forms Load event procedure.
It then populates the fields using... say [txtModelID = rs!ModelID].
This works fine.

But in the table that the recordsourse draws from there is only a customerID field and no CustomerFirstName or CustomerLastName fields; these are in the Customers Table which has matching CustomerID numbers.

what I would like to know is once the recordsource has been created how can I write an SQL statement in the Form Load event procedure the will do something like ..
SELECT CustomerName From tblCustomers Where tblCustomers.CustomerID = rs!CustomerID
then have that sql statement load the relevant customers name into the txtCustomer textbox on the form. Same with a few other fields that are on the form but not in the table that the Recordset draws from.

I do not want to use a stored query (with criteria) to populate the form...I tried that and although it worked fine to populate all the required fields, if the user modified the three fields that are updatable, then clicked the SAVE button (which would create a recordset and only write those 3 fields back into the table) , every time you did go to do a SAVE a message would come up saying something like ... "Another user has modified the record, do you wish to Write, Drop or Save your changes to the clipboard" which is untrue as there is only one user on any given PC. So I would like to avoid that if at all possible. Even if it takes 3 or 4 SQL statements to get the relevant text boxes loaded.

Many thanks again for all the help.

[NeoPa]

Zaphod,
It seems like you're trying to work around Access rather than using it to do your work for you.
A form with a single record is handled ok in Access but doesn't need a special command button to trigger the save. Any movement off the current record will do that automatically (Obviously if you wanted to restrict it to a command button then you could avoid showing the record navigation controls and have some code in the button to move off then back on to the record).
If you wanted to restrict the fields that were updatable then locking them would do that for you.
In short, I very strongly recommend working with Access rather than around it.

[MMcCarthy]

I have a form that is used just to review previously saved records.(1 record at a time).

It also allows just a couple of fields to be updatable (in a given record)and resaved.

I load all the text boxes in this this form simply by creating a recordset in the forms Load event procedure.
It then populates the fields using... say [txtModelID = rs!ModelID].
This works fine.

But in the table that the recordsourse draws from there is only a customerID field and no CustomerFirstName or CustomerLastName fields; these are in the Customers Table which has matching CustomerID numbers.

what I would like to know is once the recordsource has been created how can I write an SQL statement in the Form Load event procedure the will do something like ..
SELECT CustomerName From tblCustomers Where tblCustomers.CustomerID = rs!CustomerID
then have that sql statement load the relevant customers name into the txtCustomer textbox on the form. Same with a few other fields that are on the form but not in the table that the Recordset draws from.

I would suggest creating a second recordset. Make a select query in that recordset that would return all the field values you need including the CustomerID.

Once the relevant CustomerID is reached by the original recordset you could match it using something like ...

rs2.FindFirst "[CutomerID]=" & rs1!CustomerID

You can use an unbound form and make the relevant textboxes equal to the resulting record like

Me.txtCustID = rs2!CustomerID

Mary

[ZaphodBBB]

Hi and thanks again for the replies

Yes you are right NeoPa I probably am working around access , unfortumatly the only time I know it is when i get advised by some one here like yourself!

Thanks Mary I never knew you could open two recordsets at the same time..I'll give it a try and see how I go thanks.

Mike

[ZaphodBBB]

Hi Mary

Just tried as you suggested and it worked well!!

Used:
strSQL = "SELECT CompanyName,Department,ContactFirstName,ContactLas tName " & _
"FROM tblCustomers Where tblCustomers.CustomerID =" & rs!CustomerID

Then
Set rs2 = db.OpenRecordset(strSQL, dbOpenDynaset)

After first having dimmed the strSQL and the two Recordsets.

So yet again, thanks to you and NeoPa for all the assistance given!

[MMcCarthy]

Hi Mary

Just tried as you suggested and it worked well!!

Used:
strSQL = "SELECT CompanyName,Department,ContactFirstName,ContactLas tName " & _
"FROM tblCustomers Where tblCustomers.CustomerID =" & rs!CustomerID

Then
Set rs2 = db.OpenRecordset(strSQL, dbOpenDynaset)

After first having dimmed the strSQL and the two Recordsets.

So yet again, thanks to you and NeoPa for all the assistance given!

That's great.

Glad it worked out for you.

Mary