form creating new row in table1, looking for field value in table

Please can someone help as I have been battling all week with this problem, 
and haven't had a response.

I have a form which uses some data from table1 to create an entry in table2 
(i know duplication is bad, but as this is a live database, and i am working 
on a new section, this makes this complicated task easier), which works fine. 
however, i have a third table that has the unique ID from table1 and table2 
in order to show when a table1 row of data is needed in table2 via a 
checkbox. This third table3 is created when elsewhere.

The problem lies with bringing in the unique ID value from table2 and puting 
it in the same row as the unique ID value from table 1, into table 3. eg:

table1
UID = 12345

table2
UID = 6789

table3
UID = 1
tbl1-UDI = 12345
tbl2-UDI =

the code i use when opening the form is as follows: (text126 pulls the value 
of UID1)

Dim badger As String
Dim LinkID As Long

badger = Me.NameData & ";" & Me.dteStartDate & ";" & Me.dteEndDate & ";" & 
Me.Text126

DoEvents
LinkID = DLookup("UID2", "table3", "[UID1]=" & Me.Text126)

On Error GoTo ErrorHandle
    DoCmd.OpenForm "frm_TRA_AddBooking", , , "[NameData ]=" & Me![NameData], 
acFormAdd, , badger 

I am guessing it is something to do with the form creating an autonumber for 
table2, and not being able to use that value until after the form is closed, 
however I have tried passing over the value to a second form, and that does 
not pass it over either.(text54 calls the value of UID2 which pulls from the 
table)

Dim DayDataInfo As String

DayDataInfo = Forms!frm_TRA_AddBooking!UID2;" & 
Forms!frm_TRA_AddBooking!UID1;" & Forms!frm_TRA_AddBooking!Text54

DoCmd.OpenForm "frm_TRA_DayData"

I would be very grateful if someone would help me with this (providing I 
have explained it clearly enough)
0
Utf
5/25/2007 8:57:01 AM
access.formscoding 7493 articles. 0 followers. Follow

1 Replies
666 Views

Similar Articles

[PageSpeed] 9

Don't worry, I fixed it via some tweaking elsewhere, and by passing the 
openargs over in the correct bit.

"OTWarrior" wrote:

> Please can someone help as I have been battling all week with this problem, 
> and haven't had a response.
> 
> I have a form which uses some data from table1 to create an entry in table2 
> (i know duplication is bad, but as this is a live database, and i am working 
> on a new section, this makes this complicated task easier), which works fine. 
> however, i have a third table that has the unique ID from table1 and table2 
> in order to show when a table1 row of data is needed in table2 via a 
> checkbox. This third table3 is created when elsewhere.
> 
> The problem lies with bringing in the unique ID value from table2 and puting 
> it in the same row as the unique ID value from table 1, into table 3. eg:
> 
> table1
> UID = 12345
> 
> table2
> UID = 6789
> 
> table3
> UID = 1
> tbl1-UDI = 12345
> tbl2-UDI =
> 
> the code i use when opening the form is as follows: (text126 pulls the value 
> of UID1)
> 
> Dim badger As String
> Dim LinkID As Long
> 
> badger = Me.NameData & ";" & Me.dteStartDate & ";" & Me.dteEndDate & ";" & 
> Me.Text126
> 
> DoEvents
> LinkID = DLookup("UID2", "table3", "[UID1]=" & Me.Text126)
> 
> On Error GoTo ErrorHandle
>     DoCmd.OpenForm "frm_TRA_AddBooking", , , "[NameData ]=" & Me![NameData], 
> acFormAdd, , badger 
> 
> I am guessing it is something to do with the form creating an autonumber for 
> table2, and not being able to use that value until after the form is closed, 
> however I have tried passing over the value to a second form, and that does 
> not pass it over either.(text54 calls the value of UID2 which pulls from the 
> table)
> 
> Dim DayDataInfo As String
> 
> DayDataInfo = Forms!frm_TRA_AddBooking!UID2;" & 
> Forms!frm_TRA_AddBooking!UID1;" & Forms!frm_TRA_AddBooking!Text54
> 
> DoCmd.OpenForm "frm_TRA_DayData"
> 
> I would be very grateful if someone would help me with this (providing I 
> have explained it clearly enough)
0
Utf
5/25/2007 9:31:01 AM
Reply:

Similar Artilces:

how do I create a line graph for part of the periods?
I want to put two line graphs on one chart but each line is for a subset of the periods. Harold - Set up the data like this: Srs A Srs B a 1 b 2 c 3 d 4 4 e 5 f 6 g 7 and Series A will cover a-d and Series B will cover d-g. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Harold Rostoker wrote: > I want to put two line graphs on one chart but each line is for a subset of > the periods. ...

Reservation Form
I have an active website for my business. I built it in Frontpage 2002. It no longer will send email through the settings I used ( Reservation form) and I cannot get it to take any of the settings I give it. I do not think that my knowledge is sufisticated enough. And, I don't think that I am entering the information completely enough. I am using a simple form with a single enter button. Can you help? May be possible. -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression Web "FrontPage 2002 on windows XP" <FrontPage200...

Empty Rows & colomns
Hi evryone need a VB code to find first empty row and first empty colomn. apretiate any help Khalil Hi Khalil, Function FirstEmpty(what As String) As Long Dim r As Range If what = "row" Then For Each r In ActiveSheet.Rows If WorksheetFunction.CountA(r) = 0 Then FirstEmpty = r.Row Exit Function End If Next ElseIf what = "col" Then For Each r In ActiveSheet.Columns If WorksheetFunction.CountA(r) = 0 Then FirstEmpty = r.Column Exit Function End If Next End If ' no empty rows (or columns), return zero FirstEmpty = 0 End ...

deleting entire rows with the same cell value in the first column
Hi! I have an huge table in which there are lots of rows that have the same value as the one in the row before: 1 1 1 2 2 2 How can I delete them leaving this way only one row per value without searching for them and deleting them manualy? Any help welcome! Pedro, assuming your data starts in a1, in b1 type =if(a1=a2,"Y","") and copy down for the length of your data. Then filter on Y and delete all those rows. HTH -- Sincerely, Michael Colvin "Pedro F." wrote: > Hi! > I have an huge table in which there are lots of rows that have the same >...

Auto populate a form form an open form
Hi I have a main form with customer names and addresses and would like to add a control button on this form which opens up another form which is used for recording telephone calls on. What I have been trying to do is auto populate this call log form with information from the main form ie name and address? Any thoughts Thanks Chris Chris, Please don't re-post the same question twice. You have to be patient and wait for a reply. Responders are volunteers, and you may not get an answer right away. Posting twice causes some people to respond to the first post, while others are ...

copy properties Under forms
Hi, I'm wondering if someone can help me with something on copying properties. I've checked the access help but can't find anything there. This is what i want to do. I'm copying text boxes, labels that has caption, name and other information in the properties that i want to keep when i copy the information to another tab on a form. Is there a way i can copy the property information without doing alot of manual work by going into each text or label box. I hope this makes sense. thanks for you help in advance. Keith When you copy a control, everything about the cop...

highlighting rows is there an macro for this
I have columns from A1 to A300 TO CP1 TO CP300 if I click on a particulr cell on any row is it possible to high light that whole row if there is a way please tell me how!!! See some choices in your other post in Misc. Regards, Howard "Mike" <Mike@discussions.microsoft.com> wrote in message news:400C7CBD-7A47-4C28-85A7-C77AA50106F5@microsoft.com... > I have columns from A1 to A300 TO CP1 TO CP300 if I click on a particulr > cell > on any row is it possible to high light that whole row if there is a way > please tell me how!!! ...

form pozition on screen
I would like to find form position on screen. It's similar to DoCmd.movesize, but I want discover "parameters' for form position (left corner, top corner, etc) Are you saying that you want to find out what the current position IS? If so, you can use something like this: MyFormLeftVariable = Me.Left MyFormTopVariable = Me.Top MsgBox "My Form is at " & MyFormLeftVariable & VbCrLf & MyFormTopVariable Or however you want to use it. -- Bob Larson Access World Forums Super Moderator Utter Access VIP Tutorials at http://www.btabdevelopment.com ___________...

Pivot Table question:
If I have a simple spreadsheet, with: acctNo Call Month Number of Calls 23142 Feb-2003 12 23142 Mar-2003 90 25943 Feb-2003 24 25943 Mar-2003 32 26044 Mar-2003 1 1260 Feb-2003 144 1260 Mar-2003 615 25828 Mar-2003 59 17688 Mar-2003 359 5235 Mar-2003 4 23042 Mar-2003 53 Then I can very easy generate a pivot table like this: Sum of Number of Calls Call Month acctNo 2003/02/01 2003/03/01 (blank) Grand Total 1260 144 615 759 5235 4 4 17688 359 359 23042 53 53 23142 12 90 ...

Converting Leads to Opp/Account/Contact creates unresolved emails
Hi, We have noticed that Converting Leads to Opp/Account/Contact creates unresolved emails. We use CRM 4.0 and we are tracking all emails in CRM. When a Lead is converted to an Account, Contact, and Opportunity, the email address is copied to both the Account and the Contact. Every further email received from the customer is then unresolved in CRM. Is there a way to define what is copied so that we can have only the email in the Contact updated, and the email in the Account left blank? Thank you! -- Laura Barber On Aug 19, 5:31=A0am, Laura Barber <laura-at-progsys-dot-net> w...

What is the purpose of 'Dummy' window in the Command forms
I am wondering why GP has Dummy window with title "~internal~" for the Command forms. For example Command_Financial form has Dummy window Do I need to create Dummy window for my Command forms. If so why I need it. Any comments appreciated!!! Hi Mandeep, The Dummy window is there as a placeholder. It really doesn't do anything, but should be there in order for the form to work properly since your form needs to be open for the commands to be available. The title ~internal~ just prevents this window from showing up in the Security assignment windows. Make sure that...

Create hyperlink via macro? Word 2007
Having a problem creating a macro to insert a hyperlink in Word 2007. Here's exactly what I want it to do: 1. AFTER I highlight a few words or so in the document, I press the button/hotkeys that kicks off the macro 2. The Insert Hyperlink box comes up 3. The address bar gets prefilled with EXACTLY the same text (a lengthy, unchanging path to a file) EVERY TIME, then the macro STOPS or PAUSES running so that the user can enter a specific file name, which will change each time, thus completing the full path. 4. I click on OK and the hyperlink is created correctly. 5. We only...

Form class module for non-existent form
Hi, After copying a form with a class module, something went wrong. I now have a class module for the new form, but the form no longer exists. I am unable to remove the in-limbo class module. I want to use that name for my form, which is not possible as long as that class module is in the way. How do I get rid of the in-limbo class module? I've tried repairing tha DB as well as "msaccess /decompile" but it hasn't helped. Anything else? Thanks, Kjell Kjell, A class module is not part of a form, but a separate module. It sounds like your 'in-limbo' module is...

Report broken down by results from a calculated field
Hello, I have a query which calculates a date which is 100 days from the initial referral date. Other fields in the query are: patient ID, date of surgery and the reason why no surgery within 100 days of referral if applicable (this field is added through a combobox that only gives choice of 3 options). I would need a report that groups the results of the calculated field (those who had surgery within 100 days of initial referral, those who did not have surgery within 100 days and those who do not yet have surgery date. Those who do NOT have surgery within 100 days of referral, I nee...

Cannot open the Insert object (Pdf file) form Excel 2000(9.0.8968 SP-3)
Dear All, I am no idea why I cannot open the embedded object (Pdf file) inside Excel file which using Windows ME. Once clicked the object icons, then it shows the error message likes " the embedded object cannot be opened by the source application" I used the Adobe reader 6.01 as the source application. Actually, I searched the related forum from the Internet. However, I cannot solve the problem. Anyone helps? Thanks tlee Hi, I can't test 2000 in ME, but it works fine in 2003 under XP. -- If this helps, please click the Yes button. Cheers, Shane Devenshire &q...

Look up problem
Part 1 I have this look up in cell H18. =LOOKUP(RIGHT(I18,2),$G$68:$H$75) Cell I18 is formated as text but contains numbers like 123405. The RIGHT(I18,2) takes the last 2 digits (the 05) and does the lookup. This works except when a number that is not in the lookup range is entered in I18. For example, if 95 is entered the lookup returns the lookup for 05. My first question is how can I return only valid input in the LU range. Part2 If nothing is in Cell I18 then #N/A appears in H18. The #N/A looks awful. The second question is how can I show nothing in H18 when I18 is blank. I have Exc...

Row height #9
Row height autofit isn't working for non-merged cells. Have ensure no spaces following text. What happens when you autofit that row? How much text do you have in that cell? Sharon wrote: > > Row height autofit isn't working for non-merged cells. Have ensure no spaces > following text. -- Dave Peterson ...

Saving changes to a previously created template
Hi I am having a frustrating time updating my paperwork to bring it all up to date, please read below. firstly create your would be template save as - name.dot or .dotx then close the template and close word down. open word open the template you just created make changes to template at this point this is where I have a problem! try and save the template with the changes with the same name I get a message saying Word cannot give a document the same name as an open document, Type a different name for the document you want to save. this worked perfectly fine in office 2003...

Setting the Windows Cursor during Form Processing
hiya gurus I would like to force the mouse cursor to immediately become an hourglass when some long processing/quering commences on a form. it appears that it takes some time for Access to show the hourglass and usually only after the user clicks the form a couple of times because there appears to be nothing happening... Is there an easy way to do this from within Access.. I have done it in VB6 using Me.MousePointer = vbHourglass but this does nto work in Access.. any suggestions? cheers Jeff mmmmm Screen.MousePointer = 11 guess this does it? "jeff" <jeff@work...

How to create Lookup fields
Hi ! Is it possible to create lookup fields ? During my customization I added a field mapping to contacts, created that field in the deployment manager & mapped it to Contact but on the form it's always a text field and not a lookup ,any ideas??? v1.2 of CRM does not support adding lookup fields. Matt Parks ---------------------------------------- ---------------------------------------- On Wed, 7 Apr 2004 11:22:12 -0700, "Yara" <anonymous@discussions.microsoft.com> wrote: Hi ! Is it possible to create lookup fields ? During my customization I added a field map...

Create Virtual Directory for OWA
We have 2 servers. Our web server is Windows 2000 SP4 with IIS 5 and our Exchange Server which is a Windows 2003 Server with IIS 6 and Exchange 2003. Is it possible to create a virtual directory to connect to the exchange server from our web server? I didn't want to create a front-end/back-end server configuration but it seems like I should be able to create a virtual directory on the Win2k web server called Exchange so that users could go to www.domain.com/exchange and get the OWA interface. What do I need to do to accomplish this? You need a Exchange Front End server. There is a lot m...

Position 2nd Form according to 1st Form
I have 2 Forms. When the 1st Form is openned and want I open the 2nd Form, I want it to position itself about 15cm form the Left and 20cm from the top of the 1st Form. I am using Access 2003, Thanks. "AY" <AY@discussions.microsoft.com> wrote in message news:CFA3A4B5-36D9-4A20-AB57-601290155335@microsoft.com... >I have 2 Forms. When the 1st Form is openned and want I open the 2nd Form, >I > want it to position itself about 15cm form the Left and 20cm from the top > of > the 1st Form. I am using Access 2003, Thanks. You should be able to use Nicole Calinoiu...

TOURNAMENT FORMS
Need tournament draw sheet single elimination template. You may find some old NCAA basketball tournament forms out on the web. Try http://google.com HHSTENNIS wrote: > > Need tournament draw sheet single elimination template. -- Dave Peterson ec35720@msn.com ...

Run Macro only if certain form is open
I have programed a maco to open one of various forms depending on criteria on an open form. Is there a way to run a "follow up Macro" based on which form opens? In other words can I run a macro based on wheather a certain form is active? -- Thanks, Chuck Take a look at the RunMacro Method of the DoCmd object. You can have it as part of the OnLoad of the form in question. Chuck wrote: >I have programed a maco to open one of various forms depending on criteria on >an open form. Is there a way to run a "follow up Macro" based on which form >opens? In other w...

outlook new message has no subject
The subject line is not there when I create a new message in outlook so recipients get an email from me with no subject. Of course it has no subject until you put one in. Explain what you are = doing to expect a subject line prefilled. --=81 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. =20 After furious head scratching, LSA1019 asked: | The subject line is not there when I create a new message in outlook | so recipients get an email from me with no subject. D...