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 CustomerFirstNa me or CustomerLastNam e 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.Cu stomerID = 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.
5 1455 NeoPa 32,579
Recognized Expert Moderator MVP
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 14,534
Recognized Expert Moderator MVP
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 CustomerFirstNa me or CustomerLastNam e 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.Cu stomerID = 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
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
Hi Mary
Just tried as you suggested and it worked well!!
Used:
strSQL = "SELECT CompanyName,Dep artment,Contact FirstName,Conta ctLastName " & _
"FROM tblCustomers Where tblCustomers.Cu stomerID =" & rs!CustomerID
Then
Set rs2 = db.OpenRecordse t(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 14,534
Recognized Expert Moderator MVP
Hi Mary
Just tried as you suggested and it worked well!!
Used:
strSQL = "SELECT CompanyName,Dep artment,Contact FirstName,Conta ctLastName " & _
"FROM tblCustomers Where tblCustomers.Cu stomerID =" & rs!CustomerID
Then
Set rs2 = db.OpenRecordse t(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
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: dmiller23462 |
last post by:
Hi guys....I have absolutely NO IDEA what I'm doing with Javascript
but my end result is I need two text boxes to stay hidden until a
particular option is selected....I've cobbled together the JavaScript
in this code from a couple different sites but I'm not 100% sure what
each line is doing...This is the ASP code that I'm using for the
page....Take a look at the JavaScript code and please let me know what
each line is doing....I have been...
|
by: Enterprise |
last post by:
Hi,
I have 25 text boxes in a form. Lets say TB1 through TB25 are the Names of
the boxes. They have no controll sources. I want to be able to check whether
the text box is empty. Is there anyway I can do this in a loop? The only way
I can see to do this is to go if isempty(TB1) then...endif if
isempty(TB2)then...endif which will involve 25 if statements. I was kind of
hoping to do this in a for loop if possible.
Thanks
P.S. Using...
|
by: ckpoll2 |
last post by:
Hello,
I have a report and am having a hard time summing some numbers. In the detail section of the report, I have 2 text boxes that have IIF statements in them. These IIF statements return numbers, either a positive number or 0. In the Name Footer, I am trying to sum all the occurrances of Text18 and Text19, the two IIF text boxes in the detail. I type =sum(), but when I run the report, I am first prompted with a pop-up text box with the...
|
by: shank |
last post by:
I posted this in an HTML forum, but I think it's going to take some
scripting to act as I want to act.
I have a form with multiple records.
My intention is to have the user click a checkbox for each product he wants.
Then submit the form placing multiple items in his cart.
If I use a text box like the following and enter a 1 for each product,
submit, no problem.
The user gets the products he wants.
<input name="qty" type="text"...
|
by: Gerhard |
last post by:
Hi, all
I run into the same problem on Access 2000 and 2003.
Hopefully someone can replicate it – or not.
1. Create an unbound form – call it Form1.
2. Insert two unbound text boxes – call it Text0 and Text2.
| |
by: rn5a |
last post by:
Can someone please suggest me a text editor especially for DEBUGGING
ASP scripts apart from Microsoft Visual Interdev?
I tried using Visual Interdev & created a project but Interdev
generates some error related to FrontPage extensions. I couldn't
exactly understand the error. I tried to create the project in C:
\Inetpub\wwwroot. If I just open a ASP file (by navigating to the
File-->Open File... menu), then Interdev doesn't give the...
|
by: sara |
last post by:
Hi -
I have been struggling with solution ideas for this now for almost 2
weeks, and have not been able to figure this out.
I have a user who creates a Purchase Order (tblPOData). In some
circumstances, this or another user must create an invoice to go with
the PO (I know - that makes no sense, but this is the business
case).
I have the user go to a form to create the invoice:
|
by: Dennis |
last post by:
I've noticed some old posts regarding this issue; but nothing recently
and no resolutions in the old postings.
Its almost hard for me to believe that this wouldn't be fixed by now.
I am using Access 2007.
The Forms Load event does not fire when the form is displayed. It
does fire when the next button "On Click" event occurs. I have 5 or 6
buttons, and it doesn't matter which one I press. The actual action
|
by: RICHARD BROMBERG |
last post by:
Please bear in mind that I am a newbie.
I am posting this question a second time because the responses to my earlier
post were a little wide of the mark.
So, here goes:
I am using Access 2000 and I would like to make a data entry form
(frmDataEntry) with a two text boxes.
The table (tblPeople) has fields Name and Age. Both are character fields.
The Form has two text boxes (txtName and txtAge)
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |