Auto populate a userform via a reference (1 Viewer)

[gmazza76]

Good Afternoon,

I am new to access and am a bit stuck.
I have a userform i want to populate once a reference number has been entered into a unbound textbox. Can anyone point me in the right direction to create a macro that will pull the data out of my database?

sorry if i am being vague, but i am unsure how to word this as i am having no look with google.

thanks
gavin

 

[burrina]

What exactly do you want to populate? Normally you would use a Combobox for this with an AfterUpdateEvent procedure.
I don't use macros, so no help there.

 

[gmazza76]

I have a database with information in and a column headed reference.
On my userform i want to be able to enter the reference number ( tab out or run a macro) once this is done the information in refernce to to the number is pulled in.

ie the date is to be populated in text2, from column [date] etc....


Thanks in advance

Gav

 

[burrina]

How can we advise you with NO information? Table Name,Query Name, Field Names, etc...

Can you post a Demo copy of the db?

 

[gmazza76]

Sorry,

My table name is Data with columns
[date],[developer],[RefNo],[Prop],[Criteria]
The form i am lauching the search from is
"frmAgentWorkToDo" and cell reference Text41.

I have created the button to open the correct form which is "FrmUpdate"

When the form is loaded i need the reference to appear in Text1 on FrmUpdate, then the following to upload to
date - text11
developer - Combo7
RefNo - Text1
Prop - text15
Criteria - Combo25

I would upload a copy, but i am working on this for the company and it has sensative info in.

Thanks
Gav

 

[burrina]

An example would be, where Column(1) is the field in your query for Combo7 that references RefNo

Forms!FrmUpdate!Text1 = (Me.Combo7.Column(1))

HTH

 

[gmazza76]

Thanks for this,
Would i enter this in the control source in properties.
Also how would this reference my data table?

Sorry for being a pain, just trying to understand where the information is from?

Thanks
Gavin

 

[burrina]

Okay, here is an example. Of course adjust accordingly to your needs.

Good Luck With Your Project!

 

[gmazza76]

Thanks for the file you sent, but i have 1 question if possible.

I think i understand how your file works loading another userform, but is there a way i can do this without loading another userform.
As in i have the reference number and then the fields to populate below it on the current userform.
I understand the VB you have written so far, but can i adjust it so the textboxes etc populate instead of opening another form.
I think i would possibly need to refresh the cells that are populted

Private Sub cmdopenform_Click() 'Open Form With Matching Criteria.
On Error GoTo Err_cmdopenform_Click
If IsNull(Me.cbofind) Then      'Require Criteria.
    Call MsgBox("You Must Enter Criteria First", vbExclamation, Application.Name)
        Me.cbofind.SetFocus
            Exit Sub
                End If
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "frmAgentWorkToDo"
 
    stLinkCriteria = "[RefNo]=" & "'" & Me![RefNo] & "'"
 
  'CELL'S TO ADD TO    
frmagentWorkToDo![RefNo]!text64
 
Exit_cmdopenform_Click:
    Exit Sub
Err_cmdopenform_Click:
    MsgBox Err.Description
    Resume Exit_cmdopenform_Click
 
End Sub

I dont know how plausible this is?

thanks
Gav

 

[burrina]

You want to populate frmAgentWorkToDo without opening it from frmUpdate?
Is this correct? What is text64 ?

 

[gmazza76]

Is it possible to send a file to you through this website without posting it online?

The simplist way is probably to think of it as 1 form instead of 2. ie fill a number press submit and it opens another userform.

I have a field called ref no, then the relevant fields that need populating under it (currently in your second userform)

I can send the DB direct to you if you wish

Thanks
Gavin