Get Info from Table to Form

[AccessAmateur]

On 13 Feb 03 AccessAmateur wrote:

GetInfofromTable

I have a form which edits/prints data most of which is obtained from a large (50,000+entries) table which is read only.
How do I set up so that the user can open the large table, select a record using any search/filter criteria on any field in the table then send the record ID# back to the small table on which the form is based?
Currently the user must copy/paste the ID# from the table to the form.
Setting up the large table as a Combo box is not an option because then the search & filter capablilities are not available when the user is searching for the desired record in the large table.
This should be such a simple thing!
Thanks


and got No replies.
Is this impossible? Or too simple & I just should know?
Thanks again
AccessAmateur

 

[bjackson]

do you want to save the id automatically or do you want the
user to press a save button to save the id.
is it the saving of the id you are concerned about or do
you want to set up a new form

 

[AccessAmateur]

I want the user to user to press a button or select from a right click popup or something while at the selected record in the big table (datasheet view) an have the record ID# (it has an ID# field) sent to the small table which is open in form view.
The big table can be opened as a subform in datasheet view (although I haven't set it up this way as yet) and I think the user event will have to be a rt click sub menu because any control buttons disappear when in datasheet view. I can do this much, but I don't know how to select the ID# field from the current record and send it to the small table.
Thanks
Ted

 

[bjackson]

instead of having your main table in datasheet view
have it as a continuos form and add a command
button.This button will appear on each line,In the on click
event you would have something like this,


Dim Dbs As dao.Database
Dim Recset As dao.Recordset
Dim Qdf as dao.querydef
Set Dbs = CurrentDb()
Set Qdf = Dbs.CreateQueryDef("", "SELECT id# FROM smalltablename" _
& " " & "WHERE (((smalltablename.Id#)=" & controlnameon your subform that holds the Id# & "));")
Set Recset = Qdf.OpenRecordset
With Recset

If Not Recset.EOF And Not Recset.BOF Then


Recset.Edit
![id#] = yoursubform id#
Recset.Update
Else
recSet.addnew
![id#]=yoursubform id#
recset.update

End If




End With
Recset.Close
Set Recset = Nothing
Set Dbs = Nothing

you have to fill in all the appropiate names for your controls
you may find you need to tweak the above code to
suit your needs-i am just trying to show you 1 direction
you might head in

smalltablename

 

[AccessAmateur]

Thanks Bj

Took a bit of tweaking but I got this to work:
Dim strSQL As String

Set BigTableID = Me.ActiveControl
Set BigTableCodeNum = Me.CodeNum
Dim frmCurrentForm As Form
Set frmCurrentForm = Screen.ActiveForm
Set SmallTableID = frmCurrentForm.ID
strSQL = "UPDATE BigTable, SmallTable SET SmallTable.CodeNum = " & BigTableCodeNum & ";"
strSQLa = "UPDATE BigTable INNER JOIN SmallTable ON BigTable.IDCode = SmallTable.IDCode SET SmallTable.ID = [BigTable]![ID];"
DoCmd.SetWarnings WarningsOff
DoCmd.RunSQL strSQL
DoCmd.RunSQL strSQLa
It has to transfer the 'IDCode' (integer) then do a join on IDCode then transfer the 'ID' because the 'ID' is a string that looks like a number but often begins with a '00' so Access screws it up by assuming it's a number & dropping the '00'. Weird.
Anyway, this is the first bit of Code I ever successfully wrote.
Thanks for pointing me in the right direction to get started.

 

[bjackson]

your welcome but i would be careful using
active screen active control etc,sometimes this
doesnt always turn out to be what you think,it can
return different things and not return them
consistently

 

[Pat Hartman]

I suggest that you comment out the warnings off statement. strSQL should not work since it does not contain an explicit join.

BTW, if leading zeros are disappearing, it would be because the field is defined as a number NOT a string. Check your table definitions before blaming Access.

 

[AccessAmateur]

Thanks Pat
I want the warnings off because otherwise the user gets a "You are about to update xxx records" message. strSQL does not need a join because the Small Table is limited to just one record. It is a form used to edit &/or print a single page report for one record at a time only.
Field "ID" is formated as text in both forms, but the data entry in the Big Table (which I cannot change) is "001234" etc.
I tested it with a small sample table. If there are two records, one ID='001234' and the other ID='1234' , both will end up as '1234' even though the fields are identical text fields.
The 'ActiveForms' etc only work properly when the Big TableForm is functioning as a subform of the Small Table Form which is the intended setup.
Thanks again for the help. I couldn't have gotten anywhere without it.

Ted