How to auto copy fields from one record, and paste to current record?

[I_M_Desperate]

I have been trying to figure this out for days with no luck. What I am trying to do is create a button on a form that when clicked while viewing the current record:

-goes to a certain previous record, specified by prompting the user to enter the primary key of the desired record, (in this case, a unique name)

-copies the data from all but a few certain fields in said record,

-Goes back to the current record and pastes all the data it copied into the appropriate fields.

One million cool points to whoever figures this out for me!

 

[Isskint]

One method is as follows;

Dim intImportRecord as Integer

intImportRecord = Inputbox("Enter Unique Number")

Me.FirstFieldToFill = Dlookup("FieldToReturn","TableName","UniqueID=" & intImportRecord)
Me.SecondFieldToFill = Dlookup("FieldToReturn","TableName","UniqueID=" & intImportRecord)
ETC.....

 

[I_M_Desperate]

Thank you for the reply!

I am a total noob when it comes to this sort of thing, and am not positive what I need to leave exactly as written above, and what I need to replace with names from my database. Here is what I've got so far. Can you tell me what all is glaringly wrong?

Private Sub Command166_Click()
Dim intImportRecord As String
intImportRecord = InputBox("Enter Deal Name to Copy From")
Me.LPSASectioncommentsforopenendeddef = DLookup("LPSASectioncommentsforopenendeddef", "Deals", "UniqueID=" & intImportRecord)

Where Command166 is the name of my button,
LPSASectioncommentsforopenendeddef is the name of the first field I want to copy to/from.
Deals is the name of a table

Also, I get this error when I try to click the button: Compile error: Method or data member not found

 

[Royce]

If I understand right, your "UniqueId" is a string. Thus, the last part of the line should be ... "UniqueId = '" & intUniqueId & "'". That's a single quote, ' , inside the double quotes.

Also, good coding practice says the variable should be strUniqueId. As written, using an int prefix for string makes the code very confusing.

 

[I_M_Desperate]

Ah yes, that makes sense Royce, I made those changes and have the following now:

Private Sub Command166_Click()
Dim strImportRecord As String
strImportRecord = InputBox("Enter Deal Name to Copy From")
Me.LPSASectioncommentsforopenendeddef = DLookup("LPSASectioncommentsforopenendeddef", "Deals", "UniqueID='" & strUniqueID * "'")
End Sub

I am still getting the same error mentioned above. :/ Thanks for your insight thus far!

 

[Royce]

There's a typo in the your last post. See the asterisk * near the end. That should be an ampersand, &.

 

[Royce]

Also the last part should read ... "UniqueId = '" & strImportRecord & "'"

strUniqueId is not defined in your code.

 

[I_M_Desperate]

Oh! Those typos! I've corrected it.

The error I keep getting listed above is connected to the first line:

Private Sub Command166_Click()

 

[I_M_Desperate]

Certainly this little error hasn't got you guys stumped? Well, I guess thats understandable. It's had me stumped for ages.

 

[Royce]

Just noticed the last post, are you still having problems?

It has been my experience that when the error is on the declaration line of the sub routine or function that there is a compiler error. In other words, look for another typo or syntax problem.

One check is turn off Option Explicit and see if you get a different error. ( Be sure to turn Option Explicit back on, as it will prevent a lot of subtle bugs.)

If you want, post the whole subroutine again.

Royce