Need to: Vba code to get data from table to form automatically by clicking abutton

[a7mad1990]

I have tables named "TblWorksOnly" it has id, worktype and workname fields it has 22 fixed records i need ,"TblCustomers" it has Custfullname and id fields ,"TblLocations" it has idloc and LocName fields

and a form named "FRM_TblWorksOnly"

i need code vba to get data automatically from TblworksOnly based on comboboxs "CmbCustFullName", "LocName" ,"Worktype"


how to do this with button for each customer without repetition for one customer

thanks for all

Ahmed Amer

 

[CJ_London]

not clear on your description - where are the comboboxes? on your frmtblworksonly or another form? if another form, is the frmtblworksonly a subform on this form?

and what does 'with button for each customer without repetition for one customer' mean? you have 22 buttons scattered around a form? one button?

what are the rowsources to your combos and which is the bound column?

as a guess, perhaps use the where parameter of the openform method?

DoCmd.OpenForm method (Access)

Office VBA reference topic

learn.microsoft.com

 

[a7mad1990]

Here Attach I That mean

thanks CJ_London

 

[CJ_London]

OK

1. your 3 combo's should be unbound (not clear if they are or not)
2. you are using a continuous form, so you need to apply a filter or restate the form recordsource
3. in the button click event to apply a filter use code along the lines

• me.filter="CustName = '" & cboCustFullName & "' and LocID=" & cboLocName & " and Worktype = " & cboworktype
• me.filteron=true

you have not explained the rowsources and bound columns of your combos or the field types in your tables so the filter will need adjusting for these.

 

[a7mad1990]

but this code will get filter data i Entry Manual
i need automatic data entry for WorkName "22 Record" from TblWorksOnly by select 3 combobox to this form when i click button

Get Data Automatically by Button to each customer i selescted without repetition

 

[CJ_London]

Sorry, really not clear what you require - are you saying you want those 3 fields to be autopopulated for new records?
If so in your button click event put something like

CustName.default = cboCustFullName
LocID.default=cboLocName
Worktype.default =cboworktype

as before, no idea of your combo rowsources

or perhaps you don't need the button, use the form current event to set these values if it is a new record.

you could then lock these three controls so users can't change them

 

[a7mad1990]

i need same this method but by getting the data from WorkName "22 Record" from TblWorksOnly sort by id

 

[CJ_London]

apply a filter as described in post #4 but just for that field

 

[Pat Hartman]

I'm sure we're having a translation problem. I can't make sense of the first three pictures. The video moves too fast for me to focus on what is happening but it looks like you want to copy the fixed 22 records and append them to some other table. This is a pretty common request for applications like surveys or task lists. You start a new survey and so you want to copy the 25 questions to the employeesurveyanswer table so it has one row for each question he has to answer.

To do that, you would use an append query that takes either one or two arguments depending on whether the "22" is always fixed or if it is the number of questions from survey1 rather than survey2. The second argument is the ID of the target parent record.

Start by using the QBE to create a select query that selects the rows and columns you want from the "fixed" list table. Change the query type to append. Then Access will automatically populate the append to column names if they match. Otherwise you have to do them manually. The final step is to create a new column. change the append to to the name of the FK. Then in the Field cell, add a prompt.

INSERT INTO tblLvl2 ( Lvl1ID, Lvl2Name, Lvl2ID )
SELECT tblLvl1.Lvl1ID, tblLvl1.Lvl1Name, [EnterFKValue] AS Expr1
FROM tblLvl1;

Now, to run this from VBA without being prompted, you have to supply a value for [EnterFKValue]

Dim db as DAO.Database
Dim qd as DAO.Querydef

Set db = CurrentDB()
Set qd = db.Querydefs!YourAppendQueryName
    qd.Parameter!EnterFKValue = Me.SomeFKValue

    qd.Execute